Error with Allen Browne's ELOOKUP: Too Few Parameters

Question

I have been working on this for about an hour and can't get the function to run without errors. I am using Allen Browne's ELOOKUP function from this page.

Here is the code currently calling the public function:

Dim r As Double
Dim Q1 As Double
Dim i1 As Double
r = Me!txtRepeat
Q1 = Me!txtQty1
i1 = ELookup("dblIndex", "zhqry_ActiveGrid_3_Final", "dblRepeat = " & r & " AND dblQuantity <= " & Q1, "dblQuantity DESC")

I get the error "Too few parameters: Expected 1."

I have stepped through the code and was able to extract strSQL which was:

SELECT TOP 1 dblIndex 
FROM zhqry_ActiveGrid_3_Final 
WHERE dblRepeat = 12 AND dblQuantity <= 55 
ORDER BY dblQuantity DESC;

The actual error is thrown on this line of the ELOOKUP function:

Set rs = db.OpenRecordset(strSql, dbOpenForwardOnly)

I have tried:

Encapsulating all field names in square brackets. Same error.
Adding single quotes around my criteria values. Same error.
Changed function line:Set db = DBEngine(0)(0) to set db = CurrentDb, Same error.
Changed dbOpenForwardOnly to dbOpenSnapshot. Same error.

I copied the strSQL value into Access's query builder and it ran fine so I know that I am building a valid string. I am using Access 2010.

What am I doing wrong?

Does zhqry_ActiveGrid_3_Final (or a subquery of it) have parameters like e.g. Forms!myForm!myValue? They are evaluated when opening the query in Access, but not when opening a recordset on it. -- See "Limitations of ELookup(): ... DLookup() can call the expression service to resolve an argument.." — Andre
@Andre ah I thought that restriction was only for 'strSQL'. Yes my stacked query has a form reference. I need to make an approximate match so dlookup isn't reliable. I'll see if I can rework my code. Thanks! — MoondogsMaDawg

Andre Andre · Accepted Answer · 2016-08-04T15:30:20

Limitations of ELookup():

DLookup() can call the expression service to resolve an argument such as:
DLookup("Surname", "Clients", "ClientID = [Forms].[Form1].[ClientID]")

This also applies to a query that is used as Domain parameter - parameters like this are resolved automatically in Access queries, but not in a recordset, which ELookup() uses.

You could modify ELookup() to not open the recordset directly, but from a Querydef object. Then you can call Eval_Params() from this answer on the QD object to resolve the parameters.

Error with Allen Browne's ELOOKUP: Too Few Parameters

1 Answers