dlookup to check record in another table

[muk5063]

I am having troubling making a DLookup to work correctly. A button would be pressed to print the record, before printing it would check if payment details have been entered into another table by looking for the same Case No. I have done the following but it doesn't seem to work. It prints even if a corresponding Case No is not found in 'OurCaseRefNo'. I managed to get it working but realised that when the 'ServicesRec' table had more than one entry with the same Case No (which is possible and OK) it refused to print. So I tried sorting that issue and made a bigger mess. I am quite new to VB code.

Any help highly appreciated.

Dim stDocName As String

If DLookup("[OurCaseRefNo]", "ServicesRec", "[Case No]=" & "'" & Me![Case No] & "'") > 0 Then

On Error GoTo Err_PrintCaseDetailsReport_Click
        stDocName = "CaseDetails"
        DoCmd.OpenReport stDocName, acNormal

Exit_PrintCaseDetailsReport_Click:
     Exit Sub

Err_PrintCaseDetailsReport_Click:
        MsgBox Err.Description
        Resume Exit_PrintCaseDetailsReport_Click

Else
   
    MsgBox "Entry of record is incomplete. Please enter payment details."
    stDocName = "ServicesRecForm"
    
    stLinkCriteria = "[OurCaseRefNo]=" & "'" & Me![Case No] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

End If

[CJ_London]

if you are going to post more than a line or two of code, strongly recommend you get in the habit of using the code tags to preserve indenting, otherwise code is difficult to read and many responders won't bother trying to follow it.

With regards this bit of code

If DLookup("[OurCaseRefNo]", "ServicesRec", "[Case No]=" & "'" & Me![Case No] & "'") > 0

the >0 implies Ourcaserefno is numeric, whereas your critieria implises that Caseno is text.

[June7]

If DLookup doesn't find anything it will return Null. So:

If Not IsNull(DLookup("[OurCaseRefNo]", "ServicesRec", "[Case No]='" & Me![Case No] & "'")) Then

or

If DLookup("[OurCaseRefNo]", "ServicesRec", "[Case No]='" & Me![Case No] & "'") & "" <> "" Then

or

If DCount("[OurCaseRefNo]", "ServicesRec", "[Case No]='" & Me![Case No] & "'") > 0 Then

If these fields are number type, remove the apostrophe delimiters.


Advise not to use spaces or special characters/punctuation (underscore is exception) in naming convention.

[muk5063]

Thank you both for your input. I have tried what you have suggested but to no avail. I face the same problem. I tried adding a breakpoint and added the following before the IF statement to see what was being returned from the dlookup.

MeCaseNo = Me![case no].Value

test = DLookup("[OurCaseRefNo]", "ServicesRec", MeCaseNo)

"034910" was always returned by dlookup and stored in 'test'. That is the 'OurCaseRefNo' of the first record in 'ServicesRec'. While 'MeCaseNo' was '1234515' or '987654' or something else.

Any ideas? thanks again

[June7]

Your WHERE CONDITION argument is incomplete. There is no logical operation. Look at the suggestions again. The statement in the original post was closer to correct.