Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

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

Discussion in 'Information Technology' started by Gina Whipp, Jul 28, 2006.

Tags:
  1. Gina Whipp

    Gina Whipp
    Expand Collapse
    Guest

    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
     
  2. Loading...


  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    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

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Gina Whipp" <NotInterested@InViruses.com> wrote in message
    news:OtdPYiZmGHA.2372@TK2MSFTNGP04.phx.gbl...
    > 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
     
  4. Rob Parker

    Rob Parker
    Expand Collapse
    Guest

    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

    "Gina Whipp" <NotInterested@InViruses.com> wrote in message
    news:OtdPYiZmGHA.2372@TK2MSFTNGP04.phx.gbl...
    > 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
    >
     
  5. Gina Whipp

    Gina Whipp
    Expand Collapse
    Guest

    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


    "Rob Parker" <NOSPAMrobpparker@optusnet.com.au.REMOVETHIS> wrote in message
    news:%23qcSyDamGHA.3300@TK2MSFTNGP05.phx.gbl...
    > 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
    >
    > "Gina Whipp" <NotInterested@InViruses.com> wrote in message
    > news:OtdPYiZmGHA.2372@TK2MSFTNGP04.phx.gbl...
    >> 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
    >>

    >
    >
     
  6. Gina Whipp

    Gina Whipp
    Expand Collapse
    Guest

    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

    "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    news:O5C8Q%23ZmGHA.5052@TK2MSFTNGP04.phx.gbl...
    > 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
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Gina Whipp" <NotInterested@InViruses.com> wrote in message
    > news:OtdPYiZmGHA.2372@TK2MSFTNGP04.phx.gbl...
    >> 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

    >
    >
     

Share This Page