OpenRecordSet too few parameters 4 / issues with QueryDefs

Question

Issue: I have a form (frm_input) where a user inputs a population range. Once the user enters the min and max of the range and clicks the 'ok' button, the values are input into a query (qryMasterQuery) and the query is run based on these inputs using a 'Between' statement referencing the fields in the form.

The ‘OK’ button also open another form ‘frm_output’. This form runs a function ‘percentile’ that calculates the percentiles of data in the query. The function is passed three parameters – the query name, the field within the query on which to calculate the percentile and the percentile to calculate.

The function and form run fine when I omit the "between" statements from the query. When I tried to tie it all together I received a 'too few parameters, 4' on my OpenRecordset() function. From what I've read I need to explicitly declare the DAO.Querydef object and supply the parameters via the Querydef objects parameters collection.

Issue 1: When I include the statement:

Set qdf = db.QueryDefs("qryMasterQuery")

I get a runtime 424 error message.

Much Bigger Issue 2: I am still unclear on the sytax for supplying parameters via qdf.Paramaters. I don't want to declare everything in the query, only what's being passed to the function, i.e., the 'fldName'.

my code is below:

Public Function PercentileRst(RstName As String, fldName As String, PercentileValue As Double) As Double

    'This function will calculate the percentile of a recordset.

    Dim PercentileTemp As Double
    Dim dbs As DAO.database
    Dim RstOrig As DAO.Recordset
    Set dbs = CurrentDb
    Dim xVal As Double
    Dim iRec As Long
    Dim i As Long
    Set RstOrig = CurrentDb.OpenRecordset("qryMasterFee", dbOpenDynaset)
    RstOrig.Sort = fldName
    Dim RstSorted As Recordset
    Set RstSorted = RstOrig.OpenRecordset()

    RstSorted.MoveLast
    RstSorted.MoveFirst
    xVal = ((RstSorted.RecordCount - 1) * PercentileValue) + 1
    'x now contains the record number we are looking for.
    'Note x may not be     whole number
    iRec = Int(xVal)
    xVal = xVal - iRec
    'i now contains first record to look at and
    'x contains diff to next record
    RstSorted.Move iRec - 1
    PercentileTemp = RstSorted(fldName)
    If xVal > 0 Then
        RstSorted.MoveNext
        PercentileTemp = ((RstSorted(fldName) - PercentileTemp) * xVal) + PercentileTemp
    End If
    RstSorted.Close
    RstOrig.Close
    Set RstSorted = Nothing
    Set RstOrig = Nothing
    Set dbs = Nothing
    PercentileRst = PercentileTemp
End Function

I am eternally greatful for any help as I am just about done banging me head on the wall.

usncahill usncahill · Accepted Answer · 2013-08-27T23:32:40

First a couple style tips. Dimension all your variables at the top of the function. And you can make your code text to look like code by surrounding by backticks like so or putting 4 spaces in front of each separate line.

Like so

You should not need to make a new sorted Recordset, as setting the Sort property will sort it.

Issue 1:
Don't forget to declare the QueryDef variable. Also, you missed the s in your Database variable dbs. Double-check qryMasterQuery is the right name.

Dim qdf as QueryDef
Set qdf = dbs.QueryDefs("qryMasterQuery")

See MSDN for more info.

Issue 2:
You will need to supply the parameters for the query to work. That explains the 'Too few parameters' error. The method is as follows:

Dim qdf as QueryDef
Set qdf = dbs.QueryDefs("qryMasterQuery")
qdf.Parameters("Prmtr1") = "blah" 'You can also use qdf.Parameters!Prmtr1 = "blah"
qdf.Parameters("Prmtr2") = 1234

set rstOrig = qdf.OpenRecordSet()

An alternative to supplying parameters is removing them from the query or creating a new query with the implied parameters already supplied.

See the Parameters MSDN for more info. Note the example is hard to follow and uses different methods.

OpenRecordSet too few parameters 4 / issues with QueryDefs

1 Answers