Run queries in sequence and combo box

[thart21]

Not sure if I should put this in the "Queries" forum or "Programming" but I have a Function that loops through and runs 12 queries I have listed in a table. 3 of those queries have criteria that is tied to a text or combo box on my form. When it gets to the first query that has the criteria of Forms!frmDP!Text22 it should run with the criteria the user selected. Instead I am getting a "Too few parameters expected 1" error message and can't find anything to tell me what I'm missing.

Query 1 criteria - Forms!frmDP!Text22
Query 2 criteria - Forms!frmDP!cboSeason
Query 3 criteria - Forms!frmDP!cboSeason

Public Function fQueryBatch() As Byte

Dim strSQL As String
'Dim strSQLFinal As String
strSQL = "SELECT * FROM tQueries ORDER BY queryorder ASC"

With CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

Do Until .EOF
CurrentDb.Execute .Fields("qname"), dbFailOnError
.MoveNext
Loop
End With

Dim stDocName As String
stDocName = "qFcst_Workbook_7_Final"
DoCmd.OpenQuery stDocName, acNormal, acEdit


End Function



Any help would be appreciated. I was thinking that I may need to add a "WHERE" clause to my strSQL but doesn't the criteria line of "Forms!frmDP!cboSeason" have the same affect?

Thanks,

Toni

[pbaldy]

The problem is that the Execute method can't resolve form references in the query. You can try wrapping them in the Eval() function, or using OpenQuery instead of Execute. You can also add it to an SQL string in code, but you'd concatenate it in:

"...WHERE FieldName = " & Forms!FormName.ControlName & " ORDER BY..."

That resolves the form reference so the SQL just sees the value from the form.

[thart21]

Thanks for the suggestions, tried this, not sure if it's correct. It seems to be showing the SQL correctly when I hover over it but I'm getting a "too few parameters. Expected 2" message on the line "With CurrentDb....."

Is the problem have something to do with the fact that I have different field names in the queries or does that matter?

strSQL = "SELECT * FROM tQueries"

strSQL = strSQL & " WHERE SOURCING_SEASON = '" & Forms!frmDP.Text22 & "'"
strSQL = strSQL & " & SELL_SEASON = '" & Forms!frmDP.cboSeason.Value & "'"
strSQL = strSQL & " ORDER BY queryorder ASC"

With CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)...

[pbaldy]

That appears to be your table with queries listed in it. Does it have those fields in it? I was under the impression that the queries listed in that table had the criteria in them.

[thart21]

I think that's where my confusion is. Yes, my queries are listed in the table- and "SOURCING_SEASON" AND "SELL_SEASON" are fields in the queries themselves that have the criteria. The two fields have the same data, but they are called different names within the tables the data resides in (nothing I can do about that-linked Oralce tables i have no control over). Does that answer your question?

Thanks for the help. Would love to go home tonight actually accomplishing this one!

[pbaldy]

That's not going to be a viable solution here then. Try the other 2 things: Eval() or OpenQuery.

[thart21]

I will do that. Do you mean because of the multiple field critera?

Thanks for the help!

[pbaldy]

No, because the criteria aren't really part of the SQL you're building. Apples and oranges. That SQL is to get which queries to run. The criteria are within those queries, so not relevant to the SQL being built in code. If you were selecting which queries to run based on some value entered on the form, it would be appropriate.

[thart21]

I see. So, I thought that by entering in the criteria line of the relevant queries the Forms!frmDP!cboSeason (or Text22) this took care of my having to tell it in SQL what the criteria was and it would just run based on what was selected on the form. I thought I wouldn't have to do anymore than my original SQL statement telling it to run all the queries in the table. Am I way off base in my thinking?

[pbaldy]

In theory no, but as I said the Execute method can't resolve form references in the criteria of the query being executed. Try changing this line:

CurrentDb.Execute .Fields("qname"), dbFailOnError

to

DoCmd.SetWarnings False
DoCmd.OpenQuery .Fields("qname")
DoCmd.SetWarnings True

[thart21]

Thank you it works perfectly! I apprecaite all the help.