Why doesn't this work? Please Help...

  • Thread starter Thread starter Gina Whipp
  • Start date Start date
G

Gina Whipp

Hi All,

Below is a piece of code that is suppose to trigger a pop-up menu but only
if there is a Chart of Accounts number. What is happening is the DLookUp
never triggers, instead I ALWAYS get the MsgBox "ugh!". Mind you that is
there for test purposes only. And yes the Payee field is a text field.

Oh, and while your at it perhaps explain why if the Payee has a apostraphe
in it I get an error, any way to trap that?

Thanks for any help you can give,
Gina Whipp


If DLookup("apPayee", "qryFindChartOfAccounts", "apPayee=" & "'" & cboPayee
& "'") <> Me.cboPayee Then
MsgBox "No Chart of Accounts number available for this Payee!",
vbInformation, "Checking Log"
DoCmd.CancelEvent
Else
MsgBox "ugh!" 'This ALWAYS triggers...
'If Not IsNull(Me.cboPayee) Then
'DoCmd.OpenForm "sfrFindChartOfAccounts", , , "apPayee=" & "'" &
cboPayee & "'"
'Else
'MsgBox "You MUST select a Payee first!", vbInformation,
"Checking Log"
'End If
End If
 
First issue is that DLookup() returns Null when there is no match, and Null
is not the same as False. Reverse your logic:
If DLookup("apPayee", "qryFindChartOfAccounts", _
"apPayee=" & "'" & cboPayee & "'") <> Me.cboPayee Then
MsgBox "ugh!"
Else
MsgBox "No Chart of Accounts number available for this Payee!", _
vbInformation, "Checking Log"
DoCmd.CancelEvent
End If

For details, see the last item in this article:
Common errors with Null
at:
http://allenbrowne.com/casu-12.html

For how to solve the problem with the apostrophy, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html
 
Hi Gina,

A dLookup does not "trigger". But if you mean that the inequality that you
are testing for is never true, then that's because your dLookup is returning
the field that you are testing on, so it will always match. I suspect you
should be testing whether an account number field exists - but I don't know
what it's called, and you wouldn't test it against the apPayee anyway :-).
I think that what you are trying to do is check whether there are any
account numbers for the apPayee. If that's so, then you would probably be
better to use a dCount expression, such as:

If dCount("*","qryFindChartOfAccounts", "apPayee=" & "'" & cboPayee &
"'") = 0 Then
...

The dCount will return the number of records in your query which have
apPayee equal to the selection in cboPayee. I'd also suggest putting the
test for no entry in cboPayee before this test, rather than within the If ..
Then, since if you don't, you'll need to cope with nulls in cboPayee in the
dCount (or dLookup) expression.

Finally, the apostrophe problem is caused because it is the same character
as the string delimiter around cboPayee, so when the expression is being
built the string gets terminated at the wrong place, and the remaining
section of the string in cboPayee then causes the error. There are a couple
of ways to avoid this, the easiest is to enter two double-quote characters
as the delimiter - you'll finish up with four double-quotes in your code,
thus:
... "apPayee = " & """" & cboPayee & """" ...

Note: you can include the first delimiter in the first portion of the
criteria string, thus:
... "apPayee = """ & cboPayee & """" ...

HTH,

Rob
 
Rob,

First of all, thank you for in depth response, you are right on target
(almost), you not only helped me to understand why what I was doing wouldn't
work but showed me how to make it work (the explaining was the most
important!).

But just to clarify I was looking for apPayee and not an account number.
Not all Payee's have an account number, that's the way this client wanted it
set up, not good I know but it works the way the client wants it to. (I
choose not to 'fight' that battle).

Thanks Again!
Gina Whipp
 
Allen,

Thank you for you response and the links! They will be helpful when I run
into this problem again... I have to make the same mistake at least twice
before I remember how to avoid it!

Gina Whipp
 
Back
Top