?
Solved

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

Posted on 2006-03-29
10
Medium Priority
?
449 Views
Last Modified: 2008-02-07
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.
0
Comment
Question by:stephenlecomptejr
  • 4
  • 4
  • 2
10 Comments
 
LVL 18

Assisted Solution

by:p912s
p912s earned 400 total points
ID: 16321263
How to Set Defaults for the Find and Replace Dialog Box
http://support.microsoft.com/default.aspx?scid=kb;en-us;208923
0
 
LVL 39

Expert Comment

by:thenelson
ID: 16322712
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
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 16327705
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 39

Expert Comment

by:thenelson
ID: 16327991
You want to change the DEFAULT settings for the find replace dialog.

Goto tools > options > edit/find tab.  Change the setting there.
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 16328956
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
 
LVL 18

Expert Comment

by:p912s
ID: 16329032
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
 
LVL 18

Expert Comment

by:p912s
ID: 16329117
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
 
LVL 39

Accepted Solution

by:
thenelson earned 1600 total points
ID: 16329355
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
 
LVL 18

Expert Comment

by:p912s
ID: 16341242
thenelson - as always excellent information, thanks!
0
 
LVL 39

Expert Comment

by:thenelson
ID: 16344284
You're welcome.  Glad to help and thank you very much for the points with "A" grade!

Happy Computing!

Nelson
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question