As you have found out a continuous sequence without gaps is not reliable with an auto number field.
You need to do it yourself in a field that is not auto number.
In the control source of the field you need something like =DMax()+1 then save the record so another user does not claim the same number.
See this topic at Rogers Access Library
http://www.rogersaccesslibrary.com/forum/topic142.html
Bob Peterson
“Never look back unless you are planning to go that way.”
― Henry David Thoreau
Alternate Finishing will be closed for the holiday on December 24th & 25th, 2013 and Wednesday January 1, 2014. We hope you have a merry Christmas and a happy New Year!
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Khalid Tanweer
Sent: Thursday, January 02, 2014 7:08 AM
To: ms_access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] On a new record TransactionID should give next higher value if any previous record is deleted
Hi All,
I have a form on which i had a field "TransactionID" as AutoNumber, set as key. I was having problems if a record was deleted from button on form OR Undo from a button and then adding new record it was new TransactionID.
That is if TransactionID = 4 deleted or Undo, for new record i do not get TransactionID = 4, but get TransactionID = 5
I read on a forum the following, but i am not sure where to put this code, and also would it work or not. Could somebody help.
Khalid
Change your field from an autonumber to long integer and use the following code on your add record forms.
Code:
Dim varID As Variant If IsNothing(YourID) Then ' Get the previous high number and add 1 VarID = DMax("YourFieldname", "YourTableName") + 1 ' If this is first one, then value will be null If IsNull(YourID) Then YourID = 1 FieldID = VarID End If| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (3) |
Tidak ada komentar:
Posting Komentar