[AccessD] Increment Numbers

I want to increment locker numbers. I have LockerNumber which is a
number field with a default value of 0 & format of 000. On the form I
want new records to automatically increment the locker number. Using
this code, I only get 000 for new records. I don't want to use the
autonumber because there may be a case when they need to manually enter
a number. Which will lead to another issue of duplicating numbers (I
will ask about that in my next post).

 

On  form:

Private Sub Form_BeforeInsert(Cancel As Integer)

'increment next locker number

    If Me!LockerNumber = 0 Then

      Me!LockerNumber = Nz(DMax("LockerNumber", "qry_MaxLocker")) + 1

    End If

End Sub

 

qry_MaxLocker:

SELECT Max(tbl_Locker.LockerNumber) AS MaxLocker

FROM tbl_Locker;