By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,164 Members | 969 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,164 IT Pros & Developers. It's quick & easy.

Display message box when search is not succesful.

P: 31
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.
Sep 27 '08 #1
Share this Question
Share on Google+
8 Replies


ADezii
Expert 5K+
P: 8,669
Assuming you have a Field named [Tracking_Number] {STRING}, in a Table named tblOrders, here is a Template which you can use:
Expand|Select|Wrap|Line Numbers
  1. Dim strTrackingNum As String
  2. Dim blnTrackingNumExists As Boolean
  3. Dim strMsg As String
  4.  
  5. strTrackingNum = InputBox$("Please enter the Tracking Number.")
  6.  
  7. strMsg = "The Tracking Number [" & strTrackingNum & "] does not exist " & _
  8.          "in tblOrders!"
  9.  
  10. 'blnTrackingNumExists returns True if the Tracking Number exists,
  11. 'and False if it doesn't
  12. blnTrackingNumExists = DCount("*", "Employees", "[Tracking_Number] = '" & _
  13.                                     strTrackingNum & "'")
  14.  
  15. 'User selected Cancel or OK with No Entry, bye - bye
  16. If Len(strTrackingNum) = 0 Then
  17.   Exit Sub
  18. Else
  19.   If blnTrackingNumExists Then      'Tracking Number does exist
  20.     'Code if Tracking Number exists in tblOrders
  21.   Else
  22.     'Code if Tracking Number does not exist
  23.     MsgBox strMsg, vbExclamation, "Tracking Number Not Found"
  24.   End If
  25. End If
Sep 27 '08 #2

ADezii
Expert 5K+
P: 8,669
In Line #12 for Post #3, substitute tblOrders for Employees.
Sep 27 '08 #3

P: 31
Thanks for the help, it worked great.
Sep 27 '08 #4

ADezii
Expert 5K+
P: 8,669
Thanks for the help, it worked great.
Glad it worked out for you.
Sep 28 '08 #5

P: 31
I swear, i tried to change it but i couldn't figure it out. How do i make this work for a number field?
Sep 29 '08 #6

ADezii
Expert 5K+
P: 8,669
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:
Expand|Select|Wrap|Line Numbers
  1. Dim strTrackingNum As String
  2. Dim blnTrackingNumExists As Boolean
  3. Dim strMsg As String
  4.  
  5. strTrackingNum = InputBox$("Please enter the Tracking Number.")
  6.  
  7. strMsg = "The Tracking Number [" & strTrackingNum & "] does not exist " & _
  8.          "in tblOrders!"
  9.  
  10. 'blnTrackingNumExists returns True if the Tracking Number exists,
  11. 'and False if it doesn't
  12. blnTrackingNumExists = DCount("*", "tblOrders", "[Tracking_Number] = " & _
  13.                        Val(strTrackingNum)) > 0
  14.  
  15. 'User selected Cancel or OK with No Entry or it is Not a Number
  16. If Len(strTrackingNum) = 0 Or Not IsNumeric(strTrackingNum) Then
  17.   Exit Sub
  18. Else
  19.   If blnTrackingNumExists Then      'Tracking Number does exist
  20.     'Code if Tracking Number exists in tblOrders
  21.     MsgBox "Yes it's alive!"
  22.   Else
  23.     'Code if Tracking Number does not exist
  24.     MsgBox strMsg, vbExclamation, "Tracking Number Not Found"
  25.   End If
  26. End If
Sep 29 '08 #7

P: 31
Thanks again. You have really helped.
Sep 29 '08 #8

ADezii
Expert 5K+
P: 8,669
Thanks again. You have really helped.
The pleasure is all mine.
Sep 30 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.