CODEWITHSUNDEEP
ms-accessautonumber
Shileshia Milligan
Shileshia Milligan

Reputation: 1

How do I keep my auto generated number the same when a record is edited in MS Access?

I have code behind my Save button that generates a Change Request Number upon the record being saved. So let's say:

  1. I create Change Request 97 and save it.
  2. It writes to the table as Change Request Number 97.
  3. When I go back in to Edit it and hit the edit button, after I make my changes it re-saves to the table. But it changes the Change Request Number to 98 and the 97 record is gone.

Is there a way to edit the record and keep the same Change Request Number ?

I have been trying to look up functions but I haven't coded anything in Access in a long time, I just need some help. 

If Me.NewRecord = True Then
    Me.Change_Request_Number = Nz(DMax("[Change Request Number]", "Change Requests"), 94) + 1
DoCmd.RunCommand acCmdSaveRecord

Upvotes: 0

Views: 28

Answers (1)

June7
June7

Reputation: 21379

Instead of checking for new record, could check if the field or textbox is Null. This assumes the field is not set with 0 as DefaultValue.

If IsNull(Me.Change_Request_Number) Then
    Me.Change_Request_Number = Nz(DMax("[Change Request Number]", "Change Requests"), 94) + 1
End If
DoCmd.RunCommand acCmdSaveRecord

Upvotes: 0

Related Questions