auto number fields

[Sandy]

I have an auto number field for household number in table 1
In table 2 I have a student ID and I need to be able to link each student ID
back to a household ID (this is for a rec center and they store everything by
a household id w/multiple student ids (i.e., different families, last names,
etc.) they group as a "family" and consider them a "household"

I have the householdID set to auto number in table 1

How do I link each student id with a household id? I've tried putting a
household id into table 2 however I guess I am doing it wrong. I've put it
in as text (which I know is the wrong type) so that I can choose table lookup
for table 1...

I have a master form with the "household" info and a subform with the
"student info" however because the two types are not the same (auto number vs
text), it won't let me link them to pull all the information together in one
form

help this is driving me crazy!!!!

 

[Van T. Dinh]

Change your ForeignKey HouseholdID (in the Table2) to Long in the Table
Design ...

Long is equivalent to AutoNumber using 4 bytes each, except it is not
auto-incremental ... Access / JET accept Long as the same data type as
AutoNumber.

 

[KARL DEWEY]

Put 'household id' into table 2 as a number - integer - long integer.

If you have data in table 2 you need to populate the 'household id' field.

Then click on the menu TOOLS - Relationships. Pull your two tables in to
the window. Click on 'household id' of table 1 and drag to 'household id' of
table 2. Select the referential integerity and cascade update boxes. Do
check the cascade delete unless you know what you are doing.

There is no need to not use a table lookup for table 1 as they will already
be link if you have set the master/child link for the form/subfom.

NOW, your master/child (form/subfom) will work when you add a child.