How do I know a query is finished?

[Tony Williams]

I have a for that has a command button which runs an update query. When the
query is finished I want a message box to say "Update complete" and on
clicking the OK button I want the for to close. I have added this to the end
of my code but am not sure if this will appear before the update has run.
Can anyone guide me on this please.

If MsgBox("Update completed", vbOKOnly, "Update complete") = vbOK Then
DoCmd.Close acForm, Me.Name
End If

Thanks
Tony

 

[Jeff Boyce]

Tony

Define "finished".

Are you looking for a guarantee that it ran? What's it doing? (knowing
that may give a clue for a test to confirm that it ran successfully)

If your command button does the following (psuedo-code):
Run the query
Say "The query ran"
Close this form
I guess I don't understand why you need to even put up the message, since
the user can't do anything.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tony Williams]

Hi Jeff. By finished I mean "ran". The reason I want the box is because I
want to switch off the Access warning messages and at the moment the query
runs and then the user sees the original form and doesn't know what's
happening, there's nothing that says the query has run. I wanted to pop up a
friendlier message that says "the query's run, you can now close the form"
or something along those lines. If I could add "and you have updated XXX
number of records" that would be even better.
Here is my complete code
Private Sub cmdupdateIrish_Click()
On Error GoTo Err_cmdupdateIrish_Click

Dim stDocName As String
Dim Msgstr As String
Dim Msgstr2 As String
stDocName = "qryYTDUpdateIrish"
Msgstr = "You are about to update the records for the Euro zone
companies " _
& "by calculating the quarterly data from the YTD data" & vbCrLf &
vbCrLf _
& "Are you sure you want to do that?"
Msgstr2 = "To update the data you must enter the current quarter!"
Msgstr3 = "To update the data you must enter the previous quarter!"

Me.txtqtr2.SetFocus
If Nz(Me.txtqtr2.Text) <> "" Then
Me.txtqtr3.SetFocus
If Nz(Me.txtqtr3.Text) <> "" Then
If MsgBox(Msgstr, vbYesNo, "Updating Euro zone records") = vbNo Then
DoCmd.Close
Else
DoCmd.OpenQuery stDocName, acNormal, acEdit
End If
Else
MsgBox Msgstr3, vbOKOnly, "Missing Previous Quarter"
Me.txtqtr3.SetFocus
End If
Else
MsgBox Msgstr2, vbOKOnly, "Missing Current Quarter"
Me.txtqtr2.SetFocus
End If
'If MsgBox("Update completed", vbOKOnly, "Update complete") = vbOK Then
'DoCmd.Close acForm, Me.Name
'End If

Exit_cmdupdateIrish_Click:
Exit Sub

Err_cmdupdateIrish_Click:
MsgBox Err.Description
Resume Exit_cmdupdateIrish_Click
End Sub

 

[Jeff Boyce]

Tony

Based on what I'm seeing, it looks like the messagebox happens after the
query. What happens when you run it?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Marshall Barton]

Check Help for the Execute method. I believe that it will
take care of all your issues (and maybe a few you haven't
thought of yet).

 

[Tony Williams]

Hi Jeff sorry I didn't get back to you yesterday but I'm in the UK and I
packed in at 6.45pm
When I run the query it updates the records and the message box pops up.
What I wasn't sure of was whether the message box was waiting for query to
end. I put it at the end of the code on the assumption that it did.
Sorry to be so vague but I'm new to VBA and not yet grasped all of what
happens.
Thanks
Tony

 

[Tony Williams]

Thanks Marsh I'll have a look, although I have a problem with VBA Help in
that in the index box the cursor wont select anything and I can't type
anything in the search box. The cursor changes to a |---| and wont change to
an arrow head. Anyway I'll search through the contents.
Thanks
Tony

Check Help for the Execute method. I believe that it will
take care of all your issues (and maybe a few you haven't
thought of yet).
--
Marsh
MVP [MS Access]

Hi Jeff. By finished I mean "ran". The reason I want the box is because I
want to switch off the Access warning messages and at the moment the query
runs and then the user sees the original form and doesn't know what's
happening, there's nothing that says the query has run. I wanted to pop up
a
friendlier message that says "the query's run, you can now close the form"
or something along those lines. If I could add "and you have updated XXX
number of records" that would be even better.
Here is my complete code
Private Sub cmdupdateIrish_Click()
On Error GoTo Err_cmdupdateIrish_Click

Dim stDocName As String
Dim Msgstr As String
Dim Msgstr2 As String
stDocName = "qryYTDUpdateIrish"
Msgstr = "You are about to update the records for the Euro zone
companies " _
& "by calculating the quarterly data from the YTD data" & vbCrLf &
vbCrLf _
& "Are you sure you want to do that?"
Msgstr2 = "To update the data you must enter the current quarter!"
Msgstr3 = "To update the data you must enter the previous quarter!"

Me.txtqtr2.SetFocus
If Nz(Me.txtqtr2.Text) <> "" Then
Me.txtqtr3.SetFocus
If Nz(Me.txtqtr3.Text) <> "" Then
If MsgBox(Msgstr, vbYesNo, "Updating Euro zone records") = vbNo Then
DoCmd.Close
Else
DoCmd.OpenQuery stDocName, acNormal, acEdit
End If
Else
MsgBox Msgstr3, vbOKOnly, "Missing Previous Quarter"
Me.txtqtr3.SetFocus
End If
Else
MsgBox Msgstr2, vbOKOnly, "Missing Current Quarter"
Me.txtqtr2.SetFocus
End If
'If MsgBox("Update completed", vbOKOnly, "Update complete") = vbOK
Then
'DoCmd.Close acForm, Me.Name
'End If

Exit_cmdupdateIrish_Click:
Exit Sub

Err_cmdupdateIrish_Click:
MsgBox Err.Description
Resume Exit_cmdupdateIrish_Click
End Sub

 

[Tony Williams]

Thanks Jamie that looks rather awesome as a newbie I'll need to take some
time to absorb that.
Cheers
Tony

 

[Stefan Hoffmann]

hi Tony,

I have a for that has a command button which runs an update query. When the
query is finished I want a message box to say "Update complete" and on
clicking the OK button I want the for to close. I have added this to the end
of my code but am not sure if this will appear before the update has run.
Can anyone guide me on this please.

You are using DoCmd.OpenQuery, this method runs asynch. Use
CurrentDb.Execute "Queryname" instead:

CurrentDb.Execute "Queryname"
MsgBox "Finished."


mfG
--> stefan <--

 

[Tony Williams]

Thanks Stefan
Tony

hi Tony,


You are using DoCmd.OpenQuery, this method runs asynch. Use
CurrentDb.Execute "Queryname" instead:

CurrentDb.Execute "Queryname"
MsgBox "Finished."


mfG
--> stefan <--

 

[Tony Williams]

Stefan could you explain what you mean by asynch and when I tried to use the
new code I got "Too few parameters Missing 1" error message
What's that?
Cheers
Tony

 

[Stefan Hoffmann]

hi Tony,

Stefan could you explain what you mean by asynch

Asynch means asynchronous.

The DoCmd.RunQuery may return before the query is completly finished.

and when I tried to use the
new code I got "Too few parameters Missing 1" error message

Has your query a parameter?


mfG
--> stefan <--