How to use a field to lookup a record and populate additonal field in a form

powerman9
powerman9 used Ask the Experts™
on
I am currently putting together a new Access 2016 Database. I presently have a main form which uses names as the main table. In a subform, I have an appointment form which is comprised of linked tables. In this case the Appt form has a table that includes project, time and date information that is linked to a table that included address and contact information. These forms are connected as a one to many relationship with contacts being the one and appt time/date/project being the many (One contact can be associated with many appts). The main form is connected to the subform with a one to many relationship, the name being the one and the appts being the many.

The Tables and Fields are as follows:

Main Form
Control Source - Table:
MainNameDatabase
Fields:
MainNamePrimaryID
FirstName
LastName

Subform
Control Sources - Tables:
TalentApptDatabase
Fields:
ProjectTitle
ApptDate
ApptTime

ContactDetailsDatabase
Fields:
FirstName
LastName
Company
AddressLine1
AddressLine2
City
State
Zip
(These tables' fields are combined into one subform related the to the main form)

My question is this, I want to be able to type into one of fields in ContactDetailsTable and see if that record already exists and if so associate the entire rocord with the appt record.  If not, then I want to add a new record to the ContactDetailsDatabase. What is the best way to do this, can you?

IN other words, is there a way to bring up a combo box in say Company that will search the existing records and populate those fields?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Commented:
Are the people in the main form table different from the people in the contact details?  So Joe Brown is the main name and Suzie Q is the contact details name?
Top Expert 2009

Commented:
I think you may need to redo your links.  If there are Main Names, Appointments and Contacts, and apparently Projects too (though you don't list a Projects table), then you might need a many-to-many relationship between Main Names and Contacts, or Main Names and Appointments,  and probably a one-to-many relationship between Projects and Appointments.  The nature of the relationships depends on the data.  For example, in my sample database on Many-to-Many relationships (attached), there is a many-to-many relationship between Scouts and Badges because a scout can have multiple badges, and a badge can be held by multiple scouts.  You need to think through the possible relationships and set up links accordingly.  When that is finished, then you can set up appropriate lookups.
accarch178.zip

Author

Commented:
PatHartman. Yes, that is correct. Helen, there is no projects table but there might be, so noted.  I have attached the relationship report.

Relationship-report.pdf

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial