Re: Need expert coding help with a debugging issue.

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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

Comments inline:

"BruceM" wrote:

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.

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.

"Nicholas Scarpinato" <NicholasScarpinato@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote
in message news:DA75C1FB-3F16-43B9-8089-805E763AF3CF@xxxxxxxxxxxxxxxx
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.

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.

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.

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.


.

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: 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)
  • Re: Poly Couples
    ... Essentially - different versions for different clients. ... insist that you use their existing database which is from a different ... vendor, and the structure is different than the one you use originally. ... characters would mark variable insertion place-holders in the SQL. ...
    (comp.object)
  • Re: Poly Couples
    ... business software" example mentioned in this thread. ... But the communication mechanism with the database remains the same. ... That doesn't mean that other languages (such as sql, ... vendor, and the structure is different than the one you use originally. ...
    (comp.object)