Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2004
    Posts
    78

    Unanswered: Automatic AutoNumbering

    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.

    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”

    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.

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

    Does anyone see a problem using the Form_BeforeUpdate event?

    Does anyone have any experience on this problem?

    Thank you

    Joe

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    your problem is to do with locking - ie making certain that no one else can access the data whilst you are doing something. there are several stratiegies you can use, either lock the table from the point you do your DMAX to the point you write a new record. (NASTY - potentially a big performance hit if you have many users)

    OR

    Have a separate table storing the last used number
    , lock that table, read the value, increment & write back, then release the lock. Effectively the autonumber becomes a token derived from elsewhere, you don't adversely affect the main data store whilst gettigtn a new value - you will still have potential contentions in the separate table.

    OR

    consider using the autonumber setting for a column in the table

    HTH

  3. #3
    Join Date
    Nov 2004
    Posts
    78
    HTH,
    Thanks for your help

    A. only 2 users at the same time. However, they use the same tables.

    B. I would rather not start with adding new tables. Neither locking the tables


    C. I cannot use the build in Autonumber field

    FYI
    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
    If you notice any problem I can experience wit this change, please share it with me ASAP.

    Thank you
    Joe

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Olny other way is to have a 2nd table with a coulmn containing the next index number ... You query for, add, update, then use ...
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Nov 2004
    Posts
    78
    Do you see anything wrong with the following way?

    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

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Joe1
    Do you see anything wrong with the following way?

    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
    The only thing I see wrong is if 2 people save at the same time then they'll get the same index/ID number ... Over and over again ... So much for that key working eh?
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •