Triggering an event after an aborted/stopped query execution (1 Viewer)

jumnhy

Member
Local time
Today, 11:26
Joined
Feb 25, 2021
Messages
68
Hello all,

I have a command button that's used to trigger a macro that opens an "insert" query. However, it's possible for the user to click the button, then hit the cancel option on the default warning that appears when executing this type of query.

I like the warning behavior, so I don't just want to force execution of the query and suppress warning dialogs--I think the end users in this particular case will actually appreciate the "Warning: this will append X rows of data" both for the information and the positive confirmation that something has happened.

However, if they hit cancel rather than OK, is there a way to trigger an event from the cancellation? Thanks in advance for any assistance.
 

Minty

AWF VIP
Local time
Today, 15:26
Joined
Jul 26, 2013
Messages
10,355
The best way would be to roll your own "Warning Message" box that simply either confirmed the action and then ran the update without the additional warning, or if cancelled then carried out the other things you want to happen.

Another route would be after the cancel in the inbuilt warning, you did some check to see if the record had been added, if not then do your other stuff. Maybe a DCount of the underlying records or similar.

Edit: in a Macro I'm not sure how possible this would be - in VBA pretty simple.
 

jumnhy

Member
Local time
Today, 11:26
Joined
Feb 25, 2021
Messages
68
Thanks for the ideas--mostly I was just curious if there was an event I was unaware of that I could get to fire without having to go that route. Sounds like there isn't?

I like the idea of a secondary check for query execution more than the RYO warning approach (seems like slightly less work?) so I'll see how hard that is to implement.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 15:26
Joined
Sep 12, 2006
Messages
15,614
I imagine cancelling an action query generates an error code.
I don't use macros so I am not sure how you would intercept the error, although it's probably easy to do in code.

It may be a form error, rather than a run time error.
 

jumnhy

Member
Local time
Today, 11:26
Joined
Feb 25, 2021
Messages
68
If it can be done in code, I'm all ears--though there's no popup with an error code after cancellation visible to the end user. Any idea where I could find out?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:26
Joined
Feb 28, 2001
Messages
27,003
If you use code to generate the message box in the first place, the only trick is developing the count of potentially affected records. The following is "air" code because I don't know your selection criteria.

Code:
Public Sub do-my-thing(some-arguments-if-needed)
On Error GoTo some-error-trap
...
X = DCount( "[some field name]", "some table or query name", "some criteria" )
IF X > 0 Then
    Y = MSGBOX( "You are about to change " & CStr( X) & " records.  Do you want to continue", vbYesNo, "Potential change" )
    if Y = acYes then
        currentdb.Execute "some INSERT INTO some table or query name SQL string based on the above criteria", acFailOnError
        Z = currentdb.recordsaffected
        if Z = 0 then
            do something because the query didn't make any insertions even though you expected them (from X above)
        end if
    else
        do something because they said "NO" to the warning
    end if
else
    do something because no records would have been selected (from X above)
end if
goto end-of-routine

some-error-trap:
    do something because the query failed
    if needed, do some cleanup here
    resume end-of-routine

end-of-routine:
    do any other required cleanup here
end sub

This skeleton gives you several options to do things where you got odd-ball results. Of course you would have to fill in the blanks, but this would give you roughly what you said you wanted AND the opportunity to handle multiple different types of failure and user option.
 

jumnhy

Member
Local time
Today, 11:26
Joined
Feb 25, 2021
Messages
68
Thanks, @The_Doc_Man --this is a good framework and like you say, leaves a lot of opportunity for customization. I'm very new to VBA so very much still learning, but this gives me enough to work off of. Much appreciated.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 15:26
Joined
Sep 12, 2006
Messages
15,614
If it can be done in code, I'm all ears--though there's no popup with an error code after cancellation visible to the end user. Any idea where I could find out?
I thought I had seen this before, but at the moment, I can't seem to produce a trappable error by cancelling a query. A shame, as it would be useful.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:26
Joined
May 21, 2018
Messages
8,463
I would think what @Minty suggests is by far the easiest. If you use Docmd.RunSQl it generates the warning message. If you use currentdb.execute then it generates no message. So you have a procedure that runs the code. It gives you an ok cancel message box. If OK then execute using currentdb.execute, if Cancel provide your alert message.

If you want to know how many records will be appended in your message that could be done too, but a little extra code.
 

jumnhy

Member
Local time
Today, 11:26
Joined
Feb 25, 2021
Messages
68
@gemma-the-husky , thanks for looking man, I've been checking elsewhere too but can't find anything... That said, I know we have people with a WEALTH of knowledge here that I trust more than my googling abilities. If you ever come up with anything, do let us know!

@MajP Thanks for the heads up re:DoCmd vs currentdb.execute, these are the basics of VBA that I'm very much still learning. To clarify from what you're saying: Use currentdb.execute (which won't generate its own Access action query warning) but like in @The_Doc_Man 's suggestion above, generate my own warning instead?

Much appreciated, everyone.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:26
Joined
May 21, 2018
Messages
8,463
@MajP Thanks for the heads up re:DoCmd vs currentdb.execute, these are the basics of VBA that I'm very much still learning. To clarify from what you're saying: Use currentdb.execute (which won't generate its own Access action query warning) but like in @The_Doc_Man 's suggestion above, generate my own warning instead?
Yes. I should have said @Minty suggested and @The_Doc_Man demonstrated. The @The_Doc_Man showed all of it as far as I can tell. He uses the currentdb.execute, shows the display message, and even shows the amount of records to add, counts the record added to see if it changed.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 15:26
Joined
Sep 12, 2006
Messages
15,614
its a good point.

docmd.openquery, and currentdb.execute do slightly different things when the query fails on some items, If you set warnings false, the openquery runs and doesn't warn you about errors, which is always a bit concerning.
 

jumnhy

Member
Local time
Today, 11:26
Joined
Feb 25, 2021
Messages
68
It's funny you mention that. I just spent a half hour learning some of the ins and outs of those differences. For potential future readers:

If you execute a query with the Currentdb.Execute method, the query is processed literally, and any parameters it contains won't be evaluated.

This throws up an error of "Too few parameters--Expected X" which doesn't tell you much. That's just because the parameters you've used aren't being processed.

The 3 solutions I found for that:

1. Throw in Eval([yourparameterhere]) around any parameters/expressions in your query beforehand

2. Use the QueryDef for your query and loop through the .Parameters, setting the value of the parameter = eval(parameter) (if you already created it previously, this is probably the easiest)

3. Directly reference each parameter in your code--eg if you're referencing a textbox, you would set qdf![Forms!YourForm!YourTxtBoxName] = [Forms]![YourForm]![YourTxtBoxName]. Do so for every criteria.

I went with option 2, as I already had my query ready to go.

With a little fine tuning, I'll be ready to go with this implementation, but I'm through the weeds. Going to go ahead and mark this as solved--thank you for the help, everyone.

I'm sure this stuff is all old hat to everyone who's been here helping me today, but it's new to me, and if someone else wanders this way, I hope it'll make your learning easier, too.

Selected resources on QueryDefs:
https://www.devhut.net/2011/11/07/ms-access-vba-run-parameter-query-in-vba/
https://docs.microsoft.com/en-us/of...atabase-reference/querydef-execute-method-dao

And a great sample database with a readMe worth reading from Roger's Access Library:
http://www.rogersaccesslibrary.com/forum/topic234.html
 

Minty

AWF VIP
Local time
Today, 15:26
Joined
Jul 26, 2013
Messages
10,355
QueryDefs are definitely the way to go for a nice clean solution.
Not many new users (apologies if you're not!) land on that solution straight away.

Have yourself a biscuit :cool:
 

jumnhy

Member
Local time
Today, 11:26
Joined
Feb 25, 2021
Messages
68
ha, thanks, and no apologies needed, I am indeed very new.

The funny thing is that as I get better at database development, I end up spending more time, not less, on stuff that isn't 100% necessary for the brief because I'm learning what's possible, so some of the stuff that I would just sandbox the user away from previously is now possible, it just ends up taking a long time.
 

Isaac

Lifelong Learner
Local time
Today, 08:26
Joined
Mar 14, 2017
Messages
8,738
Reading through this thread I just wanted to add one note.

You cannot just code:

Code:
Currentdb.Execute "something"
Currentdb.RecordsAffected 'this will return 0

You have to do it all within the same With block, or set a database variable.

Code:
With Currentdb
    .execute "something"
    msgbox "now the " & .recordsaffected & " is accurate"
End With
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:26
Joined
Feb 28, 2001
Messages
27,003
@Isaac - good point. When I tossed that together, I forgot that CurrentDB always re-evaluates whatever IS the current DB each time it is used. I so commonly use a separate variable for the DB that it slipped my mind for a moment as to WHY I had been doing that.
 

Users who are viewing this thread

Top Bottom