Autonumber Not Working - Disable AutoSave in a Subform in Microsoft Access


	Back to Disable AutoSave Subform Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Autonumber Not Working

Tanin Uthayanaka

14 months ago

I have a strange problem.

I was following Rick's Tech Help video Disable Autosave Subform (Extended Cut) and was able to do what was taught in my database.

Source: https://599cd.com/blog/display-article.asp?ID=2650

Through the process of testing the "Close" button sub-routine, I was able to repeatedly cause the Auto Numbering NOT to work by choosing "No" from the "Close" On click button event.

The event simply deleted changed items from "source" table then inserted original items from the "changed" table.

I could have recorded how the auto numbering is not working if you wished to see.

My fix is to Compact and Repair database and it is back to normal.

Not sure why this is happending.

NOTE: In my database, I did not "Close" the form and allow user to continue changing the data.

Thanks,
Tanin

	Sandra Truax @Reply 14 months ago
Auto Numbers are used even if the record is not saved. This video might help you understand how Auto Numbers work. AutoNumbers Are NOT For You

	Jeffrey Kraft @Reply 14 months ago
I was going to say. If auto number doesn't want to play... It's time to compact and repair... after backing everything up.

Tanin Uthayanaka OP @Reply

14 months ago

I would prefer to to fix the root cause of the problem which I believe it may have something to do with the insert command from Ricks Video but I am not sure how to fix it.

The command works but it breaks the auto numbering.

CurrentDb.Execute "INSERT INTO OrderDetailT " & _
"SELECT * FROM OrderDetailChangeT " & _
"WHERE OrderID=" & OrderID

I am suspecting that it is because the OrderDetailChangeT has the exact same structure as OrderDetailT including AutoNumber field but I am not certain if that is the root cause.

Jeffrey Kraft @Reply

14 months ago

I went to ChatGPT  to see what it would say.
  1. Corruption in the Table or Databse.  Try Compact and Repair
  2. Autonujmber Field was deleted and readded. This can cause issues. Recommned rebuilding table structure and do an append Query.
3. Many Entry or Append Query messed it up
4. Autonumber isn't the Key Field.
5. No new record is actually being created.
6. Autonumber field is hidden in a form.  I'm looing at that and thing why would that matter?
7. The table hit the autonumber limit.  Though rare Autonumber can run out of numbers (e.g., Long Integer limit is 2.1 billon for possitive.

Troubleshooting Tips:
* Call Richard Rost be ready to pay in Gold Pressed Latinum.
* Try adding a new record directly in the table view.  Does it incriment?
* Can you reliably cause the issue?
* Create a new table with an Autonumber - does it work there?
* Try an Append Query without specifying the Autonumber field.
* Look at the table design - is that field still set as Autonumber.
* Look at relationships - is referential integrity causing a block.

	Tanin Uthayanaka OP @Reply 14 months ago
My hunch is correct. I simply removed the AutoNumber in the OrderDetailChangeT. To Rick, You might want to update the video and the download to remove the AutoNumber in the OrderDetailChangeT. Regards, Tanin

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Disable AutoSave Subform.

Next Unseen

New Feature: Comment Live View