Automatically add an id# to multiple tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have about 9 tables in a database and forms corresponding to each table.
I am using a passport number as a unique ID in each table.
When I add an entry to a table or form, how do I avoid having to type in the
Passport number on all the tables or forms.
I want to be able to add the number once and automatically add it to all the
other tables or forms.
 
I do this by including the Passport field from each of those tables in my
form. I hide all of them except one, and I have code that runs after
filling in the Passport number field which takes that value and sets all
those other passport fields to equal it.
 
I have about 9 tables in a database and forms corresponding to each table.
I am using a passport number as a unique ID in each table.
Click to expand...

That's almost certainly erroneous. Do EVERY ONE of these tables
contain one, only one, never any more records for a given passport
number? What is the nature of the data in the tables, and how are they
related?

Typically you would have several one to many relationships; the
Passport number might be the primary key of the Passports (or
Travelers, or whatever) master table, and a non-unique foreign key in
other tables.
When I add an entry to a table or form, how do I avoid having to type in the
Passport number on all the tables or forms.
Click to expand...

Typically one would use a Form for the "one" side table and Subforms
for the "many" side tables, using the Primary Key as the subform's
Master Link Field and Child Link Field properties. When you add a
record to the subform, it will automatically fill in the value into
the subform's table, when (and only when) it's needed.
I want to be able to add the number once and automatically add it to all the
other tables or forms.
Click to expand...

This is not a good idea. Creating vacant "placeholder" records in the
related tables is not needed (see above), and such records have a
nasty way of staying blank rather than getting filled in with real
data.

John W. Vinson[MVP]
 
Back
Top