Message boxes when running Append query

[NeilT123]

I have a button on a form which runs an append query. The button code is:

Private Sub cbUpdateRecords_Click()

On Error GoTo cbUpdateRecords_Click_Err
DoCmd.OpenQuery "qryFertApplnGroup", acViewNormal, acEdit
cbUpdateRecords_Click_Exit:
Exit Sub

cbUpdateRecords_Click_Err:
MsgBox Error$
Resume cbUpdateRecords_Click_Exit

End Sub

When I press the button I get two message boxes:

"You are about to run an append query..."

and

"You are about to append ?? rows."

If I run the query direct from the query I only get the second box. I want the second message box so is there any other way to run the query from the form and only get the second message box?

Thank you

 

[Gasman]

Turn warnings off?, then back on after the process?

 

[NeilT123]

If I turn warnings off I lose both message boxes and I think the "you are about to append..." message box is useful.

I don't know how to / where to turn off just the first "you are about to run..." message box.

 

[arnelgp]

change it to:

Private Sub cbUpdateRecords_Click()
Dim sql As String
On Error GoTo cbUpdateRecords_Click_Err
sql = Currentdb.QueryDefs("qryFertApplnGroup").SQL
DoCmd.RunSQL sql
cbUpdateRecords_Click_Exit:
Exit Sub

cbUpdateRecords_Click_Err:
MsgBox Error$
Resume cbUpdateRecords_Click_Exit

End Sub

 

[NeilT123]

Correction, my statement above "If I run the query direct from the query I only get the second box" was incorrect. The query has to already be in design mode for you to only get one message box.

 

[Gasman]

If I turn warnings off I lose both message boxes and I think the "you are about to append..." message box is useful.

I don't know how to / where to turn off just the first "you are about to run..." message box.

You could use you own message box, though you would need to calculate the updated record count yourself?

 

[The_Doc_Man]

My question is "What do you need to do with the message box?" Is it informational only? Do you actually make a decision with it to sometimes choose to cancel the operation?

The reason I ask this is, if you only wanted to know how many records were involved, would it be OK to just get a count of affected records AFTER it was done? BECAUSE if you use .Execute you will not get any messages BUT you can ask how many records were affected by the operation. With the DoCmd methods, you cannot ask that question due to a difference in the mechanism.

Database.Execute method (DAO)

Office developer client VBA reference documentation

learn.microsoft.com

If that is OK, then you can ask for more detailed instructions on doing this correctly in this same thread.

 

[NeilT123]

Ideally I wanted the option not to run the query.
All I want is to stop the first "you are about to run an append query" message box and keep the second message box. I think I am going to do as Gasman suggests and create my own message box with a record count but i will attempt that when I have more time.

 

[Gasman]

TBH, I would not let the user decide if they want to run an update/append query.
The process would be they select something, then it is up to me to continue the process with any necessary data checks.

If they marked something to be updated in error, then there would be a method to roll that back.
I used to even do that for myself with payroll in the last job I was in. Just as a safety factor for myself.

 

[GPGeorge]

Ideally I wanted the option not to run the query.
All I want is to stop the first "you are about to run an append query" message box and keep the second message box. I think I am going to do as Gasman suggests and create my own message box with a record count but i will attempt that when I have more time.

Given this validation requirement, I agree with calculating the number of records to be updated first (a SELECT version of the update query would provide that), displaying that number in a message box with "OK" and "Cancel" buttons, and either continuing with the update or bailing out.

That also allows you to use the CurrentDB.Execute method for the update, if you allow it to happen.

 

[NeilT123]

Given this validation requirement, I agree with calculating the number of records to be updated first (a SELECT version of the update query would provide that), displaying that number in a message box with "OK" and "Cancel" buttons, and either continuing with the update or bailing out.

That also allows you to use the CurrentDB.Execute method for the update, if you allow it to happen.

Thank you. I will give that a try over the weekend.

 

[gemma-the-husky]

You have two nuanced options. The problem with running it as a query (open query) is it will run and just report the number of failing rows, which may be dangerous, as it's hard to see what it actually did after the event.

If you execute it, you can trap errors so if it rejects some rows, it doesn't run at all.

It depends on the experience you want to give to the user. Not often, but sometimes I run the open query with all the messages, as they can be useful. Generally I execute and abort the query if there are failures.

 

[Isaac]

as George said, switch to currentdb.execute instead, and code your own confirmation dialogue.