Re: Search engine in a form.....searching the captions

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

.

Relevant Pages

  • Re: "Renaming Form Buttons From Macro"
    ... Also, as we've seen from the above trials, the actual Caption in the VBE ... "Dave Peterson" wrote: ... Private Sub UserForm_Initialize ... Dim myStartDate As Date ...
    (microsoft.public.excel.programming)
  • Re: "Renaming Form Buttons From Macro"
    ... me that the text in the button could change without the actual caption in VBE ... "Dave Peterson" wrote: ... Private Sub UserForm_Initialize ... Dim myStartDate As Date ...
    (microsoft.public.excel.programming)
  • Search engine in a form.....searching the captions
    ... This was published a lil ago by Dirk Goldgar. ... Dim mstrFormToSearch As String ... Private Sub Form_Open ... Click menu items Debug -> Compile. ...
    (microsoft.public.access.devtoolkits)
  • Re: FIND RECORD IN ACCESS - Please Help
    ... Adjust the sizes, shapes, and positions of these controls to suit you. ... Dim mstrFormToSearch As String ... Private Sub Form_Open ... Click menu items Debug -> Compile. ...
    (microsoft.public.access.modulesdaovba)
  • Re: "Renaming Form Buttons From Macro"
    ... Private Sub UserForm_Initialize ... Dim iCtr As Long ... Dim myStartDate As Date ... why bother changing the caption if the caption would change next year. ...
    (microsoft.public.excel.programming)