Comments and (maybe) corrections inline ...
Mary said:
Dirk,
Thank you so much. I have everything working with one
small problem. If there barcode is not found, it moves
to the next client record instead of staying on the
current one. Here is my code:
Private Sub FindBarCode_AfterUpdate()
If IsNull(Me.FindBarCode) Then Exit Sub
End If
This should be either just the one line:
If IsNull(Me.FindBarCode) Then Exit Sub
or three lines:
If IsNull(Me.FindBarCode) Then
Exit Sub
End If
Did something weird happen to the format when you posted it? I don't
see how this could have compiled without raising an error along the
lines of "End If without Block If".
Dim varClMat As String
Dim varOnlyClient As String
Dim varClient As String
To support changes I'm going to suggest farther down, change two of
these declarations to Variant:
Dim varClMat As Variant
Dim varClient As Variant
Dim varOnlyClient As String
Dim varBarCodeNo As String
Dim varCurrClient As String
Just a note: to most developers the "var" prefix means that the
variable so named is of the Variant data type. Similarly, a prefix of
"st" or "str" is used to denote a String variable. Different people use
different naming conventions -- I myself am not wholly consistent -- and
a few argue against prefix-naming entirely, but just using "var" for
"variable" isn't terribly helpful and may even be confusing. Here's a
link to an exposition of one of the commonly used naming conventions:
http://www.mvps.org/access/general/gen0012.htm
varBarCodeNo = FindBarCode.Text
Don't use the .Text property; use the .Value property, which is the
default property, so all you have to write is:
varBarCodeNo = FindBarCode
If IsNull(varClMat = DLookup
("[ClientMatterNo]", "dbo_Records", "[BarCodeNo]=""" &
varBarCodeNo & """")) = True Then
MsgBox "No matching barcode found.", _
vbInformation, "Not Found"
GoTo Exit_FindBarCode_AfterUpdate
Else
varClMat = DLookup
("[ClientMatterNo]", "dbo_Records", "[BarCodeNo]=""" &
varBarCodeNo & """")
End If
Why look up the ClientMatterNo twice? Try this instead of the above If
.... End If block (having declared varClMat as Variant):
varClMat = _
DLookup("ClientMatterNo", "dbo_Records", _
"BarCodeNo=""" & varBarCodeNo & """")
If IsNull(varClMat) Then
MsgBox "No matching barcode found.", _
vbInformation, "Not Found"
GoTo Exit_FindBarCode_AfterUpdate
End If
Note: I don't love the GoTo, but since you have some common cleanup
code at that labeled statement, I won't complain.
varOnlyClient = Mid(varClMat, 1, 6)
It appears that you are storing two pieces of information, the ClientNo
and the MatterNo, in one field, ClientMatterNo. This practice is
generally frowned upon, and will lead to cumbersome workarounds as you
try to query and otherwise manipulate your tables using SQL. This case
here, in which you must extract the ClientNo from the field in order to
look up the client -- and in which it is conceivable that you don't
*have* a matching client record for this ClientMatterNo -- is
symptomatic.
If IsNull(varClient = DLookup
("[clnum]", "dbo_client1", "[clnum]=""" & varOnlyClient
& """")) = True Then
MsgBox "No matching barcode found.", _
vbInformation, "Not Found"
GoTo Exit_FindBarCode_AfterUpdate
Else
varClient = DLookup("[clnum]", "dbo_client1", "[clnum]
=""" & varOnlyClient & """")
End If
Again, why look up the client number twice? Try this instead of the
above If ... End If block (having declared varClient as Variant):
varClient = _
DLookup("[clnum]", "dbo_client1", _
"[clnum]=""" & varOnlyClient & """")
If IsNull(varClient) Then
MsgBox "Barcode found, but client not found!", _
vbInformation, "Not Found"
GoTo Exit_FindBarCode_AfterUpdate
End If
Note that I've modified your message slightly, to more accurately
describe the situation. It seems to me that this is potentially a very
serious matter. It should not be possible, in a relational database,
for a "child" record to exist without its parent.
With Me.RecordsetClone
.FindFirst "[clnum]=""" & varClient & """"
If Not .noMatch Then
Me.Bookmark = .Bookmark
End If
End With
It seems to me the above ought to work. You may want to correct the
captialization of the .NoMatch property.
Forms!MainForm!clnum.SetFocus
Isn't this the same form that has the code that is running? Why not
just write:
Me.clnum.SetFocus
? On the other hand, I don't see the point of setting the focus here at
all, since you're about to set it to the [BarCode Subform] control
anyway.
With Me.[BarCode Subform].Form.RecordsetClone
.FindFirst "BarCodeNo = '" & Me.FindBarCode & "'"
If .noMatch Then
Hmm, there's that same miscapitalization of NoMatch. Do you have some
variable, field, or object named "noMatch"?
MsgBox "No matching barcode found.", _
vbInformation, "Not Found"
Else
Me.[BarCode Subform].Form.Bookmark = .Bookmark
Me.[BarCode Subform].SetFocus
Me.[BarCode Subform].Form.BarCodeNo.SetFocus
End If
End With
Exit_FindBarCode_AfterUpdate:
FindBarCode.Value = ""
Set it to Null, instead:
FindBarCode = Null
Exit Sub
End Sub
Is there something in my code making it move to the next
client record? Or do I need to code it so it captures
the current client and then if the barcode is not found,
use DLookup to lookup the current client, etc.
I'm not sure where in your code the move to the next client record is
being forced, so first try making the changes I've noted and then see if
it still happens.
