Incremental number error

[ancient_hilly]

[This is a re-post. Accidentally sent to wrong group]

My form creates an incremental number for a job sheet when a tick box is
checked and this is stored in the underlying table. Code:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.[JobSheetCheck] = True Then
Me.[JobSheetNumber] = Nz(DMax("[JobSheetNumber]", "[Events]"), 49999) + 1
End If

End Sub

The JobSheetNumber field is locked and, once ticked, the tick box is locked
so that neither can be deleted.

The problem I have is that if any changes are made to any other field on the
form, when you re-enter the record the number has incremented again.

Once created, I don't want the number to change, even if other data on the
record needs to be changed.

Can anyone advise how I prevent this happening, please?

 

[swas]

Check if it is a new record, and only then update. ie

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.[JobSheetCheck] AND Me.NewRecord Then
Me.[JobSheetNumber] = Nz(DMax("[JobSheetNumber]", "[Events]"), 49999) + 1
End If

End Sub

 

[scubadiver]

or just this:

Me!JobSheetNumber = DMax("[JobSheetNumber]", "[Events]") + 1

 

[ancient_hilly]

Thank you ! It works.

or just this:

Me!JobSheetNumber = DMax("[JobSheetNumber]", "[Events]") + 1


--

The 11th day of every month:

http://truthaction.org/forum/index.php

[This is a re-post. Accidentally sent to wrong group]

My form creates an incremental number for a job sheet when a tick box is
checked and this is stored in the underlying table. Code:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.[JobSheetCheck] = True Then
Me.[JobSheetNumber] = Nz(DMax("[JobSheetNumber]", "[Events]"), 49999) + 1
End If

End Sub

The JobSheetNumber field is locked and, once ticked, the tick box is locked
so that neither can be deleted.

The problem I have is that if any changes are made to any other field on the
form, when you re-enter the record the number has incremented again.

Once created, I don't want the number to change, even if other data on the
record needs to be changed.

Can anyone advise how I prevent this happening, please?

 

[scubadiver]

The problem with your code is that it adds one if there is a tickbox and I
wondered why!

--

The 11th day of every month:

http://truthaction.org/forum/index.php

Thank you ! It works.

or just this:

Me!JobSheetNumber = DMax("[JobSheetNumber]", "[Events]") + 1


--

The 11th day of every month:

http://truthaction.org/forum/index.php

[This is a re-post. Accidentally sent to wrong group]

My form creates an incremental number for a job sheet when a tick box is
checked and this is stored in the underlying table. Code:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.[JobSheetCheck] = True Then
Me.[JobSheetNumber] = Nz(DMax("[JobSheetNumber]", "[Events]"), 49999) + 1
End If

End Sub

The JobSheetNumber field is locked and, once ticked, the tick box is locked
so that neither can be deleted.

The problem I have is that if any changes are made to any other field on the
form, when you re-enter the record the number has incremented again.

Once created, I don't want the number to change, even if other data on the
record needs to be changed.

Can anyone advise how I prevent this happening, please?