I guess what I am trying to do is have the drop-down display the second field from Nurse table and store the first field in the Patients table in the database and not the second which is what it seem to always do
That's a key difference between drop down lists and pop up menus. Drop down lists always display the entered value when you exit the field. Pop Ups will display he column 2 value if the value list is set up to hide column 1.
You can switch to a pop up menu (there are ways to hide the beveled look that it has), or you can define a relationship between the two tables if you have not already done so and then you can place the field from the other table next to the drop down list. There are two ways you can play layout games so that you are using a drop down list, but the 2nd column name field is all that is visible.
- Select the arrow option for the field and then resize it until only the arrow is visible. Place this next to the name field from the related table and use Inspector's behavior section on its data tab to deny access to the related field when in browse mode.
- Place the drop down list field behind the name field, with browse access prohibited for the name field. Choose a solid fill color for the name field to keep the dropdown field hidden.