Re: Need expert coding help with a debugging issue.

Comments inline.

"Nicholas Scarpinato" <NicholasScarpinato@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:37BBAA6C-DBF5-4D9A-A5E3-8F0BF4A14937@xxxxxxxxxxxxxxxx
Comments in-line:

"BruceM" wrote:

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.

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.

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.

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.

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.

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.

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" <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

  • Re: Need expert coding help with a debugging issue.
    ... the database in a multi-user role. ... Most developers use GoTo only for error handling. ... code that tells the user exactly which bin location he or she should put the ... The bins are sorted by vendor. ...
    (microsoft.public.access.formscoding)
  • Re: Need expert coding help with a debugging issue.
    ... Is this a split database? ... you are checking whether the vendor's items are in any bin. ... I do not see how "[Vendor Name] = '0'" can return anything other than a record in which VendorName is the character 0. ... In this case it may be simplest to use DMax instead of DLookup. ...
    (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.
    ... The database is split and I'm still having issues with variables. ... 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: Poly Couples
    ... If you want to claim that OO is the consolution prize for lame database ... there is a metamodel which describes the hierarchy. ... Metamodel is stored in SQL, however, the data is stored in non ... vendor, and the structure is different than the one you use originally. ...
    (comp.object)