asked on

Running multiple Queries on Command Click

Hi Folks

I wanted to run multiple stored queries named U1..U50 on a Command Button Click. I have this function " RunAQuery" but returns a error "Function or interface marked as restricted or the function uses an automation type not supported in VB "

I also need a for loop so that I dont have to write the function 50 times. Any help is appreciated.

Thanks

Private Sub Run_All_Queries_Click()
On Error GoTo Err_Run_All_Queries_Click

' Need for loop here
RunAQuery ("U1")
RunAQuery ("U2")
RunAQuery ("U3")
RunAQuery ("U4")
RunAQuery ("U5")

Exit_Run_All_Queries_Click:
Exit Sub

Err_Run_All_Queries_Click:
MsgBox Err.Description
Resume Exit_Run_All_Queries_Click

End Sub

Private Sub RunAQuery(strQueryName As String)
' Input : strQueryName Name of saved query to run
Dim db As Database
Dim qry As QueryDef
Set db = CurrentDb()
Set qry = db.OpenQueryDef(strQueryName) ' Return Error here
DoCmd SetWarnings False
qry.Execute
DoCmd SetWarnings True
qry.Close
db.Close
DoEvents
DBEngine.Idle
End Sub

mbizup

Try these modifications:

Private Sub RunAQuery(strQueryName As String)
' Input : strQueryName Name of saved query to run
Dim db As Database
Dim qry As QueryDef
Set db = CurrentDb()
' Set qry = db.OpenQueryDef(strQueryName) ' Return Error here
DoCmd SetWarnings False
docmd.openQuery strQueryName
'qry.Execute
DoCmd SetWarnings True
qry.Close
db.Close
DoEvents
DBEngine.Idle
End Sub

for the loop:

Dim strQuery as string
For I = 1 to 50
strQuery = "U" & I
RunAQuery strQuery
next

rgarimella

ASKER

Hi

I am getting the following errors

1) Compile Error on DoCmd SetWarnings False

2) If i Comment out the above statement, one query runs and I get the following error message

"Object Variable or With block variable not set"

Thanks

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rgarimella

ASKER

Ok
It worked
Thanks

mbizup

Glad to help!