SOLVED

Runtime Access Options

Copper Contributor

I don't wont to receive notifications from Sql queries, bat I can't  use the appropriate options to exclude them

6 Replies

@John_Bloggsgasgas 

I assume you mean messages like 'You are about to update XXX records. Do you want to continue?'

If so, do one of the following

 

DoCmd.SetWarnings False
DoCmd.RunSQL "Your SQL string here"
DoCmd.SetWarnings True

 

That will suppress all messages

However, a better option is 

 

CurrentDb.Execute "Your SQL String here", dbFailOnError

 

 

The second method suppresses the standard warnings EXCEPT any error messages

@isladogs 

Thank you for your reply.

You have understood the problem and solve the problem on the programmer's side. Unfortunately in this way all users who will use my application will no longer have notifications.

My wish would be that every user could choose whether to have them or not. I achieve this when the user has the full Access product by acting on the Options.

How can I access the Options with the runtime?

Thank you again.

best response confirmed by John_Bloggsgasgas (Copper Contributor)
Solution

@John_Bloggsgasgas 

In my experience most users find the warnings irritating

Personally, I wouldn't give users the option but its your database and can be done if you think its really worth the effort,.

As users have no means if accessing Access options in runtime, you could consider this as a workround

 

1. Have a table tblUsers to include these fields:

UserName - text (based on Windows network login or an application login form

ShowWarnings - yes/no (boolean) - set default value as false 

2. Have a settings form linked to that table where users tick a checkbox if they want to see warnings throughout your application

3. Create a public function GetUserWarningsState to recall that value. Something like this would work (or you can use tempvars)

Public Function GetUserWarningState() As Boolean
GetUserWarningState = DLookup("ShowWarnings", "tblUsers", "UserName = '" & Environ("USerName") & "'")
End Function

4. Finally each time you run an action SQL statement, modify the code as follows:

'show warnings only if user has specified their use
If GetUserWarningsState = False Then DoCmd.SetWarnings False
DoCmd.RunSQL "Your SQL string here"
DoCmd.SetWarnings True 'always set/reset warnings true afterwards

 

Hi,


You can set the query notification option at runtime with


Application.SetOption "Confirm Action Queries", False

 

or of course True at the end if you want to activate it according to the user's or better user group's choice or setting.

 

Servus
Karl
*********
http://AccessDevCon.com
https://www.donkarl.com

@Karl Donaubauer

Thanks. That's a much simpler solution which I should have thought of.

The only comment I'd make is that if more than one user shares the same PC, it may be sensible to check/update the status when the app loads

Thank you very much!
1 best response

Accepted Solutions
best response confirmed by John_Bloggsgasgas (Copper Contributor)
Solution

@John_Bloggsgasgas 

In my experience most users find the warnings irritating

Personally, I wouldn't give users the option but its your database and can be done if you think its really worth the effort,.

As users have no means if accessing Access options in runtime, you could consider this as a workround

 

1. Have a table tblUsers to include these fields:

UserName - text (based on Windows network login or an application login form

ShowWarnings - yes/no (boolean) - set default value as false 

2. Have a settings form linked to that table where users tick a checkbox if they want to see warnings throughout your application

3. Create a public function GetUserWarningsState to recall that value. Something like this would work (or you can use tempvars)

Public Function GetUserWarningState() As Boolean
GetUserWarningState = DLookup("ShowWarnings", "tblUsers", "UserName = '" & Environ("USerName") & "'")
End Function

4. Finally each time you run an action SQL statement, modify the code as follows:

'show warnings only if user has specified their use
If GetUserWarningsState = False Then DoCmd.SetWarnings False
DoCmd.RunSQL "Your SQL string here"
DoCmd.SetWarnings True 'always set/reset warnings true afterwards

 

View solution in original post