Forum Discussion
Added record to Access Table failed.
"...no way to add the missing ID"?
I think perhaps I can guess what you are trying to get at.
You have an AutoNumber datatype for the ID field in this table. And you want this field to be the Primary Key for the table, correct?
And when you added a new row to the table in DataSheet view, the new record has an AutoNumber value higher than you expected. In other words, the "missing ID" represents a gap in the sequence of values in that field. Is that also correct?
So, while it would be helpful to verify that this guess is correct, I will offer some guidance on the assumption that it is.
That's how AutoNumbers work and how they are intended to work.
AutoNumber guarantee only that each record saved to the table will have a unique value in that field. No more and no less.
What probably happened--as is often the case--is that sometime in the past a record was started in this table, but not saved. OR, a record was added and later deleted. In either case, the AutoNumber value generated for that now absent record was lost. You are not going to be able to fill in the gap.
The truth is, as noted, this is how AutoNumbers are supposed to work. You can't use them, or maybe it's better to say, you should not use them, for anything other than the Primary Key in a table.
If you want to DISPLAY a value that always increments, create your own values for it. In other words, these fields are intended only for the internal use of the Access relational database. Any use which displays them to the users is fraught with problems like this.
Of course, my assumption is based partly on the limited information in this post and decades of experience, but is certainly not infallible. If there is something else going on, a complete explanation will help.
- Microsoft, George Hepworth
Added record to Access Table failed
A new address added to an Acc Table row, with a blank ID, moved the ID Asterisk (*) down to the next row with no way to add the missing ID. New inputs toother Tables, in the same file were accepted.
More detailed description
My file is “contacts.accdb”. It contains Tables for names, phones, addresses, etc.
Unfortunately, one of our granddaughters is going through separation from her husband. Her husband lives in their apartment in Philadelphia. My grand daughter is living in Minneapolis.
I want to add new records in the name, phone, and address Tables for my granddaughter.
I successfully added her first and last name in the name table and AutoNumber assigned a new ID number.
I then successfully added her phone number plus other fields to the phone Table, using the name AutoNumber, and an AutoNumber was assigned in phone ID field.
I also changed field data in her husband’s Tables fields.
Finally; I attempted, unsuccessfully, to add data to address fields in the row with an asterisk. When data was added, a new row appeared above the asterisk row and a flag advised that I needed to manually add a number in the address ID field. I looked up the address field numbers in use and chose a higher number. This did not work.
Hepworth suggestion
“If you want to DISPLAY a value that always increments, create your own values for it. In other words, these fields are intended only for the internal use of the Access rational database. Any use which displays them to the users is fraught with problems like this.”
I do not know how to create my own values. The next sentence seems to say that would not be wise anyway. Is there another way to solve my problem?
