|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
This was published a lil ago by Dirk Goldgar. I was wondering if
anything (including Dirk) knew what it is need to implement a similar
search engine, but that instead of searching the ControlSources
searches the CAPTIONS. Maybe using a query or something??
Thanks
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
-----------
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
"frmFindRecord".
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 forms'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
frmFindRecord.
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 "frmFindRecord"
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.
--
Dirk Goldgar, MS Access MVP
|
|
I meant ANYBODY not "anything". I am sorry.
pandorasbox wrote:
[Quoted Text] > This was published a lil ago by Dirk Goldgar. I was wondering if
> anything (including Dirk) knew what it is need to implement a similar
> search engine, but that instead of searching the ControlSources
> searches the CAPTIONS. Maybe using a query or something??
> Thanks
>
> 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
> -----------
> 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
> "frmFindRecord".
>
>
> 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 forms'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
> frmFindRecord.
>
>
> 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 "frmFindRecord"
>
>
> 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.
>
>
> --
> Dirk Goldgar, MS Access MVP
|
|
This is what I tried::
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
Dim strSQL As String
Dim oSQL As DAO.QueryDef
If IsNull(Me.TxtFindWhat) Then Exit Sub
With Forms(mstrFormToSearch)
.SetFocus
Set ctlFocus = .ActiveControl
On Error Resume Next
strTemp = ctlFocus.Caption
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.Caption
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
strSQL = "SELECT * FROM TableInfo WHERE Caption Like '" &
strTarget & "'* "
oSQL.SQL = strSQL
' 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 -----
pandorasbox wrote:
[Quoted Text] > I meant ANYBODY not "anything". I am sorry.
> pandorasbox wrote:
> > This was published a lil ago by Dirk Goldgar. I was wondering if
> > anything (including Dirk) knew what it is need to implement a similar
> > search engine, but that instead of searching the ControlSources
> > searches the CAPTIONS. Maybe using a query or something??
> > Thanks
> >
> > 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
> > -----------
> > 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
> > "frmFindRecord".
> >
> >
> > 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 forms'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
> > frmFindRecord.
> >
> >
> > 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 "frmFindRecord"
> >
> >
> > 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.
> >
> >
> > --
> > Dirk Goldgar, MS Access MVP
|
|
So are you getting an error? If so, what is it? If not, what problem are you
encountering?
To be honest, I don't understand what's supposed to be happening in the For
I = 0 To .Controls.Count - 1 loop. It sets strTemp to the caption of
whatever control is the Active control (which, since you clicked on the
command button should be the command button...), then it resets strTemp to
the caption of the first enabled control it finds on the form specificed in
mstrFormToSearch.
Some obvious errors to are:
1) strSQL = "SELECT * FROM TableInfo WHERE Caption Like '" & strTarget & "'*
"
Your single quote needs to be after the asterisk, not before it. Exagerated
for clarity, it should be:
strSQL = "SELECT * FROM TableInfo WHERE Caption Like ' " & strTarget & " * '
"
2) oSQL.SQL = strSQL
You haven't instantiated oSQL so that it points to a specific QueryDef.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
"pandorasbox" <eme126[ at ]psu.edu> wrote in message
news:1155050854.887808.299460[ at ]m73g2000cwd.googlegroups.com...
[Quoted Text] > This is what I tried::
>
> 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
> Dim strSQL As String
> Dim oSQL As DAO.QueryDef
>
>
> If IsNull(Me.TxtFindWhat) Then Exit Sub
>
>
> With Forms(mstrFormToSearch)
>
>
> .SetFocus
>
>
> Set ctlFocus = .ActiveControl
>
>
> On Error Resume Next
> strTemp = ctlFocus.Caption
> 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.Caption
> 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
>
> strSQL = "SELECT * FROM TableInfo WHERE Caption Like '" &
> strTarget & "'* "
>
> oSQL.SQL = strSQL
>
> ' 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 -----
> pandorasbox wrote:
>> I meant ANYBODY not "anything". I am sorry.
>> pandorasbox wrote:
>> > This was published a lil ago by Dirk Goldgar. I was wondering if
>> > anything (including Dirk) knew what it is need to implement a similar
>> > search engine, but that instead of searching the ControlSources
>> > searches the CAPTIONS. Maybe using a query or something??
>> > Thanks
>> >
>> > 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
>> > -----------
>> > 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
>> > "frmFindRecord".
>> >
>> >
>> > 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 forms'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
>> > frmFindRecord.
>> >
>> >
>> > 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 "frmFindRecord"
>> >
>> >
>> > 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.
>> >
>> >
>> > --
>> > Dirk Goldgar, MS Access MVP
>
|
|
|