Problem with auto number 2 tables

pld60

Making progress with my data base but have run into a snag.

Working with an unbound form that place data into more than one table using a "save" button with this code.

Expand|Select|Wrap|Line Numbers

 Private Sub Commandsaverecord_Click()
 
    Set db = CurrentDb

Set rst = db.OpenRecordset("Table1")

With rst

.AddNew

!DATE = Me.DATE

!SHIFT = Me.SHIFT

!SUPERVISOR = Me.SUPERVISOR

!CATEGORY = Me.CATEGORY

!MedicalAttention = Me.MedicalAttention

!ActionTaken = Me.ActionTaken

!Other = Me.Other

.Update

End With

rst.Close

Set db = CurrentDb

Set rst = db.OpenRecordset("Tbl_Staff1")

With rst

.AddNew

!Staff1 = Me.Staff1

.Update

End With

rst.Close
 
End Sub

Works great.

My problem is that each table has key field that is auto numbered. I have a one to one relationship with the ID numbers. How do I get them to have the same ID #?

ajalwaysus

You cannot create a relationship between 2 tables where they are related to each others by auto numbers. Auto numbers have no regard to what is in another table.
In my opinion an auto number should only be used as a PK, not as a FK.
What you need to do is write the data to the primary table then grab the ID and insert it into the second table with the data you need.

Let me know if you need any help with this.
-AJ

ChipR

In this case, both your tables can't use autonumbered fields as ID numbers because they will be out of sync. You can get the number assigned to the new record in Table1, and specifically insert it into the ID field in Tbl_Staff1, or you can use Long Int for the ID field in both tables, and find your own next ID number with a function like DMAX("ID", "Table1") + 1.

pld60

ChipR

How do I do this. The relationship must be maintained

ajalwaysus

You create a FK in your Tbl_Staff1 table that has a 1-1 or 1- many relationship to your first table, while assigning the Tbl_Staff1 table it's on primary key. The relationship will be maintained while also helping to normalize the tables.

-AJ

pld60

I can't get this to work. Do i need to add some code n my on-Click to add the id from my primary table to other tables so I can keep the relationship?

pld60

I keep playing with this and can not get the autonumber from Table1 to propagate to other tables. I know that this must be simple, I must be too simple

ajalwaysus

Please post a sanitized version of your DB, and I will have a look at it. I need to see the big picture of what you are trying to accomplish.

-AJ

NeoPa

What would be better than a database is a description of your data, and possibly a question explained more clearly.

There should be no need for a database for something as simply defined as this.

Problem with auto number 2 tables

re: Problem with auto number 2 tables

re: Problem with auto number 2 tables

re: Problem with auto number 2 tables

re: Problem with auto number 2 tables

re: Problem with auto number 2 tables

re: Problem with auto number 2 tables

re: Problem with auto number 2 tables

re: Problem with auto number 2 tables

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about