How to create an ID # based on number of recored (1 Viewer)

[c1bluemini]

I am a newbe at access but even after review most of the threads on auto numbering and such, I can't seem to figure out the basic problem I am having. I have a table with an empty column called ActionID. I need to generate a numerical number that begins with 5000 and goes up by 1 number on each saved recored. I have a basic form that links all of my other feild to the table except the one I need to generate (ActionID) but will need that number saved to the table once its created.

Any and all help will be greatly appreciated.

Wm

 

[CazB]

I use this code on a form I have... it should work if you just use your table and field names instead - I already had some records in my table before I started this so I didn't need to check first to make sure if there was a number to start from, iyswim

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.NewRecord Then
       Me.EnquiryNumber = DMax("val([EnquiryNumber])", "tblEnquiryDetails") + 1
    End If
 
End Sub

EnquiryNumber is the field I need to generate a number in... tblEnquirydetails is the table that contains the field!

 

[Minty]

If you don't mind numbers missing from sequence if records are deleted then I would use an autonumber.
Make Action ID a normal number field then you can set the first number used after you are finished testing by appending a record with an ActionID of 4999. Then change the field type to autonumber. The next record you add should be 5000.

Generally speaking an ID field is exactly that, a unique id for internal reference. If you happen to tell the end user that is is their "Record Number" as long as it's unique it really doesn't matter what it is.

 

[c1bluemini]

THanks CazB- I will give this a try!

 

[c1bluemini]

Ok, CazB, I entered the code you gave me But I must me missing something. My feild in my form that i wanted to show the results is unbound. I entered the code to that field by doing a build event as follows:

Private Sub ID_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Me.ActionID = DMax("Val([ActionID])", "tblMain") + 1
End If
End Sub

The table in my DB is called "Main" and the feild within that table that I want to store this ID number is "ActionID". I currently have 3 records in the table and I have manually entered 5000,5001,5002 in the "ActiveID" feilds for these first 3 figuring that the code would then know the next correct number.

This is not working so what am I missing? THis seems so simple but its making me feel very stupid right now.

Thanks again for your help.

Wm

 

[Minty]

What error are you getting

 

[c1bluemini]

I'm not getting any error- nor am I getting a number generated either.

 

[CJ_London]

Me.ActionID = DMax("Val([ActionID])", "tblMain") + 1

The table in my DB is called "Main"

Could this be why?

 

[c1bluemini]

Thanks CJ- but that was not it either. Still not getting any data saved into the ActionID Field. And I'm not getting an error message either.

 

[CJ_London]

Do you have a control on your form called ID?

I suspect this code needs to be in the form before update event

 

[c1bluemini]

Sorry CJ, Not sure I know what you mean when you as if I have a Control on the form.

 

[CJ_London]

Your VBA code starts with this

Private Sub ID_BeforeUpdate(Cancel As Integer)

Which means it is the before update event of a control named ID

If you don't have a control called ID the it will never run - and it will only run if the user goes into that control and makes a change (the before and after update events are only activated if there is a change)