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******@bigpon d.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("Client ID", "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(Crite ria) Then
SQLStg = SQLStg & " WHERE " & Criteria
End If
If Not IsMissing(Order Clause) Then
SQLStg = SQLStg & " ORDER BY " & OrderClause
End If
SQLStg = SQLStg & ";"
'Lookup the value.
Set MyDb = DBEngine(0)(0)
Set rs = MyDb.OpenRecord set(SQLStg, dbOpenForwardOn ly)
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.Numbe r)
End If
Resume Exit_ELookup
End Function 4  6699 
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**@packairin c.com> wrote in message
news:cJ******** ************@at henet.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******@bigpon d.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("Client ID", "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(Crite ria) Then
SQLStg = SQLStg & " WHERE " & Criteria
End If
If Not IsMissing(Order Clause) Then
SQLStg = SQLStg & " ORDER BY " & OrderClause
End If
SQLStg = SQLStg & ";"
'Lookup the value.
Set MyDb = DBEngine(0)(0)
Set rs = MyDb.OpenRecord set(SQLStg, dbOpenForwardOn ly)
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.Numbe r)
End If
Resume Exit_ELookup
End Function
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*********@Se eSig.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**@packairin c.com> wrote in message
news:cJ******** ************@at henet.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******@bigpon d.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("Client ID", "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(Crite ria) Then
SQLStg = SQLStg & " WHERE " & Criteria
End If
If Not IsMissing(Order Clause) Then
SQLStg = SQLStg & " ORDER BY " & OrderClause
End If
SQLStg = SQLStg & ";"
'Lookup the value.
Set MyDb = DBEngine(0)(0)
Set rs = MyDb.OpenRecord set(SQLStg, dbOpenForwardOn ly)
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.Numbe r)
End If
Resume Exit_ELookup
End Function
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**@packairin c.com> wrote in message
news:Q6******** ************@at henet.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*********@Se eSig.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**@packairin c.com> wrote in message
news:cJ******** ************@at henet.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******@bigpon d.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("Client ID", "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(Crite ria) Then
> SQLStg = SQLStg & " WHERE " & Criteria
> End If
> If Not IsMissing(Order Clause) Then
> SQLStg = SQLStg & " ORDER BY " & OrderClause
> End If
> SQLStg = SQLStg & ";"
>
> 'Lookup the value.
> Set MyDb = DBEngine(0)(0)
> Set rs = MyDb.OpenRecord set(SQLStg, dbOpenForwardOn ly)
> 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.Numbe r)
> End If
> Resume Exit_ELookup
>
> End Function
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.OpenR ecordset(SQLStg , dbOpenForwardOn ly)
"Allen Browne" <Al*********@Se eSig.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**@packairin c.com> wrote in message
news:Q6******** ************@at henet.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*********@Se eSig.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**@packairin c.com> wrote in message
news:cJ******** ************@at henet.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******@bigpon d.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("Client ID", "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(Crite ria) Then
> SQLStg = SQLStg & " WHERE " & Criteria
> End If
> If Not IsMissing(Order Clause) Then
> SQLStg = SQLStg & " ORDER BY " & OrderClause
> End If
> SQLStg = SQLStg & ";"
>
> 'Lookup the value.
> Set MyDb = DBEngine(0)(0)
> Set rs = MyDb.OpenRecord set(SQLStg, dbOpenForwardOn ly)
> 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.Numbe r)
> End If
> Resume Exit_ELookup
>
> End Function
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: lawrence |
last post by:
A very strange bug. www.monkeyclaus.org is run by a cms I'm
developing. One of types of users we allow is "justTestingTheSite", a
type of user I developed to give demo's to prospective clients. The
purpose of this level of security is to let someone log in and see
everything as if they were root, and yet not be able to update or
delete anything, as they have no real priveledges at all.
I just logged in as root and created such an...
|
by: Polar |
last post by:
I am having troubles finding the parse error in this script. I've been
checking for weeks. I am too new to the subject I guess.
I am trying to show a readord and them have a form at the bottom of the
page for inputting data to Update the record. I get a parse error that
points to the last line in the script so I know it is something I am missing
earlier in the script but doing a line by line it seems fine to me.
Thanks for any...
|
by: jaysonsch |
last post by:
Hello!
I am having some problems with a database query that I am trying to do.
I am trying to develop a way to search a database for an entry and
then edit the existing values. Upon submit, the new values are updated
in all corresponding tables (the function of the pages in question).
However, on the page that does the DB update, I also want to do some
checks on the data before performing the update.
Now, the problem that I am...
|
by: shank |
last post by:
1) I'm getting this error: Syntax error (missing operator) in query
expression on the below statement. Can I get some advice.
2) I searched ASPFAQ and came up blank. Where can find the "rules" for when
and how to use single quotes and double quotes in ASP?
thanks!
----------------------
SQL = SQL & "WHERE '" &
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE("GenKTitles.
|
by: DBNovice |
last post by:
I have a database that keeps records on the issue and failure of an item.
Currently, the database is poorly desisned; therefore I'm performing queries
to break the data into normalized tables and attempting to perform a "left
join" query to build a cross-reference table.
The left join query is currently taking nearly 2 hours for MySQL to process,
using Navicat as a front-end. My system specs are 1.4Mhz Pentium Processor
with 1GB of RAM...
| | |
by: netpurpose |
last post by:
I need to extract data from this table to find the lowest prices of
each product as of today. The product will be listed/grouped by the
name only, discarding the product code - I use SUBSTRING(ProductName,
1, CHARINDEX('(', ProductName)-2).
I can get this result, but I had to use several views (totally
inefficient). I think this can be done in one efficient/fast query,
but I can't think of one.
In the case that one query is not...
|
by: P.L. Owens |
last post by:
I am trying to open a pop-up window with its own htm code, in order to show
an enlargement of a thumbnail. But I am passing a query string after the? in
the url of the new window. for example:
www.helpme.com?newpic.jpg
I am then trying to put the query string in
<IMG SRC="querystring" WIDTH=400 BORDER=0>.
I don't know how to get the query string to echo "newpic.jpg" in the htm
command in the pop up window.
Thank You.
|
by: Harvey |
last post by:
Hi,
I try to write an asp query form that lets client search any text-string and
display all pages in my web server that contain the text. I have IIS 6.0 on a
server 2003. The MSDN site says there is a sample file called Ixtrasp.asp,
but I could not find it in my system although I installed indexing service. I
followed the steps in MSDN site to create a basic .asp query form (too long
to post it here), but it always displays:
No...
|
by: ctyrrell |
last post by:
I have read with interest the many discussions of the 3048 Error:
Cannot open any more databases. I understand how the number of open
Table ID's causes the problem. My question has to do with the usage of
the Elookup routine as a substitute for Dlookup. I read that Dlookup
'does not clean up after itself'. Most of the discussion centered
around the use of Dlookup in queries or in the controlsource of form
and report controls.
My...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| | |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
