How do I run 2 Queries in 1 procedure 1

[james33]

Hallo all,
I have a procedure (see below) that runs an Append qry (qryAppendToJobHistoryTable) from a cmd Button.
Is it possible to run another query within that procedure the name of THE QUERY is qryDeleteJobFromActive

Private Sub cmdAppendToHistory_Click()
On Error GoTo Err_cmdAppendToHistory_Click

    Dim stDocName As String
    
    stDocName = "qryAppendToJobHistoryTable"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    
Exit_cmdAppendToHistory_Click:
    Exit Sub

Err_cmdAppendToHistory_Click:
    MsgBox Err.Description
    Resume Exit_cmdAppendToHistory_Click
    
End Sub

At the same time is it possible that I can use VBmsgboxYes/No to replace the Warnings that come up with Action queries?
I don't want to stop the warnings coming up at other times, just in this situation.
Thank you in advance
Jim #-)

 

[mymou]

Try this

Private Sub cmdAppendToHistory_Click()
On Error GoTo Err_cmdAppendToHistory_Click

Dim stDocName As String
response = MsgBox("Warning: Do you really want to delete data", vbExclamation + vbOKCancel, "Warning&quot
If response = vbOK Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryAppendToJobHistoryTable", acNormal, acEdit
DoCmd.OpenQuery "qryDeleteJobFromActive", acNormal, acEdit
DoCmd.SetWarnings True
End If

Exit_cmdAppendToHistory_Click:
Exit Sub

Err_cmdAppendToHistory_Click:
MsgBox Err.Description
Resume Exit_cmdAppendToHistory_Click

End Sub


Stew

 

[james33]

Dear mymou,
Thanks very much for the Code as you can see I altered it a bit as when I first ran it, it came up with Variable not defined
However it seems to require 2 presses of the button before it runs the queries.
This I realised from checking the tables afterwards.
Any Ideas on that one?

Private Sub cmdAppendToHistory_Click()
On Error GoTo Err_cmdAppendToHistory_Click
    Dim Response As String
    Dim stDocName As String
    Response = MsgBox("Warning: Do you really want to Close This Job?", vbExclamation + vbOKCancel, "Warning")
    If Response = vbOK Then
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "qryAppendToJobHistoryTable", acNormal, acEdit
        DoCmd.OpenQuery "qryDeleteJobFromActive", acNormal, acEdit
        DoCmd.SetWarnings True
    End If
    
Exit_cmdAppendToHistory_Click:
    Exit Sub

Err_cmdAppendToHistory_Click:
MsgBox Err.Description
Resume Exit_cmdAppendToHistory_Click

End Sub
Thanks very much for your help so far.
Regards Jim ;-)

 

[KyleS]

Hi,

The only suggestion I have from looking at the code is that you change the messahe box to a "Yes/No" by replacing the "vbOkCancel" with "vbYesNo" and then change the IF statement to read:

IF Response = 6 Then

And remove the line:
Dim stdocname As String

Kyle

 

[mymou]

Hi James

You shouldn't have to run this twice to work.

Have you tried stepping through the code to see what happens? (Just go to a line of code press F9 and then run it as normal(ie by pressing button) - when the code stops - use F8 to step though the code).

I suspect that maybe your queries aren't working as expected.

Stew