Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2006
    Posts
    2

    Unanswered: How to sequence a number in a field in multi-user environment

    Access 2003: I have a DB table (tblWGFlist) that consists of typical people information: Name, Address, Phone, etc and a BidderNumber. This table is an invitation list of people who attend our Ronald McDonald House charity auctions each year.

    I have an unbound main form (frmWGFBidder) with a combo box from a sort query to select a name from our invitation list. I have a subform (frmWGFSub1) in the main form that shows the "people info" of the name selected in the combo box. In addition, I have another subform (frmWGFSub2) that contains the BidderNumber field and an Command Button called "SetBid".

    When people check into the Auction, the cashier locates their name in the combo, then clicks the command button to create a bidder number. The code for this button is based on the DMax function to retrieve the highest number in the table's biddernumber field for the next bidder number.

    This works fine on a stand alone computer, but when I have the five computers networked and accessing the table and form (we check in 200-300 people at the start), it causes error glitches. One of the computers will get the correct bidder number but the other four will get snagged up with a 3022 error message about can't save duplicate records. I presume this is because the users have accessed the same number with DMax when the Command button was picked, but only one is going to be successful in recording back to the table, and the rest will get trapped with the error message.

    I realize that the first thing you're going to ask is "Why don't I use AutoNumber?". Well, I would love to save myself all this headache if I could figure out how to work the following with an AutoNumber:
    1. I have to start the Bidder Numbers at 500. Our auction items are AutoNumbered & PK'd starting with 1 to 250. I use combo boxes and limit to list with everything so my data clerks can't enter an auction item number into the bidder number field and vice versa when we do check out statements (it's a madhouse!). You get the logic here...
    2. We do three auctions a year, and I clean out the tables and start fresh each auction because our attendance changes depending on the type of auction. With AutoNumbers, I have to go to dummy tables and all sorts of gyrations to reset the autonumber to 500, but I can DMax at 499 + 1.
    3. I can't just keep incrementing numbers ad nauseum because I don't want a bidder number with 15 digits and it won't fit on my bidder card LOL!
    4. I don't want a bidder number automatically assigned just because you open or edit the record. Some people come to the auction just for the food and never sign up to bid...

    Seems like I need a loop thingy to keep accessing the table until a valid number is obtained, but I'm a Lisp Guru, not a VB Guru. How can I get this to work?

    Thanks, Sandra G

    Here's the proc set to my command button:

    Private Sub SetBidNo_Click()
    On Error GoTo Err_SetBidNo_Click

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
    If Me!BidderNo = 0 Then
    Me!BidderNo = Null
    End If
    If IsNull(Me!BidderNo) Then
    Me!BidderNo = Nz(DMax("[BidderNo]", "[tblWGFList]"), 499) + 1
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
    Else
    MsgBox "Bidder number has already been assigned!"
    End If
    Exit_SetBidNo_Click:
    Exit Sub

    Err_SetBidNo_Click:
    MsgBox Err.Description
    Resume Exit_SetBidNo_Click

    End Sub

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    The problem is that you need to force a lock on the next available number, or find a way round it

    one way could be to trap the error, increment the number and attempt to rewrite the record.

    another would be to get a exclusive lock on the table and maintain that record lock until you have written the record (you need to make sure that you keep the lock for as short as possible)

    another would be to store the next available number in a separate table, have a look at these threads
    http://www.dbforums.com/t1207224.html
    http://www.dbforums.com/t1198204.html
    http://www.dbforums.com/t1202354.html

    all 3 strategies should do what you want, no doubt there are more. Of the 3 the first is probably the easiest to implement, although not the most elegant or reliable if you start having more users, the latter is probably the more 'elegant' solution and a better solution for implmenting in larger systems.


    HTH

  3. #3
    Join Date
    Jan 2006
    Posts
    2
    Thanks for your reply. I have been chasing this problem all night on several forums, and have come to the conclusion that DMax is not the best solution for a multi-user environment due to the very issues I have had with errors.

    Several have suggested created a second table for storing and incrementing the numbers. I have a sample of code, and if I can just get the naming conventions and the syntax in the right place I may make this work!

    Thanks,

    Sandra G

Posting Permissions

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