Popup a message when query finished

[TonyWilliams via AccessMonster.com]

I have a form that has a command button with code that updates 7 tables by a
series of 7 queries. Is there any way to popup a message box to indicate to
the user that all the queries have run successfully and they can move on?
Although it happens very quickly there is no visual evidence to the user that
the query is running or finished.
Thanks
Tony

 

[David C. Holley]

Yes, you can put a Msgbox at the end of the code that executes the query or
alternatively create a form that is displayed before the queries run and
then hidden afterward. If you go the form route, you can inform the user of
the progress by updating a caption on the form 'Query (x) of (x)
running...).

[Forms]![frmAdvisory]![Notification].caption = "Executing query 5 of 7..."
[Forms]![frmAdvisory]![Notification].Repaint

The repaint is neccessary otherwise the change to the caption won't be seen
by the user.

 

[TonyWilliams via AccessMonster.com]

Thanks David this sounds just what I want BUT how do I go about doing it? Do
I just create a form frmadvisory with one control called Notification but how
do I instigate the form and how does it show the caption etc.
Sorry but this is a rather new to me.
Thanks
Toy

Yes, you can put a Msgbox at the end of the code that executes the query or
alternatively create a form that is displayed before the queries run and
then hidden afterward. If you go the form route, you can inform the user of
the progress by updating a caption on the form 'Query (x) of (x)
running...).

[Forms]![frmAdvisory]![Notification].caption = "Executing query 5 of 7..."
[Forms]![frmAdvisory]![Notification].Repaint

The repaint is neccessary otherwise the change to the caption won't be seen
by the user.

I have a form that has a command button with code that updates 7 tables by
a

[quoted text clipped - 6 lines]

Thanks
Tony

 

[TonyWilliams via AccessMonster.com]

Could someone (David?) guide me through how I would amend my code to do this?
This is my code at the moment. You will see that it has a message box at the
start of the procedure but I want a message that tells the user what's
happening and when all the queries have run.
Many thanks
Tony
Private Sub cmdupdate_Click()
On Error GoTo Err_cmdupdate_Click
'Step1- Update tblcompany
Dim stDocName1 As String
stDocName1 = "qrycompanyupdate"

'Step2 - Update tblhvanalysis
Dim stDocName2 As String
stDocName2 = "qrycompanyupdatehva"

'Step3 - Update tblhvcomp
Dim stDocName3 As String
stDocName3 = "qrycompanyupdatehvc"

'Step4 - Update tblhvdealspt1
Dim stDocName4 As String
stDocName4 = "qrycompanyupdatehvpt1"

'Step5 - Update tblhvdealspt2
Dim stDocName5 As String
stDocName5 = "qrycompanyupdatehvpt2"

'Step6 - Update tblmainIrish
Dim stDocName6 As String
stDocName6 = "qrycompanyupdateIrish"

'Step7 - Update tblmaintabs
Dim stDocName7 As String
stDocName7 = "qrycompanyupdatemt"


Dim Msgstr1 As String
Msgstr1 = "You are about to update the company name, in all Tables, of
the company you have selected:" & vbCrLf & vbCrLf _
& "Are you sure you want to do all of this?"


If MsgBox(Msgstr1, vbYesNo, "Updating Company name") = vbNo Then
DoCmd.CancelEvent
Else
DoCmd.OpenQuery stDocName1, acViewNormal, acEdit
DoCmd.OpenQuery stDocName2, acViewNormal, acEdit
DoCmd.OpenQuery stDocName3, acViewNormal, acEdit
DoCmd.OpenQuery stDocName4, acViewNormal, acEdit
DoCmd.OpenQuery stDocName5, acViewNormal, acEdit
DoCmd.OpenQuery stDocName6, acViewNormal, acEdit
DoCmd.OpenQuery stDocName7, acViewNormal, acEdit

End If


Exit_cmdupdate_Click:
Exit Sub

Err_cmdupdate_Click:
MsgBox Err.Description
Resume Exit_cmdupdate_Click
End Sub

Yes, you can put a Msgbox at the end of the code that executes the query or
alternatively create a form that is displayed before the queries run and
then hidden afterward. If you go the form route, you can inform the user of
the progress by updating a caption on the form 'Query (x) of (x)
running...).

[Forms]![frmAdvisory]![Notification].caption = "Executing query 5 of 7..."
[Forms]![frmAdvisory]![Notification].Repaint

The repaint is neccessary otherwise the change to the caption won't be seen
by the user.

I have a form that has a command button with code that updates 7 tables by
a

[quoted text clipped - 6 lines]

Thanks
Tony

 

[Alex Dybenko]

Hi,
just add one line after last query:
DoCmd.OpenQuery stDocName7, acViewNormal, acEdit
Msgbox "You have run all 7 queries!"

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Could someone (David?) guide me through how I would amend my code to do
this?
This is my code at the moment. You will see that it has a message box at
the
start of the procedure but I want a message that tells the user what's
happening and when all the queries have run.
Many thanks
Tony
Private Sub cmdupdate_Click()
On Error GoTo Err_cmdupdate_Click
'Step1- Update tblcompany
Dim stDocName1 As String
stDocName1 = "qrycompanyupdate"

'Step2 - Update tblhvanalysis
Dim stDocName2 As String
stDocName2 = "qrycompanyupdatehva"

'Step3 - Update tblhvcomp
Dim stDocName3 As String
stDocName3 = "qrycompanyupdatehvc"

'Step4 - Update tblhvdealspt1
Dim stDocName4 As String
stDocName4 = "qrycompanyupdatehvpt1"

'Step5 - Update tblhvdealspt2
Dim stDocName5 As String
stDocName5 = "qrycompanyupdatehvpt2"

'Step6 - Update tblmainIrish
Dim stDocName6 As String
stDocName6 = "qrycompanyupdateIrish"

'Step7 - Update tblmaintabs
Dim stDocName7 As String
stDocName7 = "qrycompanyupdatemt"


Dim Msgstr1 As String
Msgstr1 = "You are about to update the company name, in all Tables, of
the company you have selected:" & vbCrLf & vbCrLf _
& "Are you sure you want to do all of this?"


If MsgBox(Msgstr1, vbYesNo, "Updating Company name") = vbNo Then
DoCmd.CancelEvent
Else
DoCmd.OpenQuery stDocName1, acViewNormal, acEdit
DoCmd.OpenQuery stDocName2, acViewNormal, acEdit
DoCmd.OpenQuery stDocName3, acViewNormal, acEdit
DoCmd.OpenQuery stDocName4, acViewNormal, acEdit
DoCmd.OpenQuery stDocName5, acViewNormal, acEdit
DoCmd.OpenQuery stDocName6, acViewNormal, acEdit
DoCmd.OpenQuery stDocName7, acViewNormal, acEdit

End If


Exit_cmdupdate_Click:
Exit Sub

Err_cmdupdate_Click:
MsgBox Err.Description
Resume Exit_cmdupdate_Click
End Sub

Yes, you can put a Msgbox at the end of the code that executes the query
or
alternatively create a form that is displayed before the queries run and
then hidden afterward. If you go the form route, you can inform the user
of
the progress by updating a caption on the form 'Query (x) of (x)
running...).

[Forms]![frmAdvisory]![Notification].caption = "Executing query 5 of 7..."
[Forms]![frmAdvisory]![Notification].Repaint

The repaint is neccessary otherwise the change to the caption won't be
seen
by the user.

I have a form that has a command button with code that updates 7 tables
by
a

[quoted text clipped - 6 lines]

Thanks
Tony

 

[TonyWilliams via AccessMonster.com]

Thanks Alex. Could you look at David's earlier post? How do I use his idea
and yours both together?
Thanks for your help
Tony

Hi,
just add one line after last query:
DoCmd.OpenQuery stDocName7, acViewNormal, acEdit
Msgbox "You have run all 7 queries!"

Could someone (David?) guide me through how I would amend my code to do
this?

[quoted text clipped - 81 lines]

 

[PieterLinden via AccessMonster.com]

You could do something like this:

Private Sub cmdupdate_Click()
On Error GoTo Err_cmdupdate_Click
'Step1- Update tblcompany
Dim stDocName1 As String
stDocName1 = "qrycompanyupdate"

'Step2 - Update tblhvanalysis
Dim stDocName2 As String
stDocName2 = "qrycompanyupdatehva"

'Step3 - Update tblhvcomp
Dim stDocName3 As String
stDocName3 = "qrycompanyupdatehvc"

'Step4 - Update tblhvdealspt1
Dim stDocName4 As String
stDocName4 = "qrycompanyupdatehvpt1"

'Step5 - Update tblhvdealspt2
Dim stDocName5 As String
stDocName5 = "qrycompanyupdatehvpt2"

'Step6 - Update tblmainIrish
Dim stDocName6 As String
stDocName6 = "qrycompanyupdateIrish"

'Step7 - Update tblmaintabs
Dim stDocName7 As String
stDocName7 = "qrycompanyupdatemt"


Dim Msgstr1 As String
Msgstr1 = "You are about to update the company name, in all Tables, of
the company you have selected:" & vbCrLf & vbCrLf _
& "Are you sure you want to do all of this?"


If MsgBox(Msgstr1, vbYesNo, "Updating Company name") = vbNo Then
DoCmd.CancelEvent
Else
me.txtEventBeingProcessed = "Update tblcompany"
Me.Repaint
Application.DoEvents
DBEngine(0)(0).Execute "qrycompanyupdate"

me.txtEventBeingProcessed = "Update tblhvanalysis"
Me.Repaint
Application.DoEvents
DBEngine(0)(0).Execute "qrycompanyupdatehva"

' lather, rinse, repeat for each query you want to run.
' finally...
Me.txtEventBeingProcessed = "" 'no more messages (or you could
close the form)
End If


Exit_cmdupdate_Click:
Exit Sub

Err_cmdupdate_Click:
MsgBox Err.Description
Resume Exit_cmdupdate_Click
End Sub

 

[TonyWilliams via AccessMonster.com]

Thanks Pieter I'll try that and come back if I have any problems
Thanks again
Tony

 

[TonyWilliams via AccessMonster.com]

Hi Pieter I've eventually got round to coding the message and when I compile
the code I get an error message that says "Method or data member not found"
and this bit of the vode is highlighted:
Me.txtEventBeingProcessed

Any ideas?
Thanks
Tony