Changing foreign key by changing another field

[Jeff H]

I successfully modified what you showed me (Hans) about creating a Lookup field from a query in order to have the VID and PID columns in the Activity table display a searchable dropdown with ID, First Name, and Last Name. Just what I wanted.

The staff gather activity reports from each volunteer each month and enter them in the Activity table. So I’m going to need a keyboard-friendly form for doing that input. These comboboxes will allow them to easily locate and identify the correct volunteer and patient IDs.

Now that I have the VID/PID fields displaying properly, how can I set it up so that when a new record is entered in the Activity table, or an existing record edited, the foreign keys will be determined by the ID fields?

[HansV]

The Activity table shouldn't contain VID and PID fields - the VolunteerID_FK and PatientID_FK fields should be used to specify the volunteer and patient.
You can create a query based on the Volunteer table with VolunteerID, First Name and Last Name, and use this as Row Source for the lookup combo box in the VolunteerID_FK field. When the user selects a volunteer, the selected VolunteerID will be entered in the VolunteerID_FK field.
Similar for the patient.

[Jeff H]

Yes, that makes sense. I made the following modifications, because the important element is that users are looking up VIDs.

I made the query on VolunteerID, VID, First, and Last. Then I set the Activity table VolunteerID_FK field to a lookup with 5 columns (instead of 4) and the column widths of 0";.5";.75";1.2";0" (instead of .5";.75";1.2";0").

That gives me the display I need, but the field is still a number representing the linkage to VolunteerID. Is that correct? And can I now delete the VID field from Activity table and rename VolunteerID_FK as "VID"?

[HansV]

That looks correct.
You can remove VID from the Activity table.
If you wish, you can rename VolunteerID_FK, but that is not essential.

[Jeff H]

Got it. I could just label the form field while keep a uniform standard for linking fields.

Thanks so much Hans!