469,647 Members | 1,700 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,647 developers. It's quick & easy.

Autonumber issue.

Hi,
I have a subform (subform1) with several records. Each record in subform1 has a button next it. When I click on the button it show another form (form2) that contains the clients info. it works fine but the problem I am having is when I create a new record.
When I add a new record to the subform1 (I add just first name and last name) and then I click on the button that opens form2 (I add address, phone etc). I am able to add these info but when I exit form2 the data is no longer there for the new record. I know the reason but I can't find a solution.
The preason is this: The link between these 2 forms is Autonumber. when I create a new record in subform1 it creates an autonumber, and the same thing in form2. Except form2's autonumber is alwyas 1 more than subform1. and that's why the data is not linked.
How do I solve this
I hope this is not confusing. I also did some search in this forum but no luck. Thanks
Here is the code behind form2:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "form2"

stLinkCriteria = "Autonumber=" & [AutoNumber]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Thanks in advance
Jul 18 '08 #1
2 1486
puppydogbuddy
1,923 Expert 1GB
you need to better understand PKs (pimary keys) and FKs (foreign keys). Autonumbers work great as PKs because they are unique identifier to a record. However, you don't use an autonumber as a FK like you did in the second table

A FK link to a field that is an Autonumber is set as a Long Integer data type. The PK and FK are linked in the relationship window, and by virtue of that relationship Access will insert the number generated by the autonumber field into the FK field.

Again, the main thing you need to do is add a foreign key field (long integer data type)to the table behind your subform, and create your relationship between the AutonumberPK in the first table and the FK field you just added. You would keep the autonumber field in the second table, but you would delete the relationship to the autonumber in the first table. You would keep the autonumber in the second table because it still serves as a unique record id in the second table.
Jul 19 '08 #2
We'll do. Thank you so much.
Jul 21 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by BT Openworld | last post: by
35 posts views Thread by Traci | last post: by
1 post views Thread by jimfortune | last post: by
2 posts views Thread by Materialised | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.