Set Find/Replace Dialog Defaults With Code

[JoyInOK]

For some reason, the default value of "Search Fields as Formatted" in the Find and Replace dialog box in Access XP is Checked. Users must remember to uncheck it each time in order to get correct search results. In prior versions, defaults for this dialog box were set in Tools/Options/Edit/Find/Default Search Behavior. No more. A microsoft knowledgebase article suggests using a macro with SendKeys to "uncheck" (or actually to "check" it, since MS seems to think the default is unchecked) the option before opening the dialog box. One would then add a custom button on the toolbar or form and call the macro from the button.
Problem1: Macro only works from your custom button, so the keyboard shortcut CTRL+F brings up the standard dialog with Search Fields as Formatted checked.
Problem2: The Search Fields as Formatted checkbox is a toggle control. This means if you send a keystroke to "uncheck" it, it will work the first time, but the second call to the procedure will (with the same SendKeys command) "check" it. So 50% of the time you get the actual results you want.

I've searched but have not found a way to call the find dialog box with VBA and set the value of the options on this dialog through code. Any ideas?

 

[jrbarnett]

Joy,

Just a suggestion, abeit one which I fully expect to told is a gross hack and undesirable. I am fully aware of this:

Use a flag to indicate if your macro has been run before, and not execute it if the flag is set. Problem here is if the flag is within the form, even if it is a static variable, and the form it is in is closed, then opened again, even a static variable is lost.
You could of course use a global variable, but that again is frowned upon. This gets around that problem, and also the problem of informing the other code if the find/replace window is called from multiple places within your application,

John

 

[KZPools]

Joy, this is something you brought up a long while ago but am finding myself faced with this problem today. It's been a nuisance since our conversion of the underlying tables to SQL (it slows the searches downn considerably); when the tables were naitive to Access it didn't present much of an issue. We've been dealing with it as is a couple of months now and had some time today to look for a resolution. What did you ultimatley do to solve the problem? Thanks, -Joseph

 

[JoyInOK]

Bad news, KZPools. After much gnashing of teeth, I held a training for my users explaining that if they don't get the results they want, they should try again after unchecking the box. This works when it is obvious that the results are wrong (When they KNOW we have more that one Smith in the database, for example). I'm sure, however that we've given some incorrect information due to this problem.
One would think Microsoft would have realized this problem by now. I was hoping a service pack would address it, but no luck so far.

 

[KZPools]

Thanks Joy.

How is it we can even bring the issue to their attention? Have they ever acknowledge it as a concern to end users?

Take care,
-J.

 

[jrbarnett]

Further to my earlier posting, I have discovered that some of the options can be set programmatically via Application.SetOption (OptionName, Value) and the value retrieved using Application.GetOption (OptionName), so there is the possibility of retrieving the variable status, store it in a table (thus getting around the variable scope problem) and restoring it to the original value on exiting, because these values are set on a per machine basis, rather than per database/user.
I don't have Access on this PC to give you exact code snippets at the moment, but I'm sure you can look this up in the VBA help.

John