HELP! Passing Parameters to Queries With VBA

[rburna904]

Ok Guys, I have researched this for the last few hours and am at a stand still.....

I have some queries and queries of queries etc. that need to be processed via an append query to a new table. Some of these queries require parameters to be passed to them. I have the following code and can't seem to get it to work and I am unsure of why....

Private Sub cmdProcess_Click()
    
    Dim rstCostCenters As DAO.Recordset
    Dim intCostCenters As Integer
    
    Dim rstB9DC_QTR1_2 As DAO.Recordset
    Dim qdB9DC_QTR1_2 As DAO.QueryDef
    
    Dim rstB9DC_QTR3 As DAO.Recordset
    Dim qdB9DC_QTR3 As DAO.QueryDef
    
    Dim rstB9OC As DAO.Recordset
    Dim qdB9OC As DAO.QueryDef
    
    Dim strFY As Integer
    Dim strCostCenter As Integer
    Dim strQuarter As Integer
    
    strFY = 2006
    strQuarter = 3
    
    Set rstCostCenters = CurrentDb.OpenRecordset("SELECT CostCenter FROM CostCenters WHERE FY = " & strFY & " AND Active = -1")
    
    If rstCostCenters.RecordCount <> 0 Then
        rstCostCenters.MoveLast
        intCostCenters = rstCostCenters.RecordCount
        rstCostCenters.MoveFirst
    End If
    
    Set qdB9DC_QTR1_2 = CurrentDb.QueryDefs![B9DC_QTR1-2]
    Set qdB9DC_QTR3 = CurrentDb.QueryDefs!B9DC_QTR3
    Set qdB9OC = CurrentDb.QueryDefs!B9OC
    
    Do While Not rstCostCenters.EOF
        
        qdB9DC_QTR1_2.Parameters("PFISCALYEAR").Value = strFY
        qdB9DC_QTR1_2.Parameters("PCOSTCENTER").Value = rstCostCenters.Fields("CostCenter").Value
        qdB9DC_QTR1_2.Parameters("QUARTER").Value = strQuarter
        
        Set rstB9DC_QTR1_2 = qdB9DC_QTR1_2.OpenRecordset
        DoCmd.OpenQuery "B9DC_QTR1-2-2", acViewNormal, acReadOnly
        
        qdB9DC_QTR3.Parameters("PFISCALYEAR").Value = strFY
        qdB9DC_QTR3.Parameters("PCOSTCENTER").Value = rstCostCenters.Fields("CostCenter").Value
        qdB9DC_QTR3.Parameters("QUARTER").Value = strQuarter
        
        Set rstB9DC_QTR3 = qdB9DC_QTR3.OpenRecordset
        DoCmd.OpenQuery "B9DC_QTR3", acViewNormal, acReadOnly
        
        qdB9OC.Parameters("PFISCALYEAR").Value = strFY
        qdB9OC.Parameters("PCOSTCENTER").Value = rstCostCenters.Fields("CostCenter").Value
        qdB9OC.Parameters("QUARTER").Value = strQuarter
        
        Set rstB9OC = qdB9OC.OpenRecordset
        DoCmd.OpenQuery "B9OC", acViewNormal, acReadOnly
        
        MsgBox IsArray(qdB9DC_QTR1_2) & vbCrLf & IsArray(qdB9DC_QTR3) & vbCrLf & IsArray(qdB9OC)
        
        rstB9DC_QTR1_2.Close
        rstB9DC_QTR3.Close
        rstB9OC.Close

        rstCostCenters.MoveNext
    Loop
    rstCostCenters.Close

End Sub

Whenever it goes to OpenRecordSet within the Do Loop it gives Run-time Error '3219' Invalid Operation.

I have tried to get these lines to work by adding in parameters etc. with no such luck....

Any and all help is appreciated....

 

[grnzbra]

try this change

...WHERE FY = '" & strFY & "' AND

Note the single quote add on either side of the string variable.

 

[rburna904]

Not where the problem is....

Sorry, i forgot to add that the error when debugging puts me on the following line

 Set rstB9DC_QTR1_2 = qdB9DC_QTR1_2.OpenRecordset

 

[john471]

1) It is unusual to dimension Integer variables using a name prefix of "str". Many would expect this prefix to indicate a string type variable. This possibly prompted the first reply.

2)

Set qdB9DC_QTR1_2 = CurrentDb.QueryDefs![B9DC_QTR1-2]
<snip>
Set rstB9DC_QTR1_2 = qdB9DC_QTR1_2.OpenRecordset

What type of query is "B9DC_QTR1-2"? e.g. select, update, append, make table... ?

Invalid operation. (Error 3219)
<snip>
You tried to use a method or property on a type of Recordset object that the method or property doesn't apply to. See the Recordset object Summary topic to determine which methods and properties apply to a given type of Recordset object.

HTH

Regards

John