Copying an auto number field from one table to a text field in ano

[Guest]

I am creating a database in Access with 3 tables. One table has an auto
number field, but I need that same field in another table. I need to create
a relationship between the 2 tables based on this field.

How do I copy the Auto Generated number from table a to table b?

 

[Marshall Barton]

I am creating a database in Access with 3 tables. One table has an auto
number field, but I need that same field in another table. I need to create
a relationship between the 2 tables based on this field.

How do I copy the Auto Generated number from table a to table b?

Unless you are using Replication, the field in the other
table needs to be a Long Integer, not Text.

If you want to copy the field using an UPDATE query or a
code procedure, you must have a set of fields in the other
table that uniquely identify the record in the main table
with the autonumber value you want to copy.

 

[Guest]

Assuming that your tables are tied to forms via the RecordSource property and
the form is used in DataEntry mode to create the subsequent records, I can
offer this suggestion.

The autonumber field of Table1 is named something like "Table1ID". This is
the Primary Key.
Create a field in Table2 named "Table1ID" but it is a long integer - not an
autonumber. This is known as the Foreign Key.

Now the problem that I understand is how the Primary Key is transferred to
the Foreign Key of Table2. This way that I typically accomplish this is to
create Form2 with RecordSource tied to Table2. Form2 has a not visible field
named "Table1ID". The objective is to set the DefaultValue of the Table1ID
field on Form2 to the appropriate value from Table1.

When Form2 is opened in DataEntry mode (presumably from Form1) the Primary
Key is transferred to Form2 by either setting a public variable to the value
of the Primary Key or by sending the Primary Key to Form2 via the OpenArgs
part of the OpenForm command.

DoCmd.OpenForm "Form2", , , , , , Table1ID

On the On Open event of Form2 use either the Public Variable or the OpenArgs
data to load the DefaultValue of Table1ID on Form2.

Me.Table1ID.DefaultValue = Me.OpenArgs

or if using a public variable something like
Me.Table1ID.DefaultValue = lngTable1ID

This will transfer the Primary Key of Form1 to the Foreign Key of Form2 on
close if Form2 is in DataEntry mode and any information is entered by the
user into Form2.

Hope this helps.

 

[John W. Vinson]

Now the problem that I understand is how the Primary Key is transferred to
the Foreign Key of Table2. This way that I typically accomplish this is to
create Form2 with RecordSource tied to Table2. Form2 has a not visible field
named "Table1ID". The objective is to set the DefaultValue of the Table1ID
field on Form2 to the appropriate value from Table1.

A simpler alternative is to make Form2 a Subform of Form1, using Table1ID as
the Master and Child Link Field. No code needed!

John W. Vinson [MVP]