Display message box when search is not succesful.

[crazyhouse]

I am using the following code on a search page.

myvalue = InputBox("Please enter the Tracking Number.")

stDocName = "Inspection_form"
stLinkCriteria = "[tracking_number]=" & "'" & [myvalue] & "'"

DoCmd.OpenForm stDocName, , , stLinkCriteria, acReadOnly

What i would like to know is how to open another form or msgbox if the [tracking_number] does not exist as a record.

Thanks in advance.

[ADezii]

Assuming you have a Field named [Tracking_Number] {STRING}, in a Table named tblOrders, here is a Template which you can use:

Dim strTrackingNum As String
Dim blnTrackingNumExists As Boolean
Dim strMsg As String
 
strTrackingNum = InputBox$("Please enter the Tracking Number.")
 
strMsg = "The Tracking Number [" & strTrackingNum & "] does not exist " & _
         "in tblOrders!"
 
'blnTrackingNumExists returns True if the Tracking Number exists,
'and False if it doesn't
blnTrackingNumExists = DCount("*", "Employees", "[Tracking_Number] = '" & _
                                    strTrackingNum & "'")
 
'User selected Cancel or OK with No Entry, bye - bye
If Len(strTrackingNum) = 0 Then
  Exit Sub
Else
  If blnTrackingNumExists Then      'Tracking Number does exist
    'Code if Tracking Number exists in tblOrders
  Else
    'Code if Tracking Number does not exist
    MsgBox strMsg, vbExclamation, "Tracking Number Not Found"
  End If
End If

[ADezii]

In Line #12 for Post #3, substitute tblOrders for Employees.

[crazyhouse]

Thanks for the help, it worked great.

[ADezii]

Thanks for the help, it worked great.

Glad it worked out for you.

[crazyhouse]

I swear, i tried to change it but i couldn't figure it out. How do i make this work for a number field?

[ADezii]

I swear, i tried to change it but i couldn't figure it out. How do i make this work for a number field?

This would be the closest approach to what you already have:

Dim strTrackingNum As String
Dim blnTrackingNumExists As Boolean
Dim strMsg As String
 
strTrackingNum = InputBox$("Please enter the Tracking Number.")
 
strMsg = "The Tracking Number [" & strTrackingNum & "] does not exist " & _
         "in tblOrders!"
 
'blnTrackingNumExists returns True if the Tracking Number exists,
'and False if it doesn't
blnTrackingNumExists = DCount("*", "tblOrders", "[Tracking_Number] = " & _
                       Val(strTrackingNum)) > 0
 
'User selected Cancel or OK with No Entry or it is Not a Number
If Len(strTrackingNum) = 0 Or Not IsNumeric(strTrackingNum) Then
  Exit Sub
Else
  If blnTrackingNumExists Then      'Tracking Number does exist
    'Code if Tracking Number exists in tblOrders
    MsgBox "Yes it's alive!"
  Else
    'Code if Tracking Number does not exist
    MsgBox strMsg, vbExclamation, "Tracking Number Not Found"
  End If
End If

[crazyhouse]

Thanks again. You have really helped.

[ADezii]

Thanks again. You have really helped.

The pleasure is all mine.