ACCESS FEED: Re: [MS_AccessPros] On a new record TransactionID should give next higher value if any previous record is deleted

Kamis, 02 Januari 2014

Re: [MS_AccessPros] On a new record TransactionID should give next higher value if any previous record is deleted

Khalid-

First of all, there is a function called IsNothing, but it requires an object as an argument, not a field or control.

As the article implies, you first have to change your AutoNumber field to Long Integer, but I recommend you make a backup copy of the table first.

Your code needs to go in the BeforeUpdate event of the form. It should look something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)

' If on a new record,

If Me.NewRecord Then

' Assign the "next" value to the ID

Me.TransactionID = NZ(DMax("TransactionID", "Transactions"), 0) + 1

End If

End Sub

… where "Transactions" is the name of your table.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Jan 2, 2014, at 1:07 PM, Khalid Tanweer <khalidtanweerburrah@yahoo.com> wrote:

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