I have two tables, one smaller and one larger. I have an existing personnel entry form that adds records into the smaller table.

What I need to do is link the two tables together somehow so that when I enter a name that exists into the personnel entry form it searches to see if there is an existing record in the larger table and subsequently draws any other existing information and inserts it into the appropriate fields in the entry form.

I'm fairly sure this is done using relationships in some way but I've had difficulties with them in the past.

Do the tables have to have identical fields for the relationships to work?
Does the fact that the first and last name text boxes have somehow changed to scrollable boxes matter?

I've attached shots of the entry form, and the existing relationships screen. If any others are needed, let me know and I'll post them.

Thanks

I have two tables, one smaller and one larger. I have an existing personnel entry form that adds records into the smaller table.

What I need to do is link the two tables together somehow so that when I enter a name that exists into the personnel entry form it searches to see if there is an existing record in the larger table and subsequently draws any other existing information and inserts it into the appropriate fields in the entry form.

I'm fairly sure this is done using relationships in some way but I've had difficulties with them in the past.

Do the tables have to have identical fields for the relationships to work?
Does the fact that the first and last name text boxes have somehow changed to scrollable boxes matter?

I've attached shots of the entry form, and the existing relationships screen. If any others are needed, let me know and I'll post them.

Thanks


I think that you should reconsider your table designs before going any further. From the looks of it, your tables are not Normalized. For instance, you have duplicate data being stored in both the tblPersonnel and tblPersonnelLarge. Personnel data should be stored in it's own table and referenced by the ID number. Also, in the PersonnelLarge table you have:

DisabilityDescription1
disabilityDescription2
etc up to 5

What happens if you need a 6th Disability Description?

Disability Info should have it's own table, and you can store the PersonnelID as a Foreign key so that you can reference it.

With the table properly setup, what you are looking to do can be accomplished by using subforms very easily.

Hate to say it but this database is in SERIOUS need of Normalization. The table structure is really not optimum (to say it kindly).

Can you post a tables only, and all data stripped out, copy here?