Elookup and Paramiter query

[paii, Ron]

I am trying to use the following "Elookup" function on a query that gets 2
parameters from an open form.

?eLookup("[MatEst]", "qryWOLPCMatEst")

Returns the following error.

Too few parameters. Expected 2. 48 eLookup Error 3061
Error 3061

?dLookup("[MatEst]", "qryWOLPCMatEst")

Returns the correct value

Is there anyway to get eLookup to see the form values?

Public Function ELookup(expr As String, domain As String, Optional Criteria,
Optional OrderClause)

On Error GoTo Err_ELookup
'Purpose: Faster and more flexible replacement for DLookup()
'Arguments: Same as DLookup, with additional Order By option.
'Return: Value of the Expr if found, else Null or #Error.
'Author: Allen Browne. ab******@bigpond.net.au
'Examples:
'1. To find the last value, include DESC in the OrderClause, e.g.:
' ELookup("[Surname] & [FirstName]", "tblClient", , "ClientID DESC")
'2. To find the lowest non-null value of a field, use the Criteria,
'e.g.:
' ELookup("ClientID", "tblClient", "Surname Is Not Null" , "Surname")
'Note: Requires a reference to the DAO library.
Dim MyDb As Database
Dim rs As Recordset
Dim SQLStg As String

'Build the SQL string.
SQLStg = "SELECT TOP 1 " & expr & " FROM " & domain
If Not IsMissing(Criteria) Then
SQLStg = SQLStg & " WHERE " & Criteria
End If
If Not IsMissing(OrderClause) Then
SQLStg = SQLStg & " ORDER BY " & OrderClause
End If
SQLStg = SQLStg & ";"

'Lookup the value.
Set MyDb = DBEngine(0)(0)
Set rs = MyDb.OpenRecordset(SQLStg, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
ELookup = Null
Else
ELookup = rs(0)
End If
rs.Close

Exit_ELookup:
Set rs = Nothing
Set MyDb = Nothing
Exit Function

Err_ELookup:
' MsgBox Err.Description, vbExclamation, "ELookup Error " & Err.number
If Err.Number < 0& Or Err.Number > 65535 Then 'Valid range for
CVErr()
ELookup = CVErr(5) 'Out of range.
Else
ELookup = CVErr(Err.Number)
End If
Resume Exit_ELookup

End Function

[Allen Browne]

You cannot use a parameter query as input to this function.

You cannot use a parameter query as input to the built-in DLookup() either.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"paii, Ron" <pa**@packairinc.com> wrote in message
news:cJ********************@athenet.net...

I am trying to use the following "Elookup" function on a query that gets 2
parameters from an open form.

?eLookup("[MatEst]", "qryWOLPCMatEst")

Returns the following error.

Too few parameters. Expected 2. 48 eLookup Error 3061
Error 3061

?dLookup("[MatEst]", "qryWOLPCMatEst")

Returns the correct value

Is there anyway to get eLookup to see the form values?

Public Function ELookup(expr As String, domain As String, Optional
Criteria,
Optional OrderClause)

On Error GoTo Err_ELookup
'Purpose: Faster and more flexible replacement for DLookup()
'Arguments: Same as DLookup, with additional Order By option.
'Return: Value of the Expr if found, else Null or #Error.
'Author: Allen Browne. ab******@bigpond.net.au
'Examples:
'1. To find the last value, include DESC in the OrderClause, e.g.:
' ELookup("[Surname] & [FirstName]", "tblClient", , "ClientID DESC")
'2. To find the lowest non-null value of a field, use the Criteria,
'e.g.:
' ELookup("ClientID", "tblClient", "Surname Is Not Null" , "Surname")
'Note: Requires a reference to the DAO library.
Dim MyDb As Database
Dim rs As Recordset
Dim SQLStg As String

'Build the SQL string.
SQLStg = "SELECT TOP 1 " & expr & " FROM " & domain
If Not IsMissing(Criteria) Then
SQLStg = SQLStg & " WHERE " & Criteria
End If
If Not IsMissing(OrderClause) Then
SQLStg = SQLStg & " ORDER BY " & OrderClause
End If
SQLStg = SQLStg & ";"

'Lookup the value.
Set MyDb = DBEngine(0)(0)
Set rs = MyDb.OpenRecordset(SQLStg, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
ELookup = Null
Else
ELookup = rs(0)
End If
rs.Close

Exit_ELookup:
Set rs = Nothing
Set MyDb = Nothing
Exit Function

Err_ELookup:
' MsgBox Err.Description, vbExclamation, "ELookup Error " & Err.number
If Err.Number < 0& Or Err.Number > 65535 Then 'Valid range for
CVErr()
ELookup = CVErr(5) 'Out of range.
Else
ELookup = CVErr(Err.Number)
End If
Resume Exit_ELookup

End Function

[paii, Ron]

The query uses

((tblxWO.Item)=[Forms]![frmSystem]![qryP2])

in the WHERE clause. and works with dlookup.

[Forms]![frmSystem]![qryP2] is a unbound text field on an open form.

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...

You cannot use a parameter query as input to this function.

You cannot use a parameter query as input to the built-in DLookup() either.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"paii, Ron" <pa**@packairinc.com> wrote in message
news:cJ********************@athenet.net...

I am trying to use the following "Elookup" function on a query that gets 2 parameters from an open form.

?eLookup("[MatEst]", "qryWOLPCMatEst")

Returns the following error.

Too few parameters. Expected 2. 48 eLookup Error 3061 Error 3061

?dLookup("[MatEst]", "qryWOLPCMatEst")

Returns the correct value

Is there anyway to get eLookup to see the form values?

Public Function ELookup(expr As String, domain As String, Optional
Criteria,
Optional OrderClause)

On Error GoTo Err_ELookup
'Purpose: Faster and more flexible replacement for DLookup()
'Arguments: Same as DLookup, with additional Order By option.
'Return: Value of the Expr if found, else Null or #Error.
'Author: Allen Browne. ab******@bigpond.net.au
'Examples:
'1. To find the last value, include DESC in the OrderClause, e.g.:
' ELookup("[Surname] & [FirstName]", "tblClient", , "ClientID DESC")
'2. To find the lowest non-null value of a field, use the Criteria,
'e.g.:
' ELookup("ClientID", "tblClient", "Surname Is Not Null" , "Surname") 'Note: Requires a reference to the DAO library.
Dim MyDb As Database
Dim rs As Recordset
Dim SQLStg As String

'Build the SQL string.
SQLStg = "SELECT TOP 1 " & expr & " FROM " & domain
If Not IsMissing(Criteria) Then
SQLStg = SQLStg & " WHERE " & Criteria
End If
If Not IsMissing(OrderClause) Then
SQLStg = SQLStg & " ORDER BY " & OrderClause
End If
SQLStg = SQLStg & ";"

'Lookup the value.
Set MyDb = DBEngine(0)(0)
Set rs = MyDb.OpenRecordset(SQLStg, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
ELookup = Null
Else
ELookup = rs(0)
End If
rs.Close

Exit_ELookup:
Set rs = Nothing
Set MyDb = Nothing
Exit Function

Err_ELookup:
' MsgBox Err.Description, vbExclamation, "ELookup Error " & Err.number If Err.Number < 0& Or Err.Number > 65535 Then 'Valid range for
CVErr()
ELookup = CVErr(5) 'Out of range.
Else
ELookup = CVErr(Err.Number)
End If
Resume Exit_ELookup

End Function

[Allen Browne]

Okay. DLookup is able to use the Expression Service to resolve that kind of
parameter.

The Expression Service is not available in the context of records opened in
code, so it won't work with ELookup().

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"paii, Ron" <pa**@packairinc.com> wrote in message
news:Q6********************@athenet.net...

The query uses

((tblxWO.Item)=[Forms]![frmSystem]![qryP2])

in the WHERE clause. and works with dlookup.

[Forms]![frmSystem]![qryP2] is a unbound text field on an open form.

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...

You cannot use a parameter query as input to this function.

You cannot use a parameter query as input to the built-in DLookup()

either.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"paii, Ron" <pa**@packairinc.com> wrote in message
news:cJ********************@athenet.net...

>I am trying to use the following "Elookup" function on a query that gets 2 > parameters from an open form.
>
> ?eLookup("[MatEst]", "qryWOLPCMatEst")
>
> Returns the following error.
>
> Too few parameters. Expected 2. 48 eLookup Error 3061 > Error 3061
>
> ?dLookup("[MatEst]", "qryWOLPCMatEst")
>
> Returns the correct value
>
> Is there anyway to get eLookup to see the form values?
>
> Public Function ELookup(expr As String, domain As String, Optional
> Criteria,
> Optional OrderClause)
>
> On Error GoTo Err_ELookup
> 'Purpose: Faster and more flexible replacement for DLookup()
> 'Arguments: Same as DLookup, with additional Order By option.
> 'Return: Value of the Expr if found, else Null or #Error.
> 'Author: Allen Browne. ab******@bigpond.net.au
> 'Examples:
> '1. To find the last value, include DESC in the OrderClause, e.g.:
> ' ELookup("[Surname] & [FirstName]", "tblClient", , "ClientID
> DESC")
> '2. To find the lowest non-null value of a field, use the Criteria,
> 'e.g.:
> ' ELookup("ClientID", "tblClient", "Surname Is Not Null" , "Surname") > 'Note: Requires a reference to the DAO library.
> Dim MyDb As Database
> Dim rs As Recordset
> Dim SQLStg As String
>
> 'Build the SQL string.
> SQLStg = "SELECT TOP 1 " & expr & " FROM " & domain
> If Not IsMissing(Criteria) Then
> SQLStg = SQLStg & " WHERE " & Criteria
> End If
> If Not IsMissing(OrderClause) Then
> SQLStg = SQLStg & " ORDER BY " & OrderClause
> End If
> SQLStg = SQLStg & ";"
>
> 'Lookup the value.
> Set MyDb = DBEngine(0)(0)
> Set rs = MyDb.OpenRecordset(SQLStg, dbOpenForwardOnly)
> If rs.RecordCount = 0 Then
> ELookup = Null
> Else
> ELookup = rs(0)
> End If
> rs.Close
>
> Exit_ELookup:
> Set rs = Nothing
> Set MyDb = Nothing
> Exit Function
>
> Err_ELookup:
> ' MsgBox Err.Description, vbExclamation, "ELookup Error " & Err.number > If Err.Number < 0& Or Err.Number > 65535 Then 'Valid range for
> CVErr()
> ELookup = CVErr(5) 'Out of range.
> Else
> ELookup = CVErr(Err.Number)
> End If
> Resume Exit_ELookup
>
> End Function

[paii, Ron]

Thank you for your help and the function(s).
By the way I modified the record set open with the following line

Set rs = CurrentDb.OpenRecordset(SQLStg, dbOpenForwardOnly)
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...

Okay. DLookup is able to use the Expression Service to resolve that kind of parameter.

The Expression Service is not available in the context of records opened in code, so it won't work with ELookup().

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"paii, Ron" <pa**@packairinc.com> wrote in message
news:Q6********************@athenet.net...

The query uses

((tblxWO.Item)=[Forms]![frmSystem]![qryP2])

in the WHERE clause. and works with dlookup.

[Forms]![frmSystem]![qryP2] is a unbound text field on an open form.

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...

You cannot use a parameter query as input to this function.

You cannot use a parameter query as input to the built-in DLookup()

either.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"paii, Ron" <pa**@packairinc.com> wrote in message
news:cJ********************@athenet.net...
>I am trying to use the following "Elookup" function on a query that gets

2

> parameters from an open form.
>
> ?eLookup("[MatEst]", "qryWOLPCMatEst")
>
> Returns the following error.
>
> Too few parameters. Expected 2. 48 eLookup Error

3061

> Error 3061
>
> ?dLookup("[MatEst]", "qryWOLPCMatEst")
>
> Returns the correct value
>
> Is there anyway to get eLookup to see the form values?
>
> Public Function ELookup(expr As String, domain As String, Optional
> Criteria,
> Optional OrderClause)
>
> On Error GoTo Err_ELookup
> 'Purpose: Faster and more flexible replacement for DLookup()
> 'Arguments: Same as DLookup, with additional Order By option.
> 'Return: Value of the Expr if found, else Null or #Error.
> 'Author: Allen Browne. ab******@bigpond.net.au
> 'Examples:
> '1. To find the last value, include DESC in the OrderClause, e.g.:
> ' ELookup("[Surname] & [FirstName]", "tblClient", , "ClientID
> DESC")
> '2. To find the lowest non-null value of a field, use the

Criteria, > 'e.g.:
> ' ELookup("ClientID", "tblClient", "Surname Is Not Null" ,

"Surname")

> 'Note: Requires a reference to the DAO library.
> Dim MyDb As Database
> Dim rs As Recordset
> Dim SQLStg As String
>
> 'Build the SQL string.
> SQLStg = "SELECT TOP 1 " & expr & " FROM " & domain
> If Not IsMissing(Criteria) Then
> SQLStg = SQLStg & " WHERE " & Criteria
> End If
> If Not IsMissing(OrderClause) Then
> SQLStg = SQLStg & " ORDER BY " & OrderClause
> End If
> SQLStg = SQLStg & ";"
>
> 'Lookup the value.
> Set MyDb = DBEngine(0)(0)
> Set rs = MyDb.OpenRecordset(SQLStg, dbOpenForwardOnly)
> If rs.RecordCount = 0 Then
> ELookup = Null
> Else
> ELookup = rs(0)
> End If
> rs.Close
>
> Exit_ELookup:
> Set rs = Nothing
> Set MyDb = Nothing
> Exit Function
>
> Err_ELookup:
> ' MsgBox Err.Description, vbExclamation, "ELookup Error " &

Err.number

> If Err.Number < 0& Or Err.Number > 65535 Then 'Valid range for
> CVErr()
> ELookup = CVErr(5) 'Out of range.
> Else
> ELookup = CVErr(Err.Number)
> End If
> Resume Exit_ELookup
>
> End Function