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

Message box Option Buttons

P: 28
I have a search function in my database, see below:-

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSearch_Click()
  2.     Dim SerialNumberRef As String
  3.     Dim Search As String
  4.  
  5. 'Check txtSearch for Null value or Nill Entry first.
  6.  
  7.     If IsNull(Me![txtsearch]) Or (Me![txtsearch]) = "" Then
  8.         MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
  9.         Me![txtsearch].SetFocus
  10.     Exit Sub
  11. End If
  12. '---------------------------------------------------------------
  13.  
  14. 'Performs the search using value entered into txtSearch
  15. 'and evaluates this against values in SerialNumber
  16.  
  17.  
  18.     DoCmd.GoToControl ("SerialNumber")
  19.     DoCmd.FindRecord Me!txtsearch
  20.  
  21.     SerialNumber.SetFocus
  22.     SerialNumberRef = SerialNumber.Text
  23.     txtsearch.SetFocus
  24.     Search = txtsearch.Text
  25.  
  26. 'If matching record found sets focus in SerialNumber and shows msgbox
  27. 'and clears search control
  28.  
  29.     If SerialNumberRef = Search Then
  30.         MsgBox "Match Found For: " & Search, , "Congratulations!"
  31.         SerialNumber.SetFocus
  32.         txtsearch = ""
  33.  
  34.     'If value not found sets focus back to txtSearch and shows msgbox
  35.         Else
  36.            MsgBox "Match Not Found For: " & Search & " - Please Try Again.", _
  37.             , "Invalid Search Criterion!"
  38.             txtsearch.SetFocus
  39.     End If
  40. End Sub
When the search finds the serial number it displays a message box with "Ok"
Because I have multiple entries with the same serial the search always only finds the first record.
Is there a way to adapt the above code so it displays a message box with 2 option buttons, one that says "Ok" and one that says "Next" whereby I can then use the Find Next function to search for the remaining serial numbers?

Ezzz
May 10 '10 #1

✓ answered by ADezii

This is the General idea:
Expand|Select|Wrap|Line Numbers
  1. 'If matching record found sets focus in SerialNumber and shows msgbox
  2. 'and clears search control
  3. Dim intResponse As Integer
  4.  
  5. 'Hopefully, SerialNumberRef and Search are Declared/Initialized somewhere
  6. If SerialNumberRef = Search Then
  7.   intResponse = MsgBox("Match Found For: " & Search, , vbRetryCancel, "Congratulations!")
  8.     If intResponse = vbRetry Then
  9.       'Do your FindNext Operation
  10.       DoCmd.FindNext
  11.     Else
  12.       'Satisfied by initial Find
  13.       DoCmd.Close   'Close the Form & Set Focus to a Control on it?
  14.       SerialNumber.SetFocus
  15.       txtsearch = ""
  16.     End If
  17. 'If value not found sets focus back to txtSearch and shows msgbox
  18. Else
  19.   MsgBox "Match Not Found For: " & Search & " - Please Try Again.", _
  20.           , "Invalid Search Criterion!"
  21.                txtsearch.SetFocus
  22. End If

Share this Question
Share on Google+
12 Replies


missinglinq
Expert 2.5K+
P: 3,532
Instead of using a Messagebox, you'll have to "roll your own," using a Popup/Modal form instead, with your own buttons. This is the standard workaround when you need a 'messagebox' that does things or looks differently from what an actual Messagebox can do or look.

Linq ;0)>
May 10 '10 #2

ADezii
Expert 5K+
P: 8,597
@Ezzz
Is there a way to adapt the above code so it displays a message box with 2 option buttons, one that says "Ok" and one that says "Next" whereby I can then use the Find Next function to search for the remaining serial numbers?
As linq explained, you would have to Roll Your Own, unless you wished to use a Message Box with Retry and Cancel Options, as in:
Expand|Select|Wrap|Line Numbers
  1. Dim intResponse As Integer
  2.  
  3. intResponse = MsgBox("Okie Dooky", vbRetryCancel, "YaDa - Yada")
  4.  
  5. If intResponse = vbRetry Then
  6.   'Do your FindNext Operation
  7. Else    'Cancel or Close Button
  8.   'Satisfied by initial Find
  9. End If
May 10 '10 #3

P: 28
ADezii
I did like your solution and tried to re-code my search formula but got bogged down with too many "If" statements. See below:-
Expand|Select|Wrap|Line Numbers
  1. 'If matching record found sets focus in SerialNumber and shows msgbox
  2. 'and clears search control
  3.  
  4.     If SerialNumberRef = Search Then
  5.         MsgBox "Match Found For: " & Search, , vbRetryCancel, "Congratulations!"
  6.         If Search = vbRetry Then
  7.         DoCmd.FindNext
  8.         'Do your FindNext Operation
  9.         Else
  10.         DoCmd.Close
  11.         'Satisfied by initial Find
  12.         SerialNumber.SetFocus
  13.         txtsearch = ""
  14.  
  15.     'If value not found sets focus back to txtSearch and shows msgbox
  16.         Else
  17.            MsgBox "Match Not Found For: " & Search & " - Please Try Again.", _
  18.             , "Invalid Search Criterion!"
  19.             txtsearch.SetFocus
As you may see I am very new to Vb coding and most of what I am using has been gleened from examples found on the net, but I am trying.

Ezzz
May 10 '10 #4

NeoPa
Expert Mod 15k+
P: 31,186
Ezzz, you have been told a number of times now to use the CODE tags when posting code. Please do so in future.

-Administrator.
May 10 '10 #5

ADezii
Expert 5K+
P: 8,597
@Ezzz
Syntax is all wrong, I'll rewrite and return later.
May 10 '10 #6

ADezii
Expert 5K+
P: 8,597
This is the General idea:
Expand|Select|Wrap|Line Numbers
  1. 'If matching record found sets focus in SerialNumber and shows msgbox
  2. 'and clears search control
  3. Dim intResponse As Integer
  4.  
  5. 'Hopefully, SerialNumberRef and Search are Declared/Initialized somewhere
  6. If SerialNumberRef = Search Then
  7.   intResponse = MsgBox("Match Found For: " & Search, , vbRetryCancel, "Congratulations!")
  8.     If intResponse = vbRetry Then
  9.       'Do your FindNext Operation
  10.       DoCmd.FindNext
  11.     Else
  12.       'Satisfied by initial Find
  13.       DoCmd.Close   'Close the Form & Set Focus to a Control on it?
  14.       SerialNumber.SetFocus
  15.       txtsearch = ""
  16.     End If
  17. 'If value not found sets focus back to txtSearch and shows msgbox
  18. Else
  19.   MsgBox "Match Not Found For: " & Search & " - Please Try Again.", _
  20.           , "Invalid Search Criterion!"
  21.                txtsearch.SetFocus
  22. End If
May 10 '10 #7

P: 28
Firstly apologies to the administrator re-the coding tags, hopefully I now understand where I was going wrong.

Secondly ADezi thanks for the responce tried your code but couldn't get it to work.
I have adapted it slightly (see below) and it nearly works!
When it carries out the "DoCmd.FindNext" function the search does indeed find the next serial number but ONLY the next one. What I'd hoped was to be able to keep retrying until it finds the desired item. Is there a way to loop the function?
Furthermore the close function is wrong. The "DoCmd.Close" actually wants to close the form and all I wanted it to do was stop the search. Also it doesnt like the SerialNumber.SetFocus txtsearch = "" after the close, but when i remove them it throws up a debug problem and highlights the DoCmd.Close statement.
Any ideas

Ezzz

Expand|Select|Wrap|Line Numbers
  1. 'If matching record found sets focus in SerialNumber and shows msgbox
  2. 'and clears search control
  3.  
  4. Dim intResponse As Integer
  5.  
  6. 'Hopefully, SerialNumberRef and Search are Declared/Initialized somewhere
  7.  
  8.     If SerialNumberRef = Search Then
  9.         intResponse = MsgBox("Match Found For: " & Search, vbRetryCancel, "Congratulations!")
  10.         SerialNumber.SetFocus
  11.         txtsearch = ""
  12.  
  13.     If intResponse = vbRetry Then
  14.       'Do your FindNext Operation
  15.       DoCmd.FindNext
  16.     Else
  17.       'Satisfied by initial Find
  18.       DoCmd.Close   'Close the Form & Set Focus to a Control on it?
  19.       SerialNumber.SetFocus
  20.       txtsearch = ""
  21.     End If
  22.  
  23.     'If value not found sets focus back to txtSearch and shows msgbox
  24.         Else
  25.            MsgBox "Match Not Found For: " & Search & " - Please Try Again.", _
  26.             , "Invalid Search Criterion!"
  27.             txtsearch.SetFocus
  28.     End If
  29. End Sub
May 12 '10 #8

NeoPa
Expert Mod 15k+
P: 31,186
Ezzz: Firstly apologies to the administrator re-the coding tags, hopefully I now understand where I was going wrong.
Good to hear Ezzz.

I can be PMed if you have any difficulties. I'm always happy to help members with the techniques of using the forum well and fully.

-NeoPa.
May 12 '10 #9

ADezii
Expert 5K+
P: 8,597
The basic concept would be:
Expand|Select|Wrap|Line Numbers
  1. Do
  2.   intResponse = MsgBox("Find Another Match?", vbRetryCancel, "Continue Find")
  3.     DoCmd.FindNext
  4. Loop Until intResponse = vbCancel
May 12 '10 #10

P: 28
Thanks ADezii
Almost works now, one minor flaw left, when i select the retry button the search does go to the next serial number in line and so on, great.
But when i find the asset i want and press the cancel button the search jumps one asset and displays the next in line not the one i stopped on?
See code below:-
Expand|Select|Wrap|Line Numbers
  1. 'If matching record found sets focus in SerialNumber and shows msgbox
  2. 'and clears search control
  3.  
  4. Dim intResponse As Integer
  5.  
  6. 'Hopefully, SerialNumberRef and Search are Declared/Initialized somewhere
  7.  
  8.     If SerialNumberRef = Search Then
  9.         intResponse = MsgBox("Match Found For: " & Search, vbRetryCancel, "Congratulations!")
  10.         SerialNumber.SetFocus
  11.         txtsearch = ""
  12.  
  13.     If intResponse = vbRetry Then
  14.       'Do your FindNext Operation
  15.       DoCmd.FindNext
  16.     'If this is not the asset you are looking for then try again
  17.     Do
  18.     intResponse = MsgBox("Find Another Match For: " & Search, vbRetryCancel, "Continue Find")
  19.     DoCmd.FindNext
  20.     Loop Until intResponse = vbCancel
  21.  
  22.     Else
  23.       'Satisfied by initial Find and close search
  24.  
  25.     End If
May 13 '10 #11

ADezii
Expert 5K+
P: 8,597
Try:
Expand|Select|Wrap|Line Numbers
  1. Do
  2.   intResponse = MsgBox("Find Another Match For: " & Search, vbRetryCancel, "Continue Find")
  3.     If intResponse = vbRetry Then DoCmd.FindNext
  4. Loop Until intResponse = vbCancel
May 13 '10 #12

P: 28
Well done thats it you've cracked it.
Much appreciated.
Regards
Ezzz
May 13 '10 #13

Post your reply

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