Subform Causing ID Index Issue

[Brad_Oleson]

I have been tasked with adding some functionality to an Access DB; it has been many years since doing any Access/VBA stuff but I was the only resource they could call on.

I've copied a subform from another form because it provides both the data access and functionality the users want. However, when entering data into it, it starts the ID field index back at 1 rather than continuing the next higher number in the table. I have googled the issue and tried the three "fixes" that seem to be the best solution, but to no avail. Is there something that limits the ability to do this the way I have done it? The error is: "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key or relationship. Change the data in the field that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again." I have considered pointing the subform to a copy of the table and then (assuming I can get past the index issue) just appending the records into the existing table. But I cannot get that to work either. I am open to suggestions and thoughts. Thanks!

Steps I have followed to fix: Compact and repair the DB Use a data definition query - the suggested query "ALTER TABLE ALTER COLUMN COUNTER(iMaxID, 1)" is supposed to reset the index to the next highest auto number in the ID field Run VBA code - code that basically runs the data def query in the above step.

[#2]

@Brad_Oleson 

First, please do NOT alter the table structure. That's not the problem here.

The description you gave is not enough to give you a full answer, but we can discuss the circumstances in general and offer some suggestions for you to follow up on.

First, this sounds like you have a one-to-many relationship between two tables. That's the most likely source of the error which raises the error message you related to us. The problem here is that the way you created this copy didn't account for that relationship.

How it needs to work is this:

The main form is bound to the table on the one-side of a relationship. The sub form is bound to the table on the many-side of that relationship. For example, it could be Orders and the line items in those orders in an OrderDetail table. Each record in the OrderDetail table has a foreign key indicating which order that record belongs to.

There is a pair of properties on the subform control which manages that relationship between the records entered into the main form and the related records in the subform. They are called "Link Master Fields" and "Link Child Fields". That's how you tell the main form and sub form to handle the Primary Key from the one-side table and the Foreign Key(s) from the many-side table.

It sounds like in copying this subform, you didn't set that up properly, or that they weren't changed to reflect the new tables involved.

Start there. If you can't suss out how to fix that, post back with some screenshots of the tables involved and the forms in Design view.

[#3]

@George Hepworth 
Wow. That was the simplest fix of all time. That was exactly the issue!

Thank you for the input!

Brad

[#4]

Congratulations on resolving the problem. Continued success with the project.