Need expert coding help with a debugging issue.

  • Thread starter Thread starter Nicholas Scarpinato
  • Start date Start date
N

Nicholas Scarpinato

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
 
For starters, properly declare your variables.

Bad:
Dim db, rs, rs2, sql, sql2, FormText, BinLoc
Dim VendorBinCode

Good:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim sql As String
Dim sql2 As String
Dim FormText As String
Dim BinLoc As Long 'or appropriate type
Dim VendorBinCode As Long 'or appropriate type

Then, get rid of all the GoTo statements. It is widely accepted that using
GoTo statements outside of error handling (e.g., On Error GoTo ErrorHandler)
is not good programming. The blocks of code being referenced by the GoTo
statements should be turned into subs or functions as appropriate and called
from the BinLocations function.

HTH

Chris

Nicholas Scarpinato said:
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
Click to expand...
 
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 said:
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
Click to expand...
 
Comments in-line:

BruceM said:
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.
Click to expand...

Duly noted... although at this stage I'm not sure that simply defining my
declarations better is going to make any difference whatsoever with regards
to the errors I'm getting... I had no problems at all until I started testing
the database in a multi-user role.
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.
Click to expand...

At present, I'm in the very early development stages of my database, so I'm
using GoTo's as a temporary solution so that all of my code pertaining to a
specific function is in one place rather than spread across 15 different subs
that may or may not be correct. With the code all in one place I can trace it
line by line without having to jump around to look at different subs. Once I
know that the code is good I'll split it off into subs.

The real world application of the database is an inventory control database
for the Returns department. Our customers send in the products they want to
return, and our returns clerks check them in. This piece of code is a sorting
code that tells the user exactly which bin location he or she should put the
item they have just scanned in. The bins are sorted by vendor. The idea was
to make them dynamic bins that the database would manage rather than making
them static bins, since we have a limited number of bins to use and there's
no sense locking a bin to a vendor whose products rarely get returned (or
that we rarely sell in the first place). The clerk would scan the barcodes on
the product, add in any addition information required, and commit the record.
Then the database would check to see if there were any products from that
vendor already stored in the bin locations table. If so, it would add 1 to
the count of that bin, and tell the user to put that product in that bin. If
not, it would assign the next available bin to that vendor, add 1 to the
count, and inform the user of the new bin location. Later on down the line
there are functions to clear out those bins and process the products in those
bins in batch mode, since we send the products in by batch rather than
individually.

I'm really stumped on this one. I've reworked the checkin form so that it's
bound to the main data table, and I have that piece working fine. I've also
changed this code to use update queries to update the vendors and counts, and
now instead of adding an extra product to the bin count when two users hit
that same record at the same time, it drops one phone out of the count table
completely. Then I have one extra product in my main table that is assigned
to a bin batch that doesn't exist. I'm tagging each phone with a serial
number comprised of the bin number and the vendor name (e.g., "A01 -
BrightPoint"), but when this problem occurs, one of the phones will have a
serial number for a vendor and bin combination that doesn't exist in the bin
table. Therefore, that product remains in the data table, but none of my
subsequent queries will pick it up.

Nicholas Scarpinato said:
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
Click to expand...
Click to expand...
 
Comments inline.

Nicholas Scarpinato said:
Comments in-line:



Duly noted... although at this stage I'm not sure that simply defining my
declarations better is going to make any difference whatsoever with
regards
to the errors I'm getting... I had no problems at all until I started
testing
the database in a multi-user role.
Click to expand...

Properly declaring your variables is a good place to start. If you are
declaring by default a recordset or database as a variant you may not get
the hoped-for result. A break point in your code may help you sort out just
where it is going astray, but being explicit about your variables would have
taken about the same amount of time as speculating that it makes no
difference.
At present, I'm in the very early development stages of my database, so
I'm
using GoTo's as a temporary solution so that all of my code pertaining to
a
specific function is in one place rather than spread across 15 different
subs
that may or may not be correct. With the code all in one place I can trace
it
line by line without having to jump around to look at different subs. Once
I
know that the code is good I'll split it off into subs.
Click to expand...

Your choice, but why would the code be split across 15 subs? Why not just
write the next step into the code?
Again, stepping through the code may be your best chance. Looking at one
chunk of code:
With rs
.MoveFirst
Do Until .EOF
VendorBinCode = rs.Fields("Vendor Name")
If VendorName = VendorBinCode Then GoTo FoundBin
.MoveNext
Loop
GoTo NewBin

What is VendorName? Is it the argument for the function? If so, it is
spelled differently (with a space). If you step through you can determine
if the hoped-for VendorBinCode is what this line of code produces.
NewBin is as follows:
rs.MoveFirst
Do Until .EOF
If rs.Fields("Vendor Name") = 0 Then GoTo MakeNewLocation
.MoveNext
Loop
GoTo LocationError

Again you are looping through all of the records you just looped through. A
domain aggregate function such as DCount, or maybe a sql string depending on
the approach you are taking, should be able to find what you seek. In any
case, the GoTo stuff is not helping.
The real world application of the database is an inventory control
database
for the Returns department. Our customers send in the products they want
to
return, and our returns clerks check them in. This piece of code is a
sorting
code that tells the user exactly which bin location he or she should put
the
item they have just scanned in. The bins are sorted by vendor. The idea
was
to make them dynamic bins that the database would manage rather than
making
them static bins, since we have a limited number of bins to use and
there's
no sense locking a bin to a vendor whose products rarely get returned (or
that we rarely sell in the first place). The clerk would scan the barcodes
on
the product, add in any addition information required, and commit the
record.
Then the database would check to see if there were any products from that
vendor already stored in the bin locations table. If so, it would add 1 to
the count of that bin, and tell the user to put that product in that bin.
If
not, it would assign the next available bin to that vendor, add 1 to the
count, and inform the user of the new bin location. Later on down the line
there are functions to clear out those bins and process the products in
those
bins in batch mode, since we send the products in by batch rather than
individually.

I'm really stumped on this one. I've reworked the checkin form so that
it's
bound to the main data table, and I have that piece working fine. I've
also
changed this code to use update queries to update the vendors and counts,
and
now instead of adding an extra product to the bin count when two users hit
that same record at the same time, it drops one phone out of the count
table
completely. Then I have one extra product in my main table that is
assigned
to a bin batch that doesn't exist. I'm tagging each phone with a serial
number comprised of the bin number and the vendor name (e.g., "A01 -
BrightPoint"), but when this problem occurs, one of the phones will have a
serial number for a vendor and bin combination that doesn't exist in the
bin
table. Therefore, that product remains in the data table, but none of my
subsequent queries will pick it up.
Click to expand...

Maybe you need to wait until the form's Before Update to run the function.
If the update fails for whatever reason, you would need to run the function
again; otherwise it will grab the next available numbers or bins or
whatever. If another user is working on a record that would have produced a
conflict, the numbers will all have changed by the time that user saves the
record (and runs the form's Before Update event).

I don't know what data are in your main table, or how the main table relates
to other tables. The meaning of "update the vendors and counts is unclear".
I can see updating the counts, but what vendor information are you
updating.? Is Vendor information in its own table? If so, how does that
table relate to other tables?

Maybe this thread will attract the attention of somebody who is familiar
with the business situation you are describing, but I can only say that I am
not, so may be handicapped in trying to understand your requirements.
However, I suspect that just about anybody would need a clearer view of your
database's structure if they are to provide a specific suggestion.
"Nicholas Scarpinato" <[email protected]>
wrote
in message news:[email protected]...
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
Click to expand...
Click to expand...
Click to expand...
 
I appreciate your efforts, but unfortunately none of what you've suggested
has helped. I've been working on this problem for three days now, and I've
done step mode through the code at least 100 times. I've refined the code a
bit to try to implement some other ideas, but nothing is solving the main
issue. I've even re-written three other segments of code in my entry form and
main module, and still have no resolution to my problem.

Comments inline:
Properly declaring your variables is a good place to start. If you are
declaring by default a recordset or database as a variant you may not get
the hoped-for result. A break point in your code may help you sort out just
where it is going astray, but being explicit about your variables would have
taken about the same amount of time as speculating that it makes no
difference.
Click to expand...

Properly declared or not, I'm still getting the exact same result. I did
have one issue that I ran into with a global variable that was passing
information from one function to another: if one user scanned in a new
product from a different vendor while the code was running, the vendor name
from the new product would be attached to the one currently processing. I
still need that variable to be global, so I made sure to add a line to clear
it out when it was no longer needed by the database. But properly declaring
my variables has had no effect on the outcome.
Your choice, but why would the code be split across 15 subs? Why not just
write the next step into the code?
Again, stepping through the code may be your best chance. Looking at one
chunk of code:
With rs
.MoveFirst
Do Until .EOF
VendorBinCode = rs.Fields("Vendor Name")
If VendorName = VendorBinCode Then GoTo FoundBin
.MoveNext
Loop
GoTo NewBin

What is VendorName? Is it the argument for the function? If so, it is
spelled differently (with a space). If you step through you can determine
if the hoped-for VendorBinCode is what this line of code produces.
Click to expand...

Funny you mention VendorName... that was the global variable I mentioned
above. And you were correct, the argument name for BinLocations() was
VendorName. I wrote the code for BinLocations before I ended up neeing a
global variable to store the vendor name in. I changed the argument for the
function to VName, I hadn't noticed I still had it as VendorName until I
caught the issue I mentioned previously. As far as being split across other
subs, I suppose I could write it all in-line, but I think I'd rather have
them as subs, if for no other reason than to clean up the main code for each
function.
NewBin is as follows:
rs.MoveFirst
Do Until .EOF
If rs.Fields("Vendor Name") = 0 Then GoTo MakeNewLocation
.MoveNext
Loop
GoTo LocationError

Again you are looping through all of the records you just looped through. A
domain aggregate function such as DCount, or maybe a sql string depending on
the approach you are taking, should be able to find what you seek. In any
case, the GoTo stuff is not helping.
Click to expand...

For what I'm attempting to do, I would need DLookup, not DCount. When I
first started development on this database, the vendor codes were all
numeric. (They still are, actually, although not according to my database.)
After about two hours of playing around with the numeric codes, I decided it
would be much easier for everybody involved if we could see the actual vendor
names those number represented, hence the parsing code that I have on the
main entry form which parses out a scanned barcode into three fields for PO
date, PO number, and Vendor Name. Originally, if a bin was empty, the Vendor
Code field of that record in the bin locations table would be zero. But I
changed the table to encorporate the new changes, so now the field name is
Vendor Name, but when the bin is empty it still sets the Vendor Name to zero.
It doesn't have to stay that way, but I haven't had a reason to change it
yet. Once the database is finalized I'll change it to say "Empty" or
something.

Now if I could figure out how to make DLookup work the way I want it to...
for some reason I can never get it working.
 
I'm about at the end of my work day, so I won't be looking at this much more
until Monday. However, I find myself wondering how you pass VendorName to
the function. I see why a reassigned global variable would cause a problem.
Is this a split database? If as I suspect it is not, if you split it and
each user has their own front end I think you will find that the variable is
not affected by other users with their own copies of the front end. In any
case, it seems to me that the variable needs to be local one way or the
other.
By the way, a reason for having a separate sub or function is when code
needs to be executed at one of several events. Rather than rewriting the
code at each event, a sub or function is called. If a segment of code is
not being used by other events I see no reason to split it off from the main
function.
 
Comments inline:

BruceM said:
I'm about at the end of my work day, so I won't be looking at this much more
until Monday. However, I find myself wondering how you pass VendorName to
the function. I see why a reassigned global variable would cause a problem.
Is this a split database? If as I suspect it is not, if you split it and
each user has their own front end I think you will find that the variable is
not affected by other users with their own copies of the front end. In any
case, it seems to me that the variable needs to be local one way or the
other.
Click to expand...

The database is split and I'm still having issues with variables. However,
I've just rewritten the entire code from scratch using DLookups and SQL
statements generated dynamically. I haven't had a chance to implement the
changes on my second test box yet, but this is what I have now:

Function BinLocationsNew(VName)
DoCmd.SetWarnings False
Dim BinLookup As Variant, FormText As String
BinLookup = DLookup("[Bin Location]", "Bin Locations and Counts Table", _
"[Vendor Name] = '" & VName & "'")
If IsNull(BinLookup) = True Then
BinLookup = DLookup("[Bin Location]", "Bin Locations and Counts_
Table", "[Vendor Name] = '0'")
DoCmd.RunSQL "UPDATE [Bin Locations and Counts Table] SET _
[Vendor Name] = '" & VName & "' WHERE [Bin Location] = '" & _
BinLookup & "';"
DoCmd.RunSQL "UPDATE [Bin Locations and Counts Table] SET _
[Count] = [Count] + 1 WHERE [Bin Location] = '" & BinLookup & "';"
FormText = "Bin location not found! New bin location created. _
Please use bin " & BinLookup & "."
DoCmd.OpenForm "Bin Confirmation Form", , , , , acDialog, FormText
Else
DoCmd.RunSQL "UPDATE [Bin Locations and Counts Table] SET _
[Count] = [Count] + 1 WHERE [Vendor Name] = '" & VName & "';"
FormText = "Bin location found!" & vbNewLine & "Please use bin " _
& BinLookup & "."
DoCmd.OpenForm "Bin Confirmation Form", , , , , acDialog, FormText
End If
DoCmd.SetWarnings True
End Function

This function is called from the following line of the main entry form's code:

Call BinLocationsNew(Me![Vendor Name])

It works much faster than the old code, and seems to be a bit cleaner in
it's execution, although I'm not sure that I really improved anything other
than processing time just yet. The DLookup has a feature I hadn't really
thought about before that suits my needs perfectly in that if it finds more
than one record that matches the criteria, it returns the first one it finds.
That works perfectly for assigning the next available bin when I need to
create a new one, as my bin names are A01 through A20 and the table is sorted
ascending on the bin names.
 
Well, the good news is, the new code works great. The bad news is, I still
have the same problem as before when I try it concurrently on two machines. I
have a time delay function that's supposed to kick in and force the database
to take a five second pause before trying to execute the code again,
depending on how long ago the last item was entered, but the problem there is
the entry times are dependant on the time on each user's machine, which may
be as much as five or six minutes off from eachother. Other than that, I
don't know what else I can do. But it's time to go home for the weekend, so
I'm going to go home and not think about this for a couple days. :)

Nicholas Scarpinato said:
Comments inline:

BruceM said:
I'm about at the end of my work day, so I won't be looking at this much more
until Monday. However, I find myself wondering how you pass VendorName to
the function. I see why a reassigned global variable would cause a problem.
Is this a split database? If as I suspect it is not, if you split it and
each user has their own front end I think you will find that the variable is
not affected by other users with their own copies of the front end. In any
case, it seems to me that the variable needs to be local one way or the
other.
Click to expand...

The database is split and I'm still having issues with variables. However,
I've just rewritten the entire code from scratch using DLookups and SQL
statements generated dynamically. I haven't had a chance to implement the
changes on my second test box yet, but this is what I have now:

Function BinLocationsNew(VName)
DoCmd.SetWarnings False
Dim BinLookup As Variant, FormText As String
BinLookup = DLookup("[Bin Location]", "Bin Locations and Counts Table", _
"[Vendor Name] = '" & VName & "'")
If IsNull(BinLookup) = True Then
BinLookup = DLookup("[Bin Location]", "Bin Locations and Counts_
Table", "[Vendor Name] = '0'")
DoCmd.RunSQL "UPDATE [Bin Locations and Counts Table] SET _
[Vendor Name] = '" & VName & "' WHERE [Bin Location] = '" & _
BinLookup & "';"
DoCmd.RunSQL "UPDATE [Bin Locations and Counts Table] SET _
[Count] = [Count] + 1 WHERE [Bin Location] = '" & BinLookup & "';"
FormText = "Bin location not found! New bin location created. _
Please use bin " & BinLookup & "."
DoCmd.OpenForm "Bin Confirmation Form", , , , , acDialog, FormText
Else
DoCmd.RunSQL "UPDATE [Bin Locations and Counts Table] SET _
[Count] = [Count] + 1 WHERE [Vendor Name] = '" & VName & "';"
FormText = "Bin location found!" & vbNewLine & "Please use bin " _
& BinLookup & "."
DoCmd.OpenForm "Bin Confirmation Form", , , , , acDialog, FormText
End If
DoCmd.SetWarnings True
End Function

This function is called from the following line of the main entry form's code:

Call BinLocationsNew(Me![Vendor Name])

It works much faster than the old code, and seems to be a bit cleaner in
it's execution, although I'm not sure that I really improved anything other
than processing time just yet. The DLookup has a feature I hadn't really
thought about before that suits my needs perfectly in that if it finds more
than one record that matches the criteria, it returns the first one it finds.
That works perfectly for assigning the next available bin when I need to
create a new one, as my bin names are A01 through A20 and the table is sorted
ascending on the bin names.
By the way, a reason for having a separate sub or function is when code
needs to be executed at one of several events. Rather than rewriting the
code at each event, a sub or function is called. If a segment of code is
not being used by other events I see no reason to split it off from the main
function.
Click to expand...
Click to expand...
 
I saw your follow-up post, but the code is here, so I am posting some
replies inline.

Nicholas Scarpinato said:
Comments inline:

BruceM said:
I'm about at the end of my work day, so I won't be looking at this much
more
until Monday. However, I find myself wondering how you pass VendorName
to
the function. I see why a reassigned global variable would cause a
problem.
Is this a split database? If as I suspect it is not, if you split it and
each user has their own front end I think you will find that the variable
is
not affected by other users with their own copies of the front end. In
any
case, it seems to me that the variable needs to be local one way or the
other.
Click to expand...

The database is split and I'm still having issues with variables. However,
I've just rewritten the entire code from scratch using DLookups and SQL
statements generated dynamically. I haven't had a chance to implement the
changes on my second test box yet, but this is what I have now:

Function BinLocationsNew(VName)
DoCmd.SetWarnings False
Dim BinLookup As Variant, FormText As String
BinLookup = DLookup("[Bin Location]", "Bin Locations and Counts Table", _
"[Vendor Name] = '" & VName & "'")
If IsNull(BinLookup) = True Then
BinLookup = DLookup("[Bin Location]", "Bin Locations and Counts_
Table", "[Vendor Name] = '0'")
Click to expand...

As I understand, you are checking whether the vendor's items are in any bin.
If not, you are looking for the next available bin. If so, a couple of
points. If no record matches the criteria, DLookup should return Null. I
do not see how "[Vendor Name] = '0'" can return anything other than a record
in which VendorName is the character 0. Also, you should not rely on the
table order for selecting the next available bin (although the Bin Location
field should be indexed), but rather an explicitly sorted recordset, such as
in a named query. In this case it may be simplest to use DMax instead of
DLookup.
DoCmd.RunSQL "UPDATE [Bin Locations and Counts Table] SET _
[Vendor Name] = '" & VName & "' WHERE [Bin Location] = '" & _
BinLookup & "';"
DoCmd.RunSQL "UPDATE [Bin Locations and Counts Table] SET _
[Count] = [Count] + 1 WHERE [Bin Location] = '" & BinLookup & "';"
FormText = "Bin location not found! New bin location created. _
Please use bin " & BinLookup & "."
DoCmd.OpenForm "Bin Confirmation Form", , , , , acDialog, FormText
Else
DoCmd.RunSQL "UPDATE [Bin Locations and Counts Table] SET _
[Count] = [Count] + 1 WHERE [Vendor Name] = '" & VName & "';"
FormText = "Bin location found!" & vbNewLine & "Please use bin " _
& BinLookup & "."
DoCmd.OpenForm "Bin Confirmation Form", , , , , acDialog, FormText
End If
DoCmd.SetWarnings True
End Function
Click to expand...

I don't quite understand the problem you are having when you run the code
concurrently on two machines. I know you described it in an earlier
posting, but please describe, with field names and values, the expected
result as opposed to the result you are getting when two users enter records
concurrently. Running the code again when there is an error should do what
you need, provided an error is being generated.
This function is called from the following line of the main entry form's
code:

Call BinLocationsNew(Me![Vendor Name])

It works much faster than the old code, and seems to be a bit cleaner in
it's execution, although I'm not sure that I really improved anything
other
than processing time just yet. The DLookup has a feature I hadn't really
thought about before that suits my needs perfectly in that if it finds
more
than one record that matches the criteria, it returns the first one it
finds.
That works perfectly for assigning the next available bin when I need to
create a new one, as my bin names are A01 through A20 and the table is
sorted
ascending on the bin names.
By the way, a reason for having a separate sub or function is when code
needs to be executed at one of several events. Rather than rewriting the
code at each event, a sub or function is called. If a segment of code is
not being used by other events I see no reason to split it off from the
main
function.

"Nicholas Scarpinato" <[email protected]>
wrote
in message news:D[email protected]...
Click to expand...
Click to expand...
 
Comments inline:

BruceM said:
As I understand, you are checking whether the vendor's items are in any bin.
If not, you are looking for the next available bin. If so, a couple of
points. If no record matches the criteria, DLookup should return Null. I
do not see how "[Vendor Name] = '0'" can return anything other than a record
in which VendorName is the character 0.
Click to expand...

Well, there's technically three conditions: the vendor already has an
assigned bin, the vendor does not have an assigned bin, and there are no bins
available. So I want to know all three. I realize I don't have it coded that
way right now, but I will once I get everything finished. If the vendor
doesn't already have an assigned bin, I want to know which one is the first
available. That's why I have this line:

If IsNull(BinLookup) = True

In my first lookup, I'm searching for a vendor name in the table that
matches the vendor name on the entry form. In the second, I'm searching for
an empty bin, which is denoted with the vendor name being zero.
Also, you should not rely on the table order for selecting the next available
bin (although the Bin Location field should be indexed), but rather an explicitly
sorted recordset, such as in a named query. In this case it may be simplest to
use DMax instead of DLookup.
Click to expand...

Good point, as I've had that issue happen before with the sorting of the
table (hence why I have my bins labeled A01-A09 instead of A1-A9). I'll fix
that today, I didn't have time to mess with it Friday.
I don't quite understand the problem you are having when you run the code
concurrently on two machines. I know you described it in an earlier
posting, but please describe, with field names and values, the expected
result as opposed to the result you are getting when two users enter records
concurrently. Running the code again when there is an error should do what
you need, provided an error is being generated.
Click to expand...

Ok, the problem is this. User A commits their entry into their front end
form. Code runs to determine which bin they need to use. User B commits their
entry into their front end form at the same time, and their code runs.
Somehow what's happening is that both users end up with their code looking at
the Vendor Name field of [Bin Locations and Counts Table] at the same time,
and since neither one actually grabs the bin location at that point, they
both see the same bin as being available. I've tried to put in a function
that checks to see if another user is currently writing to that table, but
the function finishes it's check before the table that it updates is actually
updated, because the table is stored in the back end db on the network.

But in discussing this with you so far this morning, I think I've come to a
workable solution. I'll force the database to check the temp table I'm using
as my check table, then update that table and wait for five seconds. That way
if anybody else tries to check the temp table during that time, they'll
automatically be forced to wait. Of course, there are problems with this
solution as well, but I'm going to see if the concept will work and I'll deal
with the problems afterwards.
 
Yay! I think I finally fixed it!

I built a check code to verify which user had accessed the temp table most
recently. If the user is "None", then nobody's using the table, and the
database updates that table with the current username. Then it waits five
seconds before continuing, to make sure that the table updated properly
(trying to account for network lag, our LAN is ridiculously slow for as big
of a company as we are). If another user hits the temp table and changes the
username stored in it before the table updates, the first user will have to
wait for the second user to finish. Likewise, if the second user hits the
temp table after the update finishes, they will have to wait for the first
one to finish.

Private Sub Talk_Time_AfterUpdate()
DoCmd.SetWarnings False
DoCmd.OpenForm "UsernameForm", , , , , acHidden
RunProcess:
If DLookup("Username", "User Actions Table", "Username = 'None'") = "None"
Then
Forms![UsernameForm].[Username] = UsernameTag
DoCmd.OpenQuery "Update User Actions Table"
DoCmd.OpenForm "Delay Form", , , , , acDialog
If DLookup("Username", "User Actions Table", "Username = '" &
UsernameTag _
& "'") = UsernameTag Then
Me![Scan Time] = Now() & " - " & UsernameTag
DoCmd.OpenQuery "Add New Record"
Call BinLocationsNew(Me![Vendor Name])
Else
GoTo FiveSecDelay
End If
....
(skipped a bunch of form formatting stuff... I had to unbind the form
to make this work, otherwise it kept saying it couldn't save the record)
....
Else
FiveSecDelay:
DoCmd.OpenForm "Delay Form", , , , , acDialog
GoTo RunProcess
End If
DoCmd.OpenQuery "Reset User Action Table"
DoCmd.Close acForm, "UsernameForm"
DoCmd.SetWarnings True
End Sub

I know I did the GoTo sin again, but I couldn't figure out another way to
make it loop back to the beginning with subs. In a way, it IS an error
handler... it's saying that the table is not available, try again later.
 
I have to admit I still don't quite understand all of the nuances, but your
code is definitely much easier to follow, and I expect it will be easier to
maintain too. I would have expected problems from a table or query name
with spaces, but not surrounded by brackets.
If the five-second delay is working for you, good, but I wonder if that is
going to be satisfactory over time. If the newtwork is always slow there
may not be much you can do about that, but it could be that your database is
presenting some limitations that could be overcome. This article is a good
summary:
http://www.granite.ab.ca/access/performancefaq.htm
I found the persistent connection to the back end to be especially helpful.
I don't know if this applies to your situation: In one of my databases the
user starts a record. A function determines the next available number, and
assigns it to a variable. The number is displayed, but the record is not
yet saved. Another user may start a record at the same time, which is
assigned the same number since the first record has not yet been saved.
Later, when Access attempts to save the record, the function is run again,
and the result is compared to the variable. The user who finishes first
keeps the number. For the second user, the comparison shows the numbers are
different, and a new number is generated.
I could have just waited to assign the number when the record is saved, but
the users wish to see the number right away.
I wonder if you could do something similar. Rather than running the code
directly in the After Update event, create a function that you can call both
when the record is started and in the After Update event. This is just a
thought for if and when you revisit the project. Binding and unbinding the
form at run time may be working for now, but I suspect you will wish to find
a different approach sooner or later. Good luck with the project.

Nicholas Scarpinato said:
Yay! I think I finally fixed it!

I built a check code to verify which user had accessed the temp table most
recently. If the user is "None", then nobody's using the table, and the
database updates that table with the current username. Then it waits five
seconds before continuing, to make sure that the table updated properly
(trying to account for network lag, our LAN is ridiculously slow for as
big
of a company as we are). If another user hits the temp table and changes
the
username stored in it before the table updates, the first user will have
to
wait for the second user to finish. Likewise, if the second user hits the
temp table after the update finishes, they will have to wait for the first
one to finish.

Private Sub Talk_Time_AfterUpdate()
DoCmd.SetWarnings False
DoCmd.OpenForm "UsernameForm", , , , , acHidden
RunProcess:
If DLookup("Username", "User Actions Table", "Username = 'None'") = "None"
Then
Forms![UsernameForm].[Username] = UsernameTag
DoCmd.OpenQuery "Update User Actions Table"
DoCmd.OpenForm "Delay Form", , , , , acDialog
If DLookup("Username", "User Actions Table", "Username = '" &
UsernameTag _
& "'") = UsernameTag Then
Me![Scan Time] = Now() & " - " & UsernameTag
DoCmd.OpenQuery "Add New Record"
Call BinLocationsNew(Me![Vendor Name])
Else
GoTo FiveSecDelay
End If
...
(skipped a bunch of form formatting stuff... I had to unbind the form
to make this work, otherwise it kept saying it couldn't save the record)
...
Else
FiveSecDelay:
DoCmd.OpenForm "Delay Form", , , , , acDialog
GoTo RunProcess
End If
DoCmd.OpenQuery "Reset User Action Table"
DoCmd.Close acForm, "UsernameForm"
DoCmd.SetWarnings True
End Sub

I know I did the GoTo sin again, but I couldn't figure out another way to
make it loop back to the beginning with subs. In a way, it IS an error
handler... it's saying that the table is not available, try again later.
Click to expand...
 
Back
Top