Combo Box to select records

[jondavidf]

Hey all,

I'm trying to use a combo box in a form that can be used to select values from a lookup table, which select or add a new record from another table based off that value. I thought it would be pretty straight forward but it's been a while since I've created a database and am lost on this one.

The details:



The table, tblStudyID, Study_ID field serves as a lookup table/values for tblPtMain, Study_ID field. The tblStudyID has just the one field, Study_ID, which is populated with records 00001 - 00999. I'd like to have a combo box where I can select any of the populated values from tblStudyID, e.g. 00002, and return the associated record from tblPtMain. If Study_ID 00002 doesn't exist in tblPtMain then a record is created if values are entered in any of the other fields on the form. Please see attached screenshots. FYI, Study_ID is my combo box in the screenshots. Sorry if that didn't make sense.

Hope that makes sense and thanks for all the help

[davegri]

Don't see point of having Study_ID at all. How will users know what Study_ID to look for?

But to continue on...
Can there be more than one record in tblPtMain for a given Study_ID?

This is a classic Mainform/Subform situation.
Create a form with tblStudyID as the recordsource. Create the combobox and get it working to select the Study_ID.
Then in design view, drag tblPtMain onto the form.
Then in the subform control properties, set both child and parent linking fields to Study_ID.
Also set the subform default view property to single form (or datasheet if there can be more than one subform record for the Study_ID).

[jondavidf]

The Study_ID is used in the database instead of patient's name (including name would require special handling of the database since it would contain protected health information). The personnel doing data entry will have a hard copy sheet that link's Study_ID to patient name.

The Mainform/Subform technique worked great, too. After using a little Google to determine how to display the subform in form view and open to a blank (new) record the form is pretty much finished, just going to clean it up a little and add some navigation buttons to open other forms.

Thanks for all the help!!