Need to be able to set the Find and Replace dialog's default settings for Look In:

If I am in a form and do a Find and Replace - I want the settings to be set to the following:

Look In:  the column that I selected.
Match:  Any Part of the Field.

I was first frustrated by the fact that what the default for Find and Replace's Match was Match:  Whole Field.
I was overjoyed by the following URL addressing this issue:
http://www.experts-exchange.com/Databases/MS_Access/Q_20424223.html?query=general+search+edit+find&clearTAFilter=true
Now I am again frustrated that I can't have the Look In:  automatically default to the column I have selected...which is probably because of the General search option.
LVL 1
stephenlecomptejrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

p912sCommented:
How to Set Defaults for the Find and Replace Dialog Box
http://support.microsoft.com/default.aspx?scid=kb;en-us;208923
0
thenelsonCommented:
This duplicates p912s link but also adds a trick to get rid of the replace option when needed.

This will show for how to program the find dialog:
http://support.microsoft.com/default.aspx?scid=kb;en-us;208923
Here is an example with some additional tricks:
    Dim SaveAllowEdits As Boolean
   
    Screen.PreviousControl.SetFocus   'find on the control that had focus just before the button was clicked
    SaveAllowEdits = Me.AllowEdits    'save the existing condition to return later
    Me.AllowEdits = False             'the replace tab will not show up if allowedits = false
    SendKeys "%hs%n"                  '%hs = start of field,  %n = leave curser in find what
    RunCommand acCmdFind              'open the find dialog
    Me.AllowEdits = SaveAllowEdits    'return allowedits to what it was
0
stephenlecomptejrAuthor Commented:
Please forgive me as I strain to understand the answer better.

The only thing I can do is create a button that opens up the Find/Replace dialog with settings that I change?
That will not work for my situation...

How about the following though...
With the above information presented could I put it in a startup form in Access (Under Form_Open) and THEN everytime someone where to press <CTRL>-<F> key the settings would be changed the way I want them?

 
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

thenelsonCommented:
You want to change the DEFAULT settings for the find replace dialog.

Goto tools > options > edit/find tab.  Change the setting there.
0
stephenlecomptejrAuthor Commented:
right - but I don't see anywhere under the edit/find tab to adjust my LookIn: to the column I am in initially - it always defaults to the entire form.  That's not what I want to do and I don't see a setting in there to change it.
0
p912sCommented:
Did you look at how to control the settings in the link I provided?

How to Set Defaults for the Find and Replace Dialog Box
http://support.microsoft.com/default.aspx?scid=kb;en-us;208923

This tells you how to create the macro to alter the find and replace dialog box. You can call the macro from a button on your form. The link also shows what other commands are available.
                        
You can combine these keystrokes to make multiple selections. For example, the following actions open the Find and Replace dialog box with Start Of Field selected in the Match box; select Up in the Search box; and set the focus to the Find What box:    Action       Action Argument
   ------------------------------
   SendKeys     Keystrokes: %hs%su%n
                Wait: No
   RunCommand   Command: Find
                        
0
p912sCommented:
stephenlecomptejr - I just read the link again that I provided and they have dropped the %l from the instructioins... it was in the previous version, below are links for both.

thenelson - are you aware of how to trigger the Look In feature now? It defaults to the table or every field in the form in Access 2003 and it ignores when you enter %l in the keystrokes.

ACC97: How to Set Defaults for the Find Dialog Box
http://support.microsoft.com/kb/120912/en-us

ACC2000: How to Set Defaults for the Find and Replace Dialog Box
http://support.microsoft.com/kb/208923/en-us
0
thenelsonCommented:
p912s,
>are you aware of how to trigger the Look In feature now?
yes, and I have been aware for the past several years.  But that is not what stephenlecomptejr is asking.


To change how Ctrl-F functions for the LookIn: feature, you would need to create a global, public function using the How to Set Defaults for the Find and Replace Dialog Box links then call the function from an AutoKeys Macro that redefines ctrl-F to run the function.  Not at all a good idea because your function will have to be able to handle everywhere ctrl-F might be pressed.  The  LookIn: feature is different for every form, table, query in the database.  You would be much better off to call the find/replace from a button on the form.  Or you can use about a dozen other search techniques.  Here are a few examples:
Five simple ways to have a list or combo box display a particular record on a form:

1) The combo box and list box wizard will do this for you automatically. In the first or second step (depending on you version of Access), choose the third option: "Find a record on my form based on the value selected in my combo box" This must be done on a BOUND form.  

Two easy ways to jump to a record but still be able to scroll through all the other records:

2) Have the record ID number as one of the fields in the list/combo box and in the form (they can both be hidden).  The bound column of the list/combo box is the record ID field.  Then the On After Update Event of the list/combo box would look like:

Private Sub List/ComboBoxName_AfterUpdate()       'Use the name of your list/combo box
txtRecordID.SetFocus       'Use the name of your text box
DoCmd.FindRecord List/ComboBoxName       'Use the name of your list/combo box
End Sub

3) Again, the bound column of the list/combo box is the record ID field.

Private Sub List/ComboBoxName_AfterUpdate()       'Use the name of your list/combo box
Me.RecordSet.FindFirst "txtRecordID = " & List/ComboBoxName       'Use the name of your list/combo box and text box
End Sub

Two easy ways to filter the records to show only the record selected:

4) Again, the bound column of the list/combo box is the record ID field.

Private Sub List/ComboBoxName_AfterUpdate()       'Use the name of your list/combo box
Me.Filter = "txtRecordID = " & List/ComboBoxName       'Use the name of your list/combo box and text box
Me.FilterOn = True
End Sub

5) Again, the bound column of the list/combo box is the record ID field.

In the query that is the record source for the form, create a criteria like this:
[Forms].[YourFormName].[ComboBoxName]       'Use the name of your list/combo box and form name

Additional information:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q209537
http://allenbrowne.com/ser-03.html
http://www.rogersaccesslibrary.com/download3.asp?SampleName=ComboChoosesRecord.mdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
p912sCommented:
thenelson - as always excellent information, thanks!
0
thenelsonCommented:
You're welcome.  Glad to help and thank you very much for the points with "A" grade!

Happy Computing!

Nelson
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.