asked on

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

Leigh Purvis

This any better?

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

Leigh Purvis

Hmm - but why do you open a recordset? :-S

ASKER CERTIFIED SOLUTION

Eric Sherman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

infolurk

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

rockiroads

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