DMax question

[johngordon]

I use the following code to create record numbers for my database:

=DMax("[ID]","Process")+1

Works ok in a stand alone enviorment, but in a multi use enviorment it seems to not work so well.

I do not like using Autonumber and would like to use a simple numbering system like Autonumber.

Can anyone share their thoughts on a good but simple numbering system that will work in both a stand alone and multi user enviorment.

Thanks,

John

 

[hceonetman]

johngordon,
A remnant of Foxpro 2.1, we used a separate table to keep the next available number. I don't have the specific code available, but the steps are pretty simple: get the next number from the table, then increment the number for the next user. An advantage of this approach is you can manually adjust the number as needed.

HCEONETMAN

"There's no place like 127.0.0.1"

 

[scriverb]

Try leaving your Default Value for your ID field as you stated above. Then create a new command button and save it cmdSaveRecord. Now here is the VBA code to use as the OnClick event procedure:

Private Sub cmdSaveRecord_Click()
On Error GoTo Err_cmdSaveRecord_Click
    DoCmd.RunCommand acCmdSaveRecord
    Me.Requery
    DoCmd.GoToRecord , , acLast
Exit_cmdSaveRecord_Click:
    Exit Sub
Err_cmdSaveRecord_Click:
    Me![ID] = DMax("[ID]", "Process") + 1
    Resume
End Sub

You will have to limit the users ability to save a record through the navigation buttons. Remove them. As the user creates a new record a new ID number will appear as it should with the correct number for the moment. If someone else saves a record with that number before the user saves the current record it will error out and flow through the error routine and update the ID control with a more accurate and acceptable number. This will continue over and over if necessary until a unique number is found. Should only take one try to be successful.

Post back if you have any question.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]