How to not open tables in multiple table query

[Sandy]

Hello -

I have an event procedure that runs several queries in a row. SOme of these
are make table queries, and the final command is to open a report?

How do I disable the tables opening up as I run the query? acView does not
show a "hide" option ...

Dim stDocName As String

stDocName = "BestResults-TestSummary"

DoCmd.SetWarnings False 'disable warnings

DoCmd.OpenQuery "Q_BestResults-Sub1" ' make table query
DoCmd.OpenQuery "Q_BestResults-Sub2" ' uses table in Sub1
DoCmd.OpenQuery "Q_BestResultsSub3a"
DoCmd.OpenQuery "Q_BestResultsSub3b" ' make table query
DoCmd.OpenQuery "Q_BestResultsSub4a"
DoCmd.OpenQuery "Q_BestResultsSub4b" ' append query
DoCmd.OpenQuery "Q_BestResultsSub5a"
DoCmd.OpenQuery "Q_BestResultsSub5b" ' append query

DoCmd.SetWarnings True 'enable warnings

DoCmd.OpenReport stDocName, acPreview

The report is based on a query that uses the table form Sub 1 and the final
table in Sub 5b

many thanks
sandy

 

[Douglas J. Steele]

There's no reason to use OpenQuery with action queries (Append, Update,
Delete, Insert Into).

Try:

Dim dbCurr As DAO.Database
Dim stDocName As String

Set dbCurr = CurrentDb()
dbCurr.QueryDefs("Q_BestResults-Sub1").Execute dbFailOnError
dbCurr.QueryDefs("Q_BestResults-Sub2").Execute dbFailOnError
dbCurr.QueryDefs("Q_BestResultsSub3a").Execute dbFailOnError
dbCurr.QueryDefs("Q_BestResultsSub3b").Execute dbFailOnError
dbCurr.QueryDefs("Q_BestResultsSub4a").Execute dbFailOnError
dbCurr.QueryDefs("Q_BestResultsSub4b").Execute dbFailOnError
dbCurr.QueryDefs("Q_BestResultsSub5a").Execute dbFailOnError
dbCurr.QueryDefs("Q_BestResultsSub5b").Execute dbFailOnError
Set dbCurr = Nothing

stDocName = "BestResults-TestSummary"
DoCmd.OpenReport stDocName, acPreview

 

[Sandy]

Thanks, Doug -

I gave it a try and I get this error:

Run-time error '3061':
Too few parameters. Expected 2.

The debug window is highlighting the first dbCurr line

any ideas?

 

[Douglas J. Steele]

That implies that your query uses parameters. Assuming these are references
to controls on a form, you'll need to resolve those references.

Off the top of my head, I believe it's something like:

Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef
Dim prmCurr As DAO.Parameter
Dim stDocName As String

Set dbCurr = CurrentDb()
Set qdfCurr = dbCurr.QueryDefs("Q_BestResults-Sub1")
For Each prmCurr In qdfCurr.Parameters
Set prmCurr = Eval(prmCurr.Name)
Next prmCurr
qdfCurr.Execute dbFailOnError

 

[Steve Schapel]

That implies that your query uses parameters. Assuming these are references
to controls on a form, you'll need to resolve those references.

.... or use DoCmd.OpenQuery instead. ;-)

 

[Steve Schapel]

Sandy,

In addition to Doug's advice, I think perhaps there is another confusion
here as well. It seems that some of your queries are *not* action
queries. Is that right? Perhaps you are under the impression that if
an action query (append, make-table, etc) is itself based on a query
(select query), that you have to run the select query before you can use
it within the action query. If so, this is not correct. If your
queries such as Q_BestResultsSub3a are Select queries, then those lines
should simply be removed from the code.