Return message when search returns no records

[mkallover]

I have some VB code that looks at a couple of different text boxes on a form and performs a search using that box if there is a value:

If (Not IsNull(Me![srchNPI])) Then
    NPISearch = Nz(DLookup("PRESBR_ID", "TBL_PRESBR_NPI", "PRESBR_NPI = '" & Forms!frmMain!srchNPI & "'"), 0)
    Me![IngenixID] = NPISearch
    Me!frmNameSub.Requery
    Me!frmAddressSub.Requery
    Me!frmSancSub.Requery
    Me!frmPhoneSub.Requery
    Exit Sub
    End If

This search works great provided that the NPI value that is entered can be matched by the Dlookup. However, if the NPI is not found there isn't a good indicator to the user that something is awry. I've tried looking for a solution but everything I can find is not quite what I need. Really all I need is a message box to pop up and let them know the NPI wasn't found. Can I add that without performing the Dlookup twice?

 

[jrbarnett]

If (Not IsNull(Me![srchNPI])) Then
If Len (DLookup("PRESBR_ID", "TBL_PRESBR_NPI", "PRESBR_NPI = '" & Forms!frmMain!srchNPI & "'" & "")) > 0 Then
NPISearch = Nz(DLookup("PRESBR_ID", "TBL_PRESBR_NPI", "PRESBR_NPI = '" & Forms!frmMain!srchNPI & "'"), 0)
Me![IngenixID] = NPISearch
Me!frmNameSub.Requery
Me!frmAddressSub.Requery
Me!frmSancSub.Requery
Me!frmPhoneSub.Requery
Exit Sub
Else
Msgbox "No search results found", vbOkOnly+vbInformation
End If
Else
Msgbox "please enter a search term", vbOkOnly+vbInformation
End If

 

[PHV]

Something like this ?

If Trim(Me!srchNPI & "") <>"" Then
  Me!IngenixID = DLookup("PRESBR_ID", "TBL_PRESBR_NPI", "PRESBR_NPI='" & Me!srchNPI & "'")
  If IsNull(Me!IngenixID) Then
    MsgBox "NPI " & Me!srchNPI & " NOT FOUND"
  Else
    Me!frmNameSub.Requery
    Me!frmAddressSub.Requery
    Me!frmSancSub.Requery
    Me!frmPhoneSub.Requery
  End If
  Exit Sub
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mkallover]

Thanks guys. I think I'll try PHV's first because it will avoid two calls to the DB. The tables I'm using are on the network and they're not always the fastest things to access.