Open variable number of queries

[Chrafangau]

What would the best way to open a series of queries, if you aren't sure how many of those queries there are? I have the following:

    DoCmd.Close acReport, "SCEFReport"
    DoCmd.OpenQuery "ClearTBLQuestions", acViewNormal, acEdit
    DoCmd.OpenQuery "Q1append", acViewNormal, acEdit
    DoCmd.OpenQuery "Q2append", acViewNormal, acEdit
    DoCmd.OpenQuery "Q3append", acViewNormal, acEdit

Which continues as is for 10 or 11 queries named in a similar pattern, then prints a report. However, as the number of append queries is not set (they are generated in a different quantity for different purposes, it's complicated), I am not sure how to check how many there are with that style of name.

 

[pr2-eugin]

That is quiet a bizarre request, so many Queries? all in Edit mode? Not sure what you are trying to achieve, but something along the lines should be doing the trick I guess.

    DoCmd.Close acReport, "SCEFReport"
    DoCmd.OpenQuery "ClearTBLQuestions", acViewNormal, acEdit
    Dim iCtr As Long
    iCtr = 1
    While [COLOR=Blue]checkExistance[/COLOR]("Q" & iCtr & "append") 
        DoCmd.OpenQuery "Q" & iCtr & "append", acViewNormal, acEdit
        iCtr = iCtr + 1
    Wend

Where checkExistance is a public function that checks if the Query exists in the DB.

Public Function checkExistance(qryName As String) As Boolean
[COLOR=Green]'**********************
'Code Courtesy of
'  Paul Eugin
'**********************[/COLOR]
On Error GoTo handleMe
    checkExistance = IsObject(CurrentDb.QueryDefs(qryName))
exitOnErr:
    Exit Function
handleMe:
    If Err.Number = 3265 Then 
        checkExistance = False
    Else
        MsgBox "Error (" & Err.Number & ") - Encountered", vbCritical
    End If
    Resume exitOnErr
End Fucntion

 

[Chrafangau]

Thanks heaps Paul, this served the purpose perfectly. And now I can use checkExistance for a bunch of other spots where I was stuck. Really appreciate it.
And as for the oddity of all the queries, I ran into an 'expression too complex' issue a while back that I worked around with a series of small queries and appends.

 

[pr2-eugin]

Queries too Complex error normally occur when you use many functions on one single Column. Example, you use 10 or more nested IIF to get the value for a particular column. Or something. You can overcome this issue by using a Function.