Adding records with an increments of one

[johnherrerajuan]

Hello I have a database with data already entered. I have a Table that has a Field Name BoxNo which is also is my ID. The boxNo goes from 1,2,3,4...and stops at 50 has a gap and then starts again at 100,101..etc I want to be able to have an increment of one with the boxNo. I made this code with VBA

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then [BoxNo] = DLast("BoxNo ", "ARCHIVE RECORDS LIST") + 1
End Sub

This is the first time I am using VBA i am not sure were to go from here. I need to know how to run this code. I am not sure if the code is even correct someone please help me.

 

[pr2-eugin]

Hello johnherrerajuan, Welcome to AWF..

Well what you are trying to do is a similar method calle DMax()+1, put forward by RainLover on this Forum..

However the proper method to use here would be Form_Current() rather than the BeforeUpdate().. Since Form Current is called on the first instance, everytime it moves between different record.. Also DMax() is the method to use (I think).. So it would be something like..

Private Sub Form_Current()
    If Me.NewRecord Then [BoxNo] = Nz(DMax("BoxNo ", "ARCHIVE RECORDS LIST"),0) + 1
End Sub

Look over the link for DMax()+1.. Hope this helps..

 

[johnherrerajuan]

Hello,
I did what you told me to do but, it brings out an error when I click continue(F5) it says invalid use of Me keyword then it highlights Private Sub Form_Current

Private Sub Form_Current()
    If Me.NewRecord Then [BoxNo] = Nz(DMax("BoxNo ", "ARCHIVE RECORDS LIST"), 0) + 1
End Sub