Re: Need expert coding help with a debugging issue.

I'm not an expert, but all of your variables Variants since you have not specificed otherwise. That may not give the desired result when you need recordsets and databases.
It may help if you describe your database's structure, and state just what you wish to accomplish in the real-world situation that underlies your database. You stated that you are updating a record based on certain criteria, but your code is something of a labyrinth. For instance, I see:
SkipLoop2:
.MoveNext
Loop
End With
GoTo LocationEnd

LocationEnd:
End With

Most developers use GoTo only for error handling. There may be some situations in which an argument may be made for using GoTo, but eight or so line labels suggests the code is taking the long way around. In any case, even if it works to jump to End With (at LocationEnd), it is very difficult to understand which "with" you are closing.

"Nicholas Scarpinato" <NicholasScarpinato@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:0144DBA7-6FCF-417E-B1A7-683BB7BA3AB2@xxxxxxxxxxxxxxxx
I've got a piece of code in a form on my front end db that updates a record
in a table based on certain criteria. My problem is that this code seems to
have a flaw in it that allows multiple instances of this code, running on
different machines, to access the exact same record in the table, if the
following conditions are met:

1. The code is executed at the exact same time
2. The record being updated in the target table has no current value

The table has three fields: Bin Number, Vendor Name, and Count. My problem
is that when Vendor Name is empty when the code is run by two users at the
exact same time, the Vendor Name will be filled with one of the vendor names
coming from the source form, but the count will be counted as two phones, one
for each of the users entering data. The second vendor name drops out of the
database completely and goes unaccounted for. This database is for returns
processing for a warehouse that runs through about $100,000-$200,000 worth of
returns per day, so these kinds of issues are exactly what we're trying to
avoid. Here's my code for the form... I hope that somebody can help me sort
out the flaw that's allowing this to happen:

Function BinLocations(VendorName)
DoCmd.SetWarnings False
Dim db, rs, rs2, sql, sql2, FormText, BinLoc
Dim VendorBinCode
Set db = CurrentDb()
sql = "SELECT * FROM [Bin Locations and Counts Table];"
sql2 = "SELECT * FROM [Main Returns Table];"
Set rs = db.OpenRecordset(sql)
Set rs2 = db.OpenRecordset(sql2)
With rs
.MoveFirst
Do Until .EOF
VendorBinCode = rs.Fields("Vendor Name")
If VendorName = VendorBinCode Then GoTo FoundBin
.MoveNext
Loop
GoTo NewBin
FoundBin:
BinLoc = rs.Fields("Bin Location")
FormText = "Bin location found!" & vbNewLine & "Please use bin " &
BinLoc & "."
DoCmd.OpenForm "Bin Confirmation Form", , , , , acDialog, FormText
.Edit
rs.Fields("Count") = rs.Fields("Count") + 1
.Update
With rs2
.MoveFirst
Do Until .EOF
If rs2.Fields("SIM/ESN/IMEI") <> Forms![Returns Entry
Form].[SIM/ESN/IMEI] Then GoTo SkipLoop
rs2.Edit
rs2.Fields("Long Status Description") = BinLoc & " - " &
VendorName
rs2.Update
SkipLoop:
.MoveNext
Loop
End With
GoTo LocationEnd
NewBin:
rs.MoveFirst
Do Until .EOF
If rs.Fields("Vendor Name") = 0 Then GoTo MakeNewLocation
.MoveNext
Loop
GoTo LocationError
MakeNewLocation:
BinLoc = rs.Fields("Bin Location")
rs.Edit
rs.Fields("Vendor Name") = VendorName
rs.Fields("Count") = rs.Fields("Count") + 1
rs.Update
With rs2
.MoveFirst
Do Until .EOF
If rs2.Fields("SIM/ESN/IMEI") <> Forms![Returns Entry
Form].[SIM/ESN/IMEI] Then GoTo SkipLoop2
rs2.Edit
rs2.Fields("Long Status Description") = BinLoc & " - " &
VendorName
rs2.Update
FormText = "Bin location not found! New bin location created.
Please use bin " & BinLoc & "."
DoCmd.OpenForm "Bin Confirmation Form", , , , , acDialog, FormText
SkipLoop2:
.MoveNext
Loop
End With
GoTo LocationEnd
LocationError:
FormText = "All locations appear to be in use! Please clear a location
before continuing."
DoCmd.OpenForm "Bin Confirmation Form", , , , , acDialog, FormText
GoTo LocationEnd
LocationEnd:
End With
rs.Close
rs2.Close
db.Close
DoCmd.SetWarnings True
End Function

.

Relevant Pages

  • Need expert coding help with a debugging issue.
    ... Bin Number, Vendor Name, and Count. ... Dim db, rs, rs2, sql, sql2, FormText, BinLoc ... Do Until .EOF ... If VendorName = VendorBinCode Then GoTo FoundBin ...
    (microsoft.public.access.formscoding)
  • Re: Need expert coding help with a debugging issue.
    ... Is this a split database? ... New bin location created. ... product from a different vendor while the code was running, ... If VendorName = VendorBinCode Then GoTo FoundBin ...
    (microsoft.public.access.formscoding)
  • Re: Need expert coding help with a debugging issue.
    ... Is this a split database? ... a reason for having a separate sub or function is when code needs to be executed at one of several events. ... product from a different vendor while the code was running, ... If VendorName = VendorBinCode Then GoTo FoundBin ...
    (microsoft.public.access.formscoding)
  • Re: Need expert coding help with a debugging issue.
    ... product from a different vendor while the code was running, ... it out when it was no longer needed by the database. ... If VendorName = VendorBinCode Then GoTo FoundBin ... but when the bin is empty it still sets the Vendor Name to zero. ...
    (microsoft.public.access.formscoding)
Loading