Change "Find & Replace" dialogue defaults

[Keith Nichols]

Hi Guys,

Is there any way to set the "Match" criteria to "Any part of field" in VBA?

The database I have holds projects with fairly long and iregular names. When searching, you never know where the key word will be until you see the project you are looking for. Some users who have to navigate to many records are finding it a nuisance having to click the match list every time.

I have partly resolved the problem by filtering and ordering the project details form to match the weekly report used by managers. This has greatly reduced the requirement to search, but I would like to know if this is possible.

The code below is from the standard record navigation/find record button.

Private Sub cmd_Find_Record_Click()
On Error GoTo Err_cmd_Find_Record_Click


    Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_cmd_Find_Record_Click:
    Exit Sub

Err_cmd_Find_Record_Click:
    MsgBox Err.Description
    Resume Exit_cmd_Find_Record_Click
    
End Sub

Cheers,

Keith.

 

[MarkK]

Use Sendkeys in Access Dialog

Private Sub cmd_Find_Record_Click()
  Screen.PreviousControl.SetFocus
  DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
  [COLOR="DarkRed"]SendKeys "{TAB}{TAB}{UP}^{TAB}^{TAB}"[/COLOR] 
End Sub

Cheers,

 

[ghudson]

Private Sub cmd_Find_Record_Click()
  Screen.PreviousControl.SetFocus
  DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
  [COLOR="DarkRed"]SendKeys "{TAB}{TAB}{UP}^{TAB}^{TAB}"[/COLOR] 
End Sub

Cheers,

Yuck, using SendKeys and the old DoCmd.DoMenuItem commands.

DoCmd.RunCommand acCmdFind

Find and replace default settings

Searching the forum is a great way to discover and learn the answers to your Access programming questions.

 

[Keith Nichols]

Hi Ghudson,

I have searched the help files as advised. I find I'm still too new to all this to be able to solve my problem with the information availbale from the help files. It looks like I need 1 line of code but it is beyond my skills.

Screen.PreviousControl.SetFocus
    DoCmd.RunCommand acCmdFind
    Application.SetOption "Match", 0

Any pointers?



Extracts from Access VBA Help:

The following tables list the names of all options that can be set or returned from code and the tabs on which they can be found in the Options dialog box, followed by the corresponding string argument that you must pass to the SetOption or GetOption method.

If the option is Then the setting argument is
An option button in an option group, or an option in a combo box or a list box An integer corresponding to the option's position in the option group or list (starting with zero [0])
Option text String argument
Default find/replace behavior Default Find/Replace Behavior


For options with settings that are choices in list boxes or combo boxes, the GetOption method returns a number corresponding to the position of the setting in the list. Indexing begins with zero, so the GetOption method returns zero for the first item, 1 for the second item, and so on. For example, if the Default Field Type option on the Tables/Queries tab is set to AutoNumber, the sixth item in the list, the GetOption method returns 5.
To set this type of option, specify the option's numeric position within the list as the setting argument for the SetOption method. The following example sets the Default Field Type option to AutoNumber:
Application.SetOption "Default Field Type", 5

Application.SetOption "Match Type", 5

Regards,

Keith.

 

[MarkK]

I too can't find a SetOption option to set the "Match" combo box in the find replace dialog nor does there appear to be an option on the Access->Tools->Options dialog that alters its default. Moreso, the Match combo seems to retain the setting I last gave it rather than respond to a change to the Fast, General, or whatever other search setting in Options.
Though my sendkeys solution got thumbs down for pretty in some quarters, it's simple and it works.
But I'd amend as follows...

    Screen.PreviousControl.SetFocus
    DoCmd.RunCommand acCmdFind
    SendKeys "{TAB}{TAB}{HOME}^{TAB}^{TAB}"

 

[Keith Nichols]

Thanks Lagbolt. The sendkeys works very well and has the advantage of being fairly obvious for those that come after me in maintaining the database.

GHudson,
I'm intrigued by the hint of a more elegant way to achieve this. Any hints on a way forward will be gratefully received.

Regards to one and all,

Keith.

 

[prmitchell]

thank you lagbolt

SendKeys "{TAB}{TAB}{HOME}^{TAB}^{TAB}"
doesn't seem a prolgramming and parameter approach
but effective as you suggested

I'd rather program a DoCmd.FindNext than use the Action Menu approach. I don't understand why VB doesn't cater for this.