Got a VB5 app using Access97 converted to multiuser, but when they're busy we're getting duplicates occuring 2 places using the same #, turning into amajor problem as it's happening more and more frequently. I know it's something to do with rewriting the code to be networked database, but for the life of me I cannot figure out wher I'm going wrong, the following is a sample of the code:

<-----------------------
    Set db = OpenDatabase("z:\BarDb97_be.mdb", False, False, ";pwd=backdoor")
    
    Set rs = db.OpenRecordset("SELECT viewbill.*, viewbill.ladyno From ViewBill WHERE len([ladyno]) > 0;")
    
    If Not rs.EOF Then
    
' Comment out crystalreport below if you do not want bar fine and lady drink receipts printed
    
        CrystalReport1.ReportFileName = "c:\Aussie\Ladyrpt.rpt"
        CrystalReport1.Destination = crptToPrinter
        CrystalReport1.Action = 1
    
    End If
    
    rs.Close
    
    db.Execute "MoveTempBill"
    
    Set rs = db.OpenRecordset("SELECT * FROM Config")
    rs.MoveFirst
    
    rs.Edit
    rs.Fields("NextBillNo") = gNextBillNo + 1
    gNextBillNo = gNextBillNo + 1
    rs.Update
    rs.Close
    Set rs = db.OpenRecordset("Select * From binno where binno = " & gButtonNo & " Order By binno")
    rs.MoveFirst
    
    rs.Edit
    rs.Fields("Live") = "y"
    rs.Update
    
    db.Close
    
    For X = 1 To 500
    gBarGirl(X) = ""
    gLadyName(X - 1) = ""
    gLadyDrink(X) = ""

<------------------

Etc. etc.   for some reason the bill no's are being duplicated from the 2 locations where they're entered - which means when 2 people (Upstairs, Downstao\irs) try to put in a bill at the same time they get the same bill, not satisfactory for the client - I know it's got to do with locking recoreds but I am fcuked if I can figure out how to do it -BTW I am not a programmer I took over the project as a favour for the guy who owns the place, I'm a hardware/systems analyst, more fool me for sayng I know a bit of programming.



[Edited by Gollum_HKT on 20-04-2006 at 08:53 PM GMT]

Hi,
Your bill no is a variable within the application. If there are 2 copies of the application, who controls the bill no?
The solution is to either:
Use an Identity field instead of the gNextBillNo variable, or
Create a table in the shared database that holds the bill number and read/update this instead of the variable gNextBillNo

Hope this helps,
Kieron


____________________________
Build it better, faster, quicker, easier.. then fix it (non-offical MS mission statement)