Dialog boxes when running update query from macro

[helent24]

Hi All,

I'm writing a macro part of which includes running an update query. This will automate a number of other steps, and will run from a button on a form.

When the update query runs, it brings up a couple of dialogues boxes, the first saying:

You are about to run an update query that will modify data in your table.

Are you sure you want to run this type of action query...

Yes No Help

After hitting yes, the next box comes up saying:

You are about to update 3 row(s).

Once you click yes, you can't use the Undo command...

Yes No

Is there any way to prevent these dialogue boxes from appearing? These will only confuse the people who will be using the db.

Thanks

 

[JANR]

In your macro add a line before the update action and select the Action SetWarnings and set it's property to false.

The (and this is important) after the update is finish add another Setwarning and set it's property to TRUE!! or else other access warings will NOT be displayed.

JR

 

[helent24]

Thanks Jan, that's done the trick.

 

[MyTech]

Hi

I have the same problem - running an Update Query brings up 2 warnings.

Warning#1:

You are about to run an update query that will modify data in your table.
Are you sure you want to run this type of action query...
Yes No Help

Warning#2:

You are about to update 3 row(s).
Once you click yes, you can't use the Undo command...
Yes No

The answer to turn off all warnings was a good help for Helent.

In my case, however, I don't want Warning#1 to pop up to the user,
But I do want Warning#2 to warn him of how many records he is about to update.


Any help please?

 

[boblarson]

Are you using VBA or Macros? You can't disable one or the other. It is either both or none. However, you can use some code to determine how many records are going to change by doing up a select query just like your update query and with the same criteria. You can then get the count of records which would be affected and provide your own message box.

 

[MyTech]

I used macro.

Can you please help me in either, Macro or VBA?

 

[MyTech]

I guess I have to Create my own "Dialog Box Form" and let it run before the Update Query, correct?

 

[MyTech]

Got it, gotta insert a MessageBox action before the OpenQuery action.


Now, how do I get the macro to retrieve the number of records being updated?

In other words; how do I use code inside the message macro, to retrieve the sum of records set to "Yes" in a CheckBox field

 

[boblarson]

You can use a DCount -

Dim lngCount As Long
 
lngCount = DCount("*", "YourTableOrQueryName", "[FieldNameHere] = -1")
 
MsgBox "You are about to change " & lngCount & " records.", vbInformation, "Record Count"

 

[MyTech]

Bob, thank you so much for your reply.

Meanwhile everything ok.