Searching records 1

[pollenman]

I would like to set up a command button on my form that will seach all records for a whole would or just appreviation. I can use the find command but i think my colleques will get confused. Is this possible?

Regards Terry

 

[beetee]

First of all I'm suffering from a monumental hay-fever attack so thanks.

Assuming you ment "whole word or just abbreviation", you can certainly set up a find dialog box that does your search. However, an abbreviation would take some special processing beyond simple VB functions. If you want to go with 'StartWith', you can deal with that intrinsically.

In a nutshell, let's assume that your users will click into the control they want to search, then click the 'find' button you provide.

You will want to create a form in which they can enter the find criteria. You can learn the find criteria by checking out the parameters to the DoCmd.FindRecord function.

First, record the control that had Focus before pressing the OK button:

' declared in a module
Dim PrevCtrl as control

Function GetPrevCtrl() as control
set GetPrevCtrl = PrevCtrl
end function

sub SetPrevCtrl()
set prevctrl = Screen.PreviousControl
end sub


------------------
Then, in the command button click routine, save the previous control, and open the search form

SetPrevCtrl
open the search form
docmd.openform "TheSearchForm"

-----------------

In the search form's OK button's event procedure

GetPrevCtrl.SetFocus
docmd.findrecord <params from search form>

HTH

 

[92hilux]

maybe in your query...

under criteria:
>>Like *&[txtYourWord]&*

That is wat i used to search for any part of a word or description.

Good luck

 

[pollenman]

Thanks beetee & 92hilux

I am pretty new to Access and am still finding my way

Can you explain >>Like *&[txtYourWord]&* in context

Thanks for your assistance

Terry

 

[pollenman]

beetee

how do you use this? :-(

Stupid of Chafford

 

[beetee]

After working with a bit, here's a solution.

There are several steps
Step 1) create a search params form
Step 2) create a module to record if a find
button was pressed
Step 3) add a find button to your form
Step 4) detect when the 'find' button on the search form
is pressed, and perform the find.

The search params form should contain a text box for the search string, a set of radio buttons for the match type (acEntire, acStart, acAnywhere), and other options as described by the Docmd.Find record arguments.

The module looks like this:

Option Compare Database
Option Explicit

Public Const c_DoNothing = 0
Public Const c_DoFind = 1
Public Const c_FormClosed = 2

Dim SearchMode As Integer

Sub SetSearchMode(ToWhat As Integer)
SearchMode = ToWhat
End Sub

Function GetSearchMode() As Integer
GetSearchMode = SearchMode
End Function
-----------------

The Find Button code (on the form to be searched) looks like this:

Private Sub FindRecordButton_Click()
Dim PrevCtrl As Control
Set PrevCtrl = Screen.PreviousControl
DoCmd.OpenForm &quot;SearchParamsForm&quot;
SetSearchMode c_DoNothing
Do While GetSearchMode() = c_DoNothing
DoEvents
Loop

Select Case GetSearchMode
Case c_DoNothing
Case c_DoFind
Dim SearchForm As Form
Set SearchForm = Forms![SearchParamsForm]
Me.SetFocus
PrevCtrl.SetFocus
DoCmd.FindRecord SearchForm.LookFor, acStart, False, acSearchAll
Case c_FormClosed
End Select
End Sub

NOTE that I didn't bother to set all the parameters to values contained in the Find form; that's an exercise left to the reader.

-------------------------------------------

The Find Params form code:

Private Sub Form_Close()
SetSearchMode c_DoNothing
End Sub

Private Sub GoButton_Click()
SetSearchMode c_DoFind
End Sub

-----------------------
Good Luck!!