Using DLookup and multiple criteria to return value

Posted on 2006-05-24
Last Modified: 2008-02-01
Whenever I run this code, it brings back an error.  I have tried all kinds of changes and nothing seems to work.  Any ideas?

Code is as follows:

Public Function CheckACCT_ADD(ByVal sCASEID As Integer) As Boolean
On Error GoTo ErrorHandling
    Dim bOutput As Boolean
    Dim varX As Variant
    Dim myrecordset As DAO.Recordset
    Dim myDatabase As DAO.Database
    Set myDatabase = CurrentDb
    Set myrecordset = myDatabase.OpenRecordset("CPTYCASE_STS", dbOpenDynaset)
    varX = Nz(DLookup("[CPTYCASE_ID]", "CPTYCASE_STS", "[CPTYCASE_ID] = " & Me!txtCaseID And "[STS_ID] = 54"))
    If varX = Me.txtCaseID.Value Then
            bOutput = True
            bOutput = False
        End If
    Set myrecordset = Nothing
    Set myDatabase = Nothing

    CheckACCT_ADD = bOutput
    Exit Function
    MsgBox Err.Description
    Resume ExitHere
    Exit Function

End Function
Question by:jcpeve
    LVL 44

    Expert Comment

    by:Leigh Purvis
    This any better?

    varX = Nz(DLookup("[CPTYCASE_ID]", "CPTYCASE_STS", "[CPTYCASE_ID] = " & Me!txtCaseID & " And [STS_ID] = 54"))
    LVL 44

    Expert Comment

    by:Leigh Purvis
    Hmm - but why do you open a recordset?  :-S
    LVL 19

    Accepted Solution

    Is [CPTYCASE_ID] Text or Numeric???

    Try this ...

    varX = Nz(DLookup("[CPTYCASE_ID]", "CPTYCASE_STS", "[CPTYCASE_ID] = '" & Me!txtCaseID & "' And [STS_ID] = 54"))

    LVL 8

    Expert Comment

    Have you tried tagging out the errorhandler and seeing which line is causing the problem?
    LVL 65

    Expert Comment

    just a thought

    u pass in a integer variable

    Public Function CheckACCT_ADD(ByVal sCASEID As Integer) As Boolean

    Now this function is public, I would assume this is in a module? There is no real need to create public procedures in forms, unless u want another form to access it.
    incidentally, prefixing with s usually refers to a string, prefix with i e.g. iCaseID

    If this is in a module and u want to check the variable passed in, then the code has to use that i.e.  Me!txtCaseID refers to a txtbox on the form and this assumes to be form code then

    varX = Nz(DLookup("CPTYCASE_ID", "CPTYCASE_STS", "CPTYCASE_ID = " & sCaseID & " And STS_ID = 54")

    Note, u do not need square brackets, thats only useful if your fields has spaces in them

    another thing, DLOOKUP returns null if no records found, but you have wrapped with NZ so there is no need to define it as a variant
    Dim varX As Variant

    Also like Leigh says, why have u defined a recordset if you dont plan on using it?

    You can clean up your code, either use a recordset to find it
    in that case the open call is

    Set myrecordset = myDatabase.OpenRecordset("SELECT CPTYCASE_ID FROM CPTYCASE_STS WHERE CPTYCASE_ID = " & sCaseID & " AND STS_ID= 54", dbOpenDynaset)

    or simply use DLOOKUP

    Now it seems all you doing is checking the record exists, so u could just do this

    IF IsNull(DLookup("CPTYCASE_ID", "CPTYCASE_STS", "CPTYCASE_ID = " & sCaseID & " And STS_ID = 54")) Then
        bOutput = False
        bOutput = True
    end if

    or if form code, replace sCaseID with txtCaseID.value


    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now