CODEWITHSUNDEEP
vbams-accessms-access-2010ms-access-2007
user2487679
user2487679

Reputation: 21

I'd like to make "Any Part of Field" Default (Access VBA)

In a simple Find action dialog box on an MSAccess (2007) form I want to make "Any Part of Field" the default value when the Find and Replace box appears.

The actual default value is "whole field". I though that I could change that with the following line:

DoCmd.FindRecord " ", acAnywhere, , , , , False

But that doesn't make any difference. The rest of the code works fine (associated with a command button). But that above line does nothing whether it's there or not. Please help. I have the following code:

Private Sub AppNAppFind_Click()
On Error GoTo AppNAppFind_Click_Err

On Error Resume Next
Err.Clear

DoCmd.FindRecord " ", acAnywhere, , , , , False
DoCmd.RunCommand acCmdFind


If (MacroError <> 0) Then
    Beep
    MsgBox MacroError.Description, vbOKOnly, ""
End If


AppNAppFind_Click_Exit:
   Exit Sub

AppNAppFind_Click_Err:
   MsgBox Error$
   Resume AppNAppFind_Click_Exit

I'm using Access 2007.

Upvotes: 2

Views: 6634

Answers (4)

Marco Bracher
Marco Bracher

Reputation: 21

By changing the Access Options Client Settings 'Find/Replace Default' from 'Quick Search' to 'General Search', the value 'Search in:' in the search box is automatically set from 'Current field' to 'Current document'. To set the default setting of the search function to 'Current field'/'Part of the field content', I recommend setting the registry entry '\HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Access\Settings\Default Find/Replace Behavior' to 4. For clients that do not have a complete Access installation on their computer, this value must be created again. Marco Bracher

Upvotes: 2

Timmy the Mouse
Timmy the Mouse

Reputation: 1

Private Sub MyButton_Click()
  Me.MyControl.SetFocus

  Dim WshShell As Object
  Set WshShell = CreateObject("WScript.Shell")
  
  WshShell.SendKeys "^f", True
  WshShell.SendKeys "%l{UP}", True
  WshShell.SendKeys "%ha", True
  WshShell.SendKeys "%n", True

End Sub

Upvotes: 0

bgmCoder
bgmCoder

Reputation: 6370

I'm not sure if this is an option in Access 2007, but in Access 2016, you can go to File > Options > Client Settings and look for Default find/replace behaviour and change it from Fast search to General search and it will set the default find settings to Look in: Current document and match in Any Part of Field.

This setting is for the client and will be effective in any access db you open and is persistent.

Upvotes: 1

Gord Thompson
Gord Thompson

Reputation: 123829

The following code works for me in Access 2010.

Private Sub myFind_Click()
DoCmd.GoToControl "=[Screen].[PreviousControl].[Name]"
DoCmd.FindRecord " ", acAnywhere, False, acSearchAll, False, acCurrent, False
DoCmd.RunCommand acCmdFind
End Sub

I verified that the Access default for "Find" is "Whole Field" on this machine, yet when I click my button the Find dialog has "Any Part of Field" selected for "Match".

Upvotes: 1

Related Questions