I have a search function in my database, see below:- - Private Sub cmdSearch_Click()
-
Dim SerialNumberRef As String
-
Dim Search As String
-
-
'Check txtSearch for Null value or Nill Entry first.
-
-
If IsNull(Me![txtsearch]) Or (Me![txtsearch]) = "" Then
-
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
-
Me![txtsearch].SetFocus
-
Exit Sub
-
End If
-
'---------------------------------------------------------------
-
-
'Performs the search using value entered into txtSearch
-
'and evaluates this against values in SerialNumber
-
-
-
DoCmd.GoToControl ("SerialNumber")
-
DoCmd.FindRecord Me!txtsearch
-
-
SerialNumber.SetFocus
-
SerialNumberRef = SerialNumber.Text
-
txtsearch.SetFocus
-
Search = txtsearch.Text
-
-
'If matching record found sets focus in SerialNumber and shows msgbox
-
'and clears search control
-
-
If SerialNumberRef = Search Then
-
MsgBox "Match Found For: " & Search, , "Congratulations!"
-
SerialNumber.SetFocus
-
txtsearch = ""
-
-
'If value not found sets focus back to txtSearch and shows msgbox
-
Else
-
MsgBox "Match Not Found For: " & Search & " - Please Try Again.", _
-
, "Invalid Search Criterion!"
-
txtsearch.SetFocus
-
End If
-
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
This is the General idea: - 'If matching record found sets focus in SerialNumber and shows msgbox
-
'and clears search control
-
Dim intResponse As Integer
-
-
'Hopefully, SerialNumberRef and Search are Declared/Initialized somewhere
-
If SerialNumberRef = Search Then
-
intResponse = MsgBox("Match Found For: " & Search, , vbRetryCancel, "Congratulations!")
-
If intResponse = vbRetry Then
-
'Do your FindNext Operation
-
DoCmd.FindNext
-
Else
-
'Satisfied by initial Find
-
DoCmd.Close 'Close the Form & Set Focus to a Control on it?
-
SerialNumber.SetFocus
-
txtsearch = ""
-
End If
-
'If value not found sets focus back to txtSearch and shows msgbox
-
Else
-
MsgBox "Match Not Found For: " & Search & " - Please Try Again.", _
-
, "Invalid Search Criterion!"
-
txtsearch.SetFocus
-
End If
12 5839
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)> @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: - Dim intResponse As Integer
-
-
intResponse = MsgBox("Okie Dooky", vbRetryCancel, "YaDa - Yada")
-
-
If intResponse = vbRetry Then
-
'Do your FindNext Operation
-
Else 'Cancel or Close Button
-
'Satisfied by initial Find
-
End If
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:- - 'If matching record found sets focus in SerialNumber and shows msgbox
-
'and clears search control
-
-
If SerialNumberRef = Search Then
-
MsgBox "Match Found For: " & Search, , vbRetryCancel, "Congratulations!"
-
If Search = vbRetry Then
-
DoCmd.FindNext
-
'Do your FindNext Operation
-
Else
-
DoCmd.Close
-
'Satisfied by initial Find
-
SerialNumber.SetFocus
-
txtsearch = ""
-
-
'If value not found sets focus back to txtSearch and shows msgbox
-
Else
-
MsgBox "Match Not Found For: " & Search & " - Please Try Again.", _
-
, "Invalid Search Criterion!"
-
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
NeoPa 32,556
Expert Mod 16PB
Ezzz, you have been told a number of times now to use the CODE tags when posting code. Please do so in future.
-Administrator.
@Ezzz
Syntax is all wrong, I'll rewrite and return later.
This is the General idea: - 'If matching record found sets focus in SerialNumber and shows msgbox
-
'and clears search control
-
Dim intResponse As Integer
-
-
'Hopefully, SerialNumberRef and Search are Declared/Initialized somewhere
-
If SerialNumberRef = Search Then
-
intResponse = MsgBox("Match Found For: " & Search, , vbRetryCancel, "Congratulations!")
-
If intResponse = vbRetry Then
-
'Do your FindNext Operation
-
DoCmd.FindNext
-
Else
-
'Satisfied by initial Find
-
DoCmd.Close 'Close the Form & Set Focus to a Control on it?
-
SerialNumber.SetFocus
-
txtsearch = ""
-
End If
-
'If value not found sets focus back to txtSearch and shows msgbox
-
Else
-
MsgBox "Match Not Found For: " & Search & " - Please Try Again.", _
-
, "Invalid Search Criterion!"
-
txtsearch.SetFocus
-
End If
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 - 'If matching record found sets focus in SerialNumber and shows msgbox
-
'and clears search control
-
-
Dim intResponse As Integer
-
-
'Hopefully, SerialNumberRef and Search are Declared/Initialized somewhere
-
-
If SerialNumberRef = Search Then
-
intResponse = MsgBox("Match Found For: " & Search, vbRetryCancel, "Congratulations!")
-
SerialNumber.SetFocus
-
txtsearch = ""
-
-
If intResponse = vbRetry Then
-
'Do your FindNext Operation
-
DoCmd.FindNext
-
Else
-
'Satisfied by initial Find
-
DoCmd.Close 'Close the Form & Set Focus to a Control on it?
-
SerialNumber.SetFocus
-
txtsearch = ""
-
End If
-
-
'If value not found sets focus back to txtSearch and shows msgbox
-
Else
-
MsgBox "Match Not Found For: " & Search & " - Please Try Again.", _
-
, "Invalid Search Criterion!"
-
txtsearch.SetFocus
-
End If
-
End Sub
NeoPa 32,556
Expert Mod 16PB 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.
The basic concept would be: - Do
-
intResponse = MsgBox("Find Another Match?", vbRetryCancel, "Continue Find")
-
DoCmd.FindNext
-
Loop Until intResponse = vbCancel
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:- - 'If matching record found sets focus in SerialNumber and shows msgbox
-
'and clears search control
-
-
Dim intResponse As Integer
-
-
'Hopefully, SerialNumberRef and Search are Declared/Initialized somewhere
-
-
If SerialNumberRef = Search Then
-
intResponse = MsgBox("Match Found For: " & Search, vbRetryCancel, "Congratulations!")
-
SerialNumber.SetFocus
-
txtsearch = ""
-
-
If intResponse = vbRetry Then
-
'Do your FindNext Operation
-
DoCmd.FindNext
-
'If this is not the asset you are looking for then try again
-
Do
-
intResponse = MsgBox("Find Another Match For: " & Search, vbRetryCancel, "Continue Find")
-
DoCmd.FindNext
-
Loop Until intResponse = vbCancel
-
-
Else
-
'Satisfied by initial Find and close search
-
-
End If
Try: - Do
-
intResponse = MsgBox("Find Another Match For: " & Search, vbRetryCancel, "Continue Find")
-
If intResponse = vbRetry Then DoCmd.FindNext
-
Loop Until intResponse = vbCancel
Well done thats it you've cracked it.
Much appreciated.
Regards
Ezzz
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Edward |
last post by:
I have a data entry form that allows the user to navigate around
entirely using the keyboard. However, it contains various option
button controls which are in the tab order. Whenever they are...
|
by: Saxman |
last post by:
I created a text box labeled txtText1.
The text box has a sample of text within.
The Enabled property is set to False and the Multiline property set to True.
I have four option buttons set...
|
by: PM |
last post by:
I have a question about option buttons and their values.
I have many option buttons on my forms, each set contained in their own
group boxes.
I simply want to be able to give them a value,...
|
by: Omey Samaroo |
last post by:
I have had many questions answered in this forum and I am trusting that one
of the readers may help me find a simple solution.
(1) Using a field called Date_Hired (type: Date/Time), how do I use...
|
by: Robert |
last post by:
Stephen,
I think I figured out the problem. I was able to get Check Boxes and Option
Buttons to work on my form by TURNING OFF RECORD SELECTORS on the form. Not
sure why this would make a...
|
by: tsnyder |
last post by:
I need to have an option button that allows editing to a field only
when it is checked.
|
by: sselvi |
last post by:
Hi!
I am using the visual basic applications in powerpoint to create a program which will enable the user to enter a mulitiple choice question and options for that question. For eg. if the user...
|
by: MAIjah |
last post by:
Sorry, just a newbie and what I call a 'bruteforce' programmer. I'm using a VBform in EXCEL to pass info to a worksheet. I have 7 option buttons (exclusive?, only one can be true) and want to...
|
by: beacon |
last post by:
Hello to everybody,
I have a section on a form that has 10 questions, numbered 1-10, with 3 option buttons per question. Each of the option buttons have the same response (Yes, No, Don't know),...
|
by: ahmed222too |
last post by:
how to use the option buttons in VBA (how to call the value of the option buttons ) and is it must to gather the option buttons in one option group ?
thanks
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |