Autonumber and Linked Tables

[nschirmer]

I'm working on a small database for the school I work at to keep student, family and other contacts data.

I've structured my tables so that there is a general (AllHumans) table, and then tables for specific parts of the data (like addresses - which can be shared between many people). In the address table - there is an autonumber field (AddressID) with a corresponding AddressID field in the AllHumans table.

This setup works for existing data (When I imported data, I pre-generated IDs for the field) but when I create a new record - the data is all saved EXCEPT for the AddressID field in the AllHumans table. What do I need to do make sure that the autonumber generated AddressID is also written to the AllHumans table?

Thanks!



Nick

[June7]

Imported data from where? What is imported - addresses or humans or both? Pre-generated what ID - the Address?

[nschirmer]

Thank you for your reply.

I had all of the data before as excel files, and imported the data using the built in tools. Before importing, I had generated ID numbers for each human and address (the human ID and the address ID were never the same)

[June7]

How did you relate data in Excel files?

You cannot import data to the autonumber field. This value is generated by the program. You would need another field to hold the pre-generated ID.

[nschirmer]

I'm sorry - I guess I'm not being clear on what I did. (And in hindsight - I missed explaining a step!)

In Excel, nothing was related. Originally all of the data was in a single table, but to help prepare it for the database, I reorganized it. Each address was given a code and I copied by hand that code into the column in AllHumans.
When I imported the data, I imported it into a temporary table, and then created an Append query to move the data into the Autonumber column.

To test things - I created a new field "TestAuto" in AllHumans (type: long integer) and a new, empty table with 2 columns: TestID (Autonumber) and TestData (String). I joined them on my form with AllHumans on the left and TestAuto on the Right, with option 2 (all records from AllHumans included, and only records where the joined fields are equal from TestAuto). Finally I added a field to the form.

When I entered text ("Hello World") into the TestData field, it appeared to save (no errors given) but when I closed the form, and reopened the form, the field was blank.

I checked the TestAuto table - and one record was added - with the content I entered ("Hello World"). It was given an AutoNumber of 1.

Next I went to the AllHumans table - and looked at the TestAuto field - it was empty.

If I manually add a 1 (the autonumbered value of the new entry) to the TestAuto field, the form will properly show the "Hello World".

So - do I need to write in VBA a script to get the autonumber and then add that data to the AllHumans table?

Thank you for your assistance!

[June7]

So the other table has the autonumber primary key and AllHumans has the foreign key?

AllHumans is the dependent (child) table. Child table won't automatically store the primary key from other table until you 1) establish record in parent table and 2) start data entry in a field other than the foreign key field of the child table. This can be done in a query where tables have one-to-one relationship, otherwise a form/subform arrangement is needed.

[nschirmer]

Thank you - your advice led me to write a short script to get the primary key from the most recent entry in the Address table and write that value into the right spot in the AllHumans table.