Using DLookup and multiple criteria to return value

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
        Else
            bOutput = False
        End If
   
    myrecordset.Close
    myDatabase.Close
    Set myrecordset = Nothing
    Set myDatabase = Nothing

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

End Function
jcpeveAsked:
Who is Participating?
 
Eric ShermanAccountant/DeveloperCommented:
Is [CPTYCASE_ID] Text or Numeric???

Try this ...

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

ET
0
 
Leigh PurvisDatabase DeveloperCommented:
This any better?

varX = Nz(DLookup("[CPTYCASE_ID]", "CPTYCASE_STS", "[CPTYCASE_ID] = " & Me!txtCaseID & " And [STS_ID] = 54"))
0
 
Leigh PurvisDatabase DeveloperCommented:
Hmm - but why do you open a recordset?  :-S
0
 
infolurkCommented:
Have you tried tagging out the errorhandler and seeing which line is causing the problem?
0
 
rockiroadsCommented:
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
else
    bOutput = True
end if

or if form code, replace sCaseID with txtCaseID.value


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.