Execute Append Query?

[DAW]

Can someoone tell me what I'm missing here? Its gonna be something daft I know...
The append query just adds data to a table incrementing by weeknumber. The error I get is '3061: Too few parameters...' as it tries to run the line 'qd.Execute'.

    Dim SWk%, EWk%, i%
    Dim qd As DAO.QueryDef
    '-------------------
    SWk = Val(Me.lblFWk.Caption) 'Start week#
    EWk = Val(Me.lblTWk.Caption) 'End week#
    Set qd = CurrentDb.QueryDefs!qry_2Temp5Append
    For i = SWk To EWk
        qd.Parameters![Wk] = i
        qd.Execute
    Next
    qd.Close
    Set qd = Nothing

 

[DAW]

Well, to put it another way, if you wanted to append data to a table from week x to week y (user parameters) how would you code it?

 

[gemma-the-husky]

try

currentdb.execute qd

 

[DAW]

try

currentdb.execute qd

Error: Type mismatch!

 

[LPurvis]

Your query qry_2Temp5Append only has one parameter in it?
What's the SQL of the query?
(Your code should be fine given the current information).

 

[DAW]

Your query qry_2Temp5Append only has one parameter in it?
What's the SQL of the query?
(Your code should be fine given the current information).

No, it has 3, but the other two are taken from data directly on the form (i.e. not in the code loop iteration)

 

[LPurvis]

They're still parameters in Jet's eyes.
That (when executed in Access) the Expression Service evaluates them implicitly for you - doesn't change the fact that they're just parameters.
Through code (using one of the data access APIs like DAO or ADO) you're still obliged to evaluate those parameters.
A similar thread was discussed recently here .

However for executing a query then you'll start from a slightly different function.

Function fExecuteQuery(strQuery As String, Optional intOptions As DAO.RecordsetOptionEnum = dbFailOnError, _
                                           Optional blnReturnAuto As Boolean = False) As Long
    
    Dim db As Database
    Dim prm As DAO.Parameter
    Dim qdf As QueryDef
    Dim rst As DAO.Recordset
    
    Set db = CurrentDb
    Select Case Left(strQuery, 6)
    Case "INSERT", "UPDATE", "DELETE"
        Set qdf = db.CreateQueryDef("", strQuery)
    Case Else
        Set qdf = db.QueryDefs(strQuery)
    End Select
    
    For Each prm In qdf.Parameters
        prm.Value = Eval(prm.Name)
    Next
    
    qdf.Execute intOptions
    If blnReturnAuto Then
        Set rst = db.OpenRecordset("SELECT @@Identity")
        fExecuteQuery = rst(0)
        rst.Close
    End If
    
    Set prm = Nothing
    Set rst = Nothing
    Set qdf = Nothing
    Set db = Nothing
End Function

But you'll need to work in a passing of your unevaluated parameter values too.
I'd suggest a ParamArray argument to the function - passing in the ParameterName and Value.

 

[DAW]

Excellent. I understand what you are saying, and by reading the link you posted, and am now working to redesign my code.
Thanks very much for posting your help.

 

[LPurvis]

Cool. No probs.