J
jsc3489
Source: somewhere in
http://groups.google.com/groups?q=microsoft.public.access+find+button&start=20&hl=en&;
Thanks to Mr. Goldgar, I used this little jewel with awsome results.
I did make some minor name changes that differ from the original posting,
however, Mr. Goldgar deserves all the credit.
----------------------------------------------------------
By Dirk Goldgar, MS Access MVP
Okay, here's what I have done to make something that seems to do what
you're asking. The idea is to have a simple popup search form that
operates very similarly to the way the built-in Find dialog would work
if the proper options were selected, but without displaying anything
more than the bare minimum.
Create a new form in design view. Set the following properties for the
form itself:
Format tab
-----------
Caption: Find
Default View: Single Form
Scroll Bars: Neither
Record Selectors: No
Navigation Buttons: No
Dividing Lines: No
Border Style: Dialog
Other tab
-----------
Pop Up: Yes
Turn off the control wizards and add the following controls:
(1) a text box with its associated label. Name the text box
"txtFindWhat". Set the caption of the label to something like "Enter
what you want to find:".
(2) a command button. Name the button "cmdFind" and set its caption
to "Find".
(3) optionally, a label to go over or beside the button, with a
caption along the lines of "Then click this button:".
Adjust the sizes, shapes, and positions of these controls to suit you.
Save the form now (without closing it), giving it the name
"Find Record".
Click the "Code" button on the toolbar, or else click menu items View ->
Code. That will put you in the VB Editor looking at the newly created
code module for this form. Copy the code below (between the "'-----
start of code -----" and "'----- end of code -----" lines) and paste it
over whatever may currently be displayed in the form's module:
'----- start of code -----
Option Compare Database
Option Explicit
Dim mstrFormToSearch As String
Private Sub cmdFind_Click()
Static strLastFind As String
Dim ctlFocus As Access.Control
Dim strTemp As String
Dim I As Integer
If IsNull(Me.txtFindWhat) Then Exit Sub
With Forms(mstrFormToSearch)
.SetFocus
Set ctlFocus = .ActiveControl
On Error Resume Next
strTemp = ctlFocus.ControlSource
If Err.Number <> 0 Then
For I = 0 To .Controls.Count - 1
Set ctlFocus = .Controls(I)
If ctlFocus.Enabled = True Then
Err.Clear
strTemp = ctlFocus.ControlSource
If Err.Number = 0 Then
Exit For
End If
End If
Next I
End If
ctlFocus.SetFocus
On Error GoTo 0
End With
If Me.txtFindWhat = strLastFind Then
DoCmd.FindNext
Else
DoCmd.FindRecord _
Me.txtFindWhat.Value, _
acAnywhere, _
False, _
acSearchAll, _
False, _
acAll, _
True
strLastFind = Me.txtFindWhat
End If
End Sub
Private Sub Form_Open(Cancel As Integer)
On Error Resume Next
mstrFormToSearch = Screen.ActiveForm.Name
If Len(mstrFormToSearch) = 0 Then
MsgBox "There's no active form to search!"
DoCmd.Close acForm, Me.Name, acSaveNo
End If
End Sub
'----- end of code -----
Click menu items Debug -> Compile (your project). If any compile errors
were found, you'll hjave to fix them and recompile. When you get a
clean compile, click the Save button on the toolbar.
Switch back to the database application window. Save and close
Find Record.
Now open the form you want to to use this search function on. Put a
command button on the form, name it "cmdSearch" (for example), and
create this event procedure for its Click event:
'----- start of search-button code -----
Private Sub cmdSearch_Click()
DoCmd.OpenForm "Find Record"
End Sub
'----- end of search-button code -----
Compile and save that form.
That ought to do it. Clicking the search button should open your find
dialog form, on which you can enter what you want to search for and
click the Find button to find the first record and field that contains
it. If you then click the Find button again -- without modifying the
search text -- it should find the next occurrence of that text, and so
on.
Please bear in mind that this is fairly rough, hasn't been tested in a
wide variety of circumstances, and has no error-handling. Any polish is
up to you. Let me know how it works out.
From Dirk Goldgar, MS Access MVP
http://groups.google.com/groups?q=microsoft.public.access+find+button&start=20&hl=en&;
Thanks to Mr. Goldgar, I used this little jewel with awsome results.
I did make some minor name changes that differ from the original posting,
however, Mr. Goldgar deserves all the credit.
----------------------------------------------------------
By Dirk Goldgar, MS Access MVP
Okay, here's what I have done to make something that seems to do what
you're asking. The idea is to have a simple popup search form that
operates very similarly to the way the built-in Find dialog would work
if the proper options were selected, but without displaying anything
more than the bare minimum.
Create a new form in design view. Set the following properties for the
form itself:
Format tab
-----------
Caption: Find
Default View: Single Form
Scroll Bars: Neither
Record Selectors: No
Navigation Buttons: No
Dividing Lines: No
Border Style: Dialog
Other tab
-----------
Pop Up: Yes
Turn off the control wizards and add the following controls:
(1) a text box with its associated label. Name the text box
"txtFindWhat". Set the caption of the label to something like "Enter
what you want to find:".
(2) a command button. Name the button "cmdFind" and set its caption
to "Find".
(3) optionally, a label to go over or beside the button, with a
caption along the lines of "Then click this button:".
Adjust the sizes, shapes, and positions of these controls to suit you.
Save the form now (without closing it), giving it the name
"Find Record".
Click the "Code" button on the toolbar, or else click menu items View ->
Code. That will put you in the VB Editor looking at the newly created
code module for this form. Copy the code below (between the "'-----
start of code -----" and "'----- end of code -----" lines) and paste it
over whatever may currently be displayed in the form's module:
'----- start of code -----
Option Compare Database
Option Explicit
Dim mstrFormToSearch As String
Private Sub cmdFind_Click()
Static strLastFind As String
Dim ctlFocus As Access.Control
Dim strTemp As String
Dim I As Integer
If IsNull(Me.txtFindWhat) Then Exit Sub
With Forms(mstrFormToSearch)
.SetFocus
Set ctlFocus = .ActiveControl
On Error Resume Next
strTemp = ctlFocus.ControlSource
If Err.Number <> 0 Then
For I = 0 To .Controls.Count - 1
Set ctlFocus = .Controls(I)
If ctlFocus.Enabled = True Then
Err.Clear
strTemp = ctlFocus.ControlSource
If Err.Number = 0 Then
Exit For
End If
End If
Next I
End If
ctlFocus.SetFocus
On Error GoTo 0
End With
If Me.txtFindWhat = strLastFind Then
DoCmd.FindNext
Else
DoCmd.FindRecord _
Me.txtFindWhat.Value, _
acAnywhere, _
False, _
acSearchAll, _
False, _
acAll, _
True
strLastFind = Me.txtFindWhat
End If
End Sub
Private Sub Form_Open(Cancel As Integer)
On Error Resume Next
mstrFormToSearch = Screen.ActiveForm.Name
If Len(mstrFormToSearch) = 0 Then
MsgBox "There's no active form to search!"
DoCmd.Close acForm, Me.Name, acSaveNo
End If
End Sub
'----- end of code -----
Click menu items Debug -> Compile (your project). If any compile errors
were found, you'll hjave to fix them and recompile. When you get a
clean compile, click the Save button on the toolbar.
Switch back to the database application window. Save and close
Find Record.
Now open the form you want to to use this search function on. Put a
command button on the form, name it "cmdSearch" (for example), and
create this event procedure for its Click event:
'----- start of search-button code -----
Private Sub cmdSearch_Click()
DoCmd.OpenForm "Find Record"
End Sub
'----- end of search-button code -----
Compile and save that form.
That ought to do it. Clicking the search button should open your find
dialog form, on which you can enter what you want to search for and
click the Find button to find the first record and field that contains
it. If you then click the Find button again -- without modifying the
search text -- it should find the next occurrence of that text, and so
on.
Please bear in mind that this is fairly rough, hasn't been tested in a
wide variety of circumstances, and has no error-handling. Any polish is
up to you. Let me know how it works out.
From Dirk Goldgar, MS Access MVP