Can I set Tools/Options/Edit-Find/Confirm in VBA?

K

Keith Nichols

Registered User.
Local time
Today, 19:56
Joined
Jan 27, 2006
Messages
431
Hi Guys & Gals,

I have a report that runs a number of queries via a macro in the on open property. These queries populate a table used in the report. The on close property runs a query to clear the table contents.

When a user ran this, he had a flurry of warning notices which I tracked down to the Confirm settings in the tools/options/edit find tab.

Is is possible to set these using vba?

I guess that this could be achieved using send keys, but this would probably be a bit clumsy - to say the least.

Can it be done so that the settings are changed only for the current session so that users have the original settings?

Any thoughts welcome.

Regards,

Keith.
 
Does anybody know if this is possible?

Does anybody know if this is possible?

Regards,

Keith.
 
Keith,

Check out: DoCmd.SetWarnings True/False

Wayne
 
Do not forget to turn the warnings back on after the queries run. Leaving warnings off in a database where objects might be changed is extremely dangerous since if you simply close an object that you have changed without explicitly saving it, Access will not warn you. It will simply discard your changes and close the object.

What I do as a visual hint to myself is whenever I turn warnings off, I turn the hourglass on and vice versa. That way if somehow, warnings don't get turned back on, I have a visual clue that there is a problem.
 
Excellent! Much simpler than I imagined

WayneRyan said:
Keith,

Check out: DoCmd.SetWarnings True/False

Wayne
Click to expand...

Wayne, I'm sure this will be a breeze. I was thinking in completely the wrong direction. Thanks.

Regards,


Keith.
 
Keith,

Good to hear. Pay REAL close attention to Pat's comments. Having the
warnings off isn't to be taken lightly.

Wayne
 
What happens to the hourglass on error?

Pat Hartman said:
What I do as a visual hint to myself is whenever I turn warnings off, I turn the hourglass on and vice versa. That way if somehow, warnings don't get turned back on, I have a visual clue that there is a problem.
Click to expand...

Pat,
Good tip. I have used the hourglass for some other function so I know how to do that.

One question though: if a routine jumped to the error trap before the cursor was reset to normal, would Access be locked up?

Should I include reset cursor & warnings on commands in the exit procedure of the error trap?


Regards,
 
Should I include reset cursor & warnings on commands in the exit procedure of the error trap?
Click to expand...
Yes. Expect the unexpected.
 
Many thanks - here it is.

Hi Gents,

I'm on a little leave atm so it has taken me a while toget round to this but it was very easy when I did so. This works very well although the procedure is too quick to see the hourglass. :)

Code: 
Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_Handler
    
DoCmd.Maximize

[COLOR="SeaGreen"]'Turn warnings on & Hourglass off[/COLOR]
DoCmd.SetWarnings [COLOR="Blue"]False[/COLOR]
DoCmd.Hourglass [COLOR="blue"]True[/COLOR]

[B][I][COLOR="DarkRed"]Code in routine that would generate warnings goes here.[/COLOR][/I][/B]

[COLOR="SeaGreen"]'Turn warnings on & Hourglass off[/COLOR]
DoCmd.SetWarnings [COLOR="blue"]True[/COLOR]
DoCmd.Hourglass [COLOR="blue"]False[/COLOR]

Exit_Here:
[COLOR="SeaGreen"]'Turn warnings on & Hourglass off[/COLOR]
DoCmd.SetWarnings [COLOR="blue"]True[/COLOR]
DoCmd.Hourglass [COLOR="blue"]False[/COLOR]
    Exit Sub

Err_Handler:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_Here
End Sub

One final question: do I need the repeat of the SetWarnings True & Hourglass False or could I just have it once in the Exit_Here: section?

Regards,

Keith.
 
Having it once in Exit_Here is sufficient provided this is the ONLY exit point from the routine. Hence the hourglass which will warn you if something slipped through the cracks.
 
You must log in or register to reply here.

Similar threads

D
Can't Get Monaco Option to Show Up
Replies
7
Views
433
isladogs
isladogs
Isaac
A free tool to set up a Windows computer
Replies
1
Views
392
AccessBlaster
A
K
A dream: What I do miss about Access.
2
Replies
20
Views
982
isladogs
isladogs
A
Solved replace one line of code with two using the find and replace tool in ms-access 2010/2021
Replies
4
Views
584
Gasman
Gasman
P
Need VBA Code Guidance - BeforeUpdate Event
Replies
6
Views
577
The_Doc_Man
The_Doc_Man

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom