one key many tables

[Guest]

I'm creating a database of both people and event related to the house museum
I work at. I've figures out how to make one form writing to many tables.
Event ID's are auto numbers, so getting same key for different tables on one
form is easy, I just put an auto-number box for each table on the same form.

But for each person their key is their initials. I'm using initald and not
an auto-number so you have a chance of guessing who they actually are. Right
now i have 3 tables ab out the people that need to be linked:

People (initials-the key, name, date of birth, date of death, spouse's
name...)
Children (initials-the key, a list of their kids)
Occupation (initials-the key, list of their occiupations)


I need to have the initals-the key be the same on each table. Is there a
way to make the form so that I onlhy have to tpye in the initials once
instead of 3 times to get it to be the same on all 3 tables?

 

[tina]

the tables/relationships setup of your database is not clear - but, at any
rate, i would advise against using a person's intials as a table's primary
key field. what happens when you have a "Mary K. Smith" record and try to
add "Michael K. Sutton"? recommend you use an autonumber field as primary
key and store the initials in another field - or, better yet, just extract
the initials from the name fields when you need to display them.

i didn't entirely follow your remarks about EventID's, but i get the feeling
that your tables are not normalized or related correctly. recommend you read
up on tables/relationships design before you go any further. for more
information, see http://home.att.net/~california.db/tips.html#aTip1.

hth

 

[Guest]

OK, If I set up the key for people to be an auto-number then how do I set up
a drop-down box in a form so I can select a person by choosing their name,
not their number and still have their number be what links their record to
the table?

 

[tina]

okay, let's use an example, as

tblPeople
pID (primary key, autonumber field)
pFirstName
pLastName

in a form's design view, add a combo box control. set the control's
properties as follows:

RowSourceType: Table/Query
RowSource: SELECT pID, pFirstName & " " & pLastName As FullName FROM
tblPeople ORDER BY pFirstName, pLastName;
ColumnCount: 2
ColumnWidths: 0";1.5"
BoundColumn: 1
ListWidth: 1.75"

now, if the combo box control is bound (has a fieldname in its'
ControlSource property), then the pID value will be saved in the form's
underlying table - but the combo box and its' droplist will *display* the
person's names.

hth

 

[Guest]

Thanks for the help.

okay, let's use an example, as

tblPeople
pID (primary key, autonumber field)
pFirstName
pLastName

in a form's design view, add a combo box control. set the control's
properties as follows:

RowSourceType: Table/Query
RowSource: SELECT pID, pFirstName & " " & pLastName As FullName FROM
tblPeople ORDER BY pFirstName, pLastName;
ColumnCount: 2
ColumnWidths: 0";1.5"
BoundColumn: 1
ListWidth: 1.75"

now, if the combo box control is bound (has a fieldname in its'
ControlSource property), then the pID value will be saved in the form's
underlying table - but the combo box and its' droplist will *display* the
person's names.

hth

 

[tina]

you're welcome

Thanks for the help.