DMax function re-numbers existing record

  • Thread starter Slez via AccessMonster.com
  • Start date
S

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!
 
A

Allen Browne

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

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!

Allen said:
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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top