Automatic AutoNumbering

[myhnews]

I am using DMax function for my custom autonumber
I just realized that when using this in a multi user environment, I am getting duplications.
This field is not the primary key, and I am using this as a group ID and cannot set it to “no duplications”
My form also has a sub form, and when entering the sub form, the main record gets saved automatically.
First I added the following procedure

Private Sub Form_AfterInsert()
If IsNull(Me.OrderNo) Then
Me. OrderNo = DMax("OrderNo ", "tblOrder") + 1
End If

Private Sub cmdSave_Click()
If IsNull(Me.OrderNo) Then
Me. OrderNo = DMax("OrderNo ", "tblOrder") + 1
End If

However, I then realized the after insert function will add the new autonumber to the form. However, it will not saved it to the table until the main record is saved again (which may not happen)

“DoCmd.RunCommand acCmdSaveRecord” doesn’t seems to be working after the “Form_AfterInsert”

Now I moved the code to the “Form_BeforeUpdate” (still testing)

Does anyone have any experience on this problem?

Thank you

Joe

 

[myhnews]

Thanks for your help.

The problem with using the BeforeInsert event, once I start completing the fields, the BeforeInsert event is fired. However, the record is still not saved. At that point the second user is opening the same form, the second user will get the same DMax number as the first user.

Do you see a problem using the Form_BeforeUpdate event?

Thank you
Joe

 

[myhnews]

I have revised the code a bit.

Private Sub Form_BeforeUpdate(Cancel As Integer)
' Set Record No to "Max" + 1
If IsNull(Me.OrderNo) Then
Me.OrderNo = DMax("OrderNo ", "tblOrder") + 1

' If result is still Null (this is the first Record), set to 1
If IsNull(Me.OrderNo) Then
Me.OrderNo = 1
End If
End If
End Sub

I need to implement the same type of code for about 10 forms
I anyone notices any problem I will experience wit this change, please share it with me ASAP.

Thank you
Joe

 

[myhnews]

When running the code at the BeforeUpdate event, the new autonumber is obtained and it takes less then a second until the record is saved. (The record is saved either when entering the sub form or by clicking the save button) I am not worried that the other user will save the record at the same time.

Also FYI
I am using the autonumber as a group ID, meaning; by default every new record gets a new autonumber. However, when I copy a record to the same table, the group ID stays the same. (I have a function that copies all information including the autonumber) the system automatically makes some changes to the new pasted record.

Basically I am saying that this should be treaded as an autonumber, with the exception that I can not set it to “no duplications”

In addition, I really don’t mind if autonumbers are lost, as long it’s not the same number generated by the other user.

Form_BeforeInsert will also not work in my case. Because the user has about 10 fields to fill in before moving to the sub form. However, the user can and may cancel the record before moving to the sub form. If I use the Form_BeforeInsert, the record will be saved right away.

At the moment I do not see any problem using the Form_BeforeUpdate. And it is very easy to change to all forms, without doing something intelligent.

However, if I am wrong, please tell me.

Thanks

Joe