You appear to have this relationship:
Laboratory::LabID = Staff::LabID
If so, then you can do this instead (this is the simple design, a more sophisticated approach is also possible):
Place the Lab_Name field from Laboratory on your Staff layout.
Format Staff::LabID as a drop down list where the values in column one of this list are Laboratory::LabID and the values in column 2 are the lab name.
When you select a lab in the drop down, the matching lab name will automatically appear on your layout.
Note, if you want the drop down list of lab names so that you can use an auto-complete enabled drop down where you type in a few letters and the list filters down to just the matching names, this can also be done, but then you have to add a script to look up the corresponding LabID number to enter into the foreign key field in the staff record.
I am confused (or ignorant :) )
You say place the Lab_NAME field on the Staff layout BUT then you say format Staff::labID as a drop down menu (not Lab_NAME). How do I format a field that is not on the layout? Did you mean Lab_NAME and not Lab_ID?
Also you mention COLUMNS in formatting the drop down list and I have no idea what you are referring to there....
How do I format a field that is not on the layout?
Add the field to your layout. You'll need it if you intend to link staff records to specific lab records.
When you set up a value list, you can specify that the values in the value list are drawn from the fields of a table you select. (Open Manage | Value Lists, start a new value list and check out the options you have when you use the specify field option.) You can display two columns of data for your value list. The data in the first column is what is entered into the field when you select that value. The second column provides additional data to help the user make the correct selection. In this case the name of the Lab will help you select the correct Lab while entering a lab ID number.
OK thanks that works but why do I need the Lab_NAME field on the staff layout then?
You don't need a lab name defined in the staff table at all.
Adding the lab name field from the lab table to your staff layout serves to show what name is selected for a given staff record. It's an option. You can also configure the drop down list as a pop up menu, hide the first column (an option in Manage Value list) and then you do not need to add the lab name field to the layout as it is now displayed in the Lab ID field. (This can be confusing at first as you are entering the Lab ID, but the popup menu displays the matching lab name.)
OK last question (I am sure we both hope!):
When I add the lab name field from the lab table to the staff layout it says UNRELATED TABLE.
If I relate the lab name in lab table to the staff ID key in the staff table nothing happens...just like at the start of this...
sorry so many questions
In Manage | Database | Relationships
Drag from LabID in the one table to LabID in the other so that they are related by LabID, not by name. You don't even need the Lab name field in the staff table.
WOW. Works perfect! Thank you so much. You are an amazingly patient and knowledgeable resource!