Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Return message when search returns no records

Status
Not open for further replies.

mkallover

Programmer
Feb 6, 2008
88
US
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:

Code:
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?
 
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
 
Something like this ?
Code:
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
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top