Access VBA: Opening the recordset asks for 4 parameters

Question

Private Sub Command22_Click()

    ' This section deals with grabbing the 3 calculations from qry_LiveOEE
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    Set dbs = CurrentDb

    'Open a dynaset-type Recordset using a saved query
    Set rst = dbs.OpenRecordset("qry_LiveOEE", dbOpenDynaset)

    rst.MoveFirst
    numfg_posted = rst!SumOfqty_complete
    numOEE = rst!OEE
    numpp_lhr = rst!ACT_PPLHR

    rst.Close
    dbs.Close

    Set rst = Nothing
    Set dbs = Nothing

I get an error saying Too few parameters. Expected 4. This query has 5 things in the criteria section (design view), so why is it saying I need 4 parameters?

The 5 things in the criteria section (all under different fields) are:

input from a form
input from a form
Switch statement based on current time
Date()
Is Not Null

check out this link for more info :http://stackoverflow.com/questions/6034959/open-recordset-in-access-2003-2007 — SnakeFoot, Mar 30 '16 at 14:00

score 2 · Accepted Answer · answered Mar 30 '16 at 14:05

2

Try this (not tested) update to the code:

Private Sub Command22_Click()

    ' This section deals with grabbing the 3 calculations from qry_LiveOEE
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim qdf AS DAO.QueryDef
    Dim prm As DAO.Parameter

    Set dbs = CurrentDb

    Set qdf = dbs.QueryDefs("qry_LiveOEE")
    For Each prm In qdf.Parameters
        prm.Value = Eval(prm.Name)
    Next prm
    Set rst = qdf.OpenRecordset

    rst.MoveFirst
    numfg_posted = rst!SumOfqty_complete
    numOEE = rst!OEE
    numpp_lhr = rst!ACT_PPLHR

    rst.Close
    dbs.Close

    Set rst = Nothing
    Set dbs = Nothing

Make sure all the values in the parameters are available - i.e. the form is open.

answered Mar 30 '16 at 14:05

Darren Bartrup-Cook

18,362
1
23
45

1

@whatin1992: or see http://stackoverflow.com/a/32118340/3820271 if you need this regularly. – Andre Mar 30 '16 at 14:07
2

Yep, pulled it from my own function which does something similar (but also exports to Excel) - one change I'd make to the link is that the parameters are part of a collection so can use a `For...Each` construct rather than a `For...Next`. – Darren Bartrup-Cook Mar 30 '16 at 14:14
@DarrenBartrup-Cook: Heh, true. I think that function dates back to Access 2.0 when there was no `For Each`. :) -- Edited the answer. – Andre Mar 30 '16 at 14:43

Access VBA: Opening the recordset asks for 4 parameters

1 Answers1