Warning Message when running an Active Query

[jcbhydro]

Good afternoon,

I have a number of Active Queries in my membership database which operate to modify multiple records in the database, eg resetting subscription data for a new financial year.Other examples include multiple deletions of records from several tables under certain circumstances.

My question is; Is it possible to initiate a text warning message to alert operators of the consequence of running a particular Query and to require confirmation before proceeding.

Any suggestions would be gratefully received.

jcbhydro

 

[pbaldy]

One way would be a yes/no message box. Here's my template code:

Dim msg As String, button As Variant, title As String, response As Variant
msg = "Vehicle is Shopped - Do you want to dispatch anyway?"
button = vbYesNo + vbDefaultButton2
title = "Vehicle Shopped!"

response = MsgBox(msg, button, title)
If response = vbYes Then
  'what to do if yes
Else
  'what to do if not
End If

 

[plog]

Any suggestions would be gratefully received.

Fix your tables. Action queries (UPDATE/INSERT/DELETE) that are to be run frequently are usually hacks to overcome poor table design. You don't reset data for a new year--you simply add data and you either have a date field you can use to determine what year the data is for, or you specifically add a field to do so. Same for deletion of data--you simply mark them as no longer relevant (you have a Yes/No Inactive field).

I really think all those queries are masking a deeper issue. With that said, what you want to do is not possible. pbaldy gave you code, but you cannot attach that code to a query--you could put it in a macro or a form and then fire it when the user goes to run one of your queries via that macro/form. But you cannot put any VBA code on a query itself.

 

[jcbhydro]

Thank you both for your suggestions and comment.

If I can't attach VBA code to a Query, I shall need to consider a Macro alternative.

Regards,

jcbhydro

 

[pbaldy]

You can't attach a macro to a query either. Most of us never let users see tables or queries, just forms and reports. The user would click a button on a form to run your queries, so the code would be there.