DMax function re-numbers existing record

[Slez via AccessMonster.com]

I have a subform that has a DMax function in the BeforeUpdate Event. The
code is:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me!PunchID = Nz(DMax("PunchID", "tblPunchItems", "[JobNumber]='" &
[JobNumber] & "'"), 0) + 1
End Sub

It numbers new records as intended, but if I should have to update one of the
fields in the subform, it re-numbers with the next available number. As an
example, PunchID could have records 1 thru 8. If I go to records affiliated
with PunchID 4 and make any change to the data, it automatically changes
PunchID to 9. It does not create a new recordset, just re-numbers it. I
definitely don't want it to do this. Once a PunchID is established, I need
it to remain the same value.

Can this be done in a different event or can something be added to the code
that would eliminate this occurrence? Thanks in advance for any help!

 

[Allen Browne]

Try assigning the value only if the control is null:
If IsNull(Me.PunchID) Then
...

 

[Slez via AccessMonster.com]

Thank you for the response. It works perfectly!
You sure make this look easy!...but I'm learning too!
Thanks again!

Try assigning the value only if the control is null:
If IsNull(Me.PunchID) Then
...

I have a subform that has a DMax function in the BeforeUpdate Event. The
code is:

[quoted text clipped - 19 lines]

code
that would eliminate this occurrence? Thanks in advance for any help!