473,395 Members | 1,678 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,395 software developers and data experts.

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 1725
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

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

Similar topics

33
by: Lee C. | last post by:
I'm finding this to be extremely difficult to set up. I understand that Access won't manage the primary key and the cascade updates for a table. Fine. I tried changing the PK type to number and...
1
by: BT Openworld | last post by:
I've just had to upgrade to Access 2003. Our company's main sales database started in Access V1.0 and has progressed through V2.0 and 97 without problems. I've converted it to 2003 format and have...
35
by: Traci | last post by:
If I have a table with an autonumber primary key and 100 records and I delete the last 50 records, the next record added would have a primary key of 101. Is there any way to have the primary key...
1
by: larry | last post by:
Hi there The table in question now has around 25000 records (simple -only 3 columns) Records are added with a simple insert statement : INSERT INTO SS_REQUEST_SERVICES ( REQUEST_ID,...
1
by: jimfortune | last post by:
Sometimes I use Autonumber fields for ID fields. Furthermore, sometimes I use those same fields in orderdetail type tables. So it's important in that case that once an autonumber key value is...
2
by: Materialised | last post by:
Hi All, I am writing a program in C, which will be driving via a MS Access database. I am relitivly new to database design, so if anyone could help me with my issue I would be greatful. I have...
8
by: petebeatty | last post by:
I have created a SQL string the properly inserts a record in the table. However, the insert does not occur at the end of the table. Instead it inserts a record after the last record that I viewed....
11
by: Alan Mailer | last post by:
A project I'm working on is going to use VB6 as a front end. The back end is going to be pre-existing MS Access 2002 database tables which already have records in them *but do not have any...
0
by: Macbane | last post by:
Hi All, Hope someone can help me with this. I have some PDA database software which sync with tables in an access database on my computer. It works fine until you want to sync tables that...
9
by: Tom_F | last post by:
To comp.databases.ms-access -- I just discovered, to my more than mild dismay, that some tables in my Microsoft Access 2003 database have duplicate numbers in the "AutoNumber" field. (Field...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.