?
Solved

Running multiple Queries on Command Click

Posted on 2006-04-28
5
Medium Priority
?
1,406 Views
Last Modified: 2012-05-05
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


0
Comment
Question by:rgarimella
  • 3
  • 2
5 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 16561737
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

0
 

Author Comment

by:rgarimella
ID: 16561824
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
0
 
LVL 61

Accepted Solution

by:
mbizup earned 400 total points
ID: 16561938
Okay... take another stab at this..
Docmd.SetWarnings False
         ^----Needs a dot


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   ******** Comment out this line too.
  db.Close
  DoEvents
  DBEngine.Idle
End Sub
0
 

Author Comment

by:rgarimella
ID: 16561969
Ok
It worked
Thanks
0
 
LVL 61

Expert Comment

by:mbizup
ID: 16561982
Glad to help!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question