Lawrence Salvucci
asked on
DLookup in Access Form
I have a form with 24 DLookup controls on it and it's taking forever to calculate & populate the results. I've been searching for an alternative to DLookup and came across the "ELookup" function. I'm told it's much faster than the DLookup. But when I try to use it I get a "Too Few Parameters, Expected 1". I'm guessing there might be something wrong with my syntax? And also is there something else I can use to make it faster??
Here's a sample of the formula I'm using:
ELookUp("SumOfActQuantity" ,"qryInvoi ceMasterQu erySummary A","[Mo]=' " & Format([txtMonth1],'mmm-yy ') & "'")
Here is the code for the ELookup
Here's a sample of the formula I'm using:
ELookUp("SumOfActQuantity"
Here is the code for the ELookup
Public Function ELookup(Expr As String, Domain As String, Optional Criteria As Variant, _
Optional OrderClause As Variant) As Variant
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.
' Delimited list for multi-value field.
'Author: Allen Browne. allen@allenbrowne.com
'Updated: December 2006, to handle multi-value fields (Access 2007 and later.)
'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 db As DAO.Database 'This database.
Dim rs As DAO.Recordset 'To retrieve the value to find.
Dim rsMVF As DAO.Recordset 'Child recordset to use for multi-value fields.
Dim varResult As Variant 'Return value for function.
Dim strSql As String 'SQL statement.
Dim strOut As String 'Output string to build up (multi-value field.)
Dim lngLen As Long 'Length of string.
Const strcSep = "," 'Separator between items in multi-value list.
'Initialize to null.
varResult = Null
'Build the SQL string.
strSql = "SELECT TOP 1 " & Expr & " FROM " & Domain
If Not IsMissing(Criteria) Then
strSql = strSql & " WHERE " & Criteria
End If
If Not IsMissing(OrderClause) Then
strSql = strSql & " ORDER BY " & OrderClause
End If
strSql = strSql & ";"
'Lookup the value.
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset(strSql, dbOpenForwardOnly)
If rs.RecordCount > 0 Then
'Will be an object if multi-value field.
If VarType(rs(0)) = vbObject Then
Set rsMVF = rs(0).Value
Do While Not rsMVF.EOF
If rs(0).Type = 101 Then 'dbAttachment
strOut = strOut & rsMVF!FileName & strcSep
Else
strOut = strOut & rsMVF![Value].Value & strcSep
End If
rsMVF.MoveNext
Loop
'Remove trailing separator.
lngLen = Len(strOut) - Len(strcSep)
If lngLen > 0& Then
varResult = Left(strOut, lngLen)
End If
Set rsMVF = Nothing
Else
'Not a multi-value field: just return the value.
varResult = rs(0)
End If
End If
rs.Close
'Assign the return value.
ELookup = varResult
Exit_ELookup:
Set rs = Nothing
Set db = Nothing
Exit Function
Err_ELookup:
MsgBox Err.Description, vbExclamation, "ELookup Error " & Err.number
Resume Exit_ELookup
End Function
Are all of your DLOOKUPs addressing fields in the same query or table? If so, why don't you bind the form to the query and just set the control source of the various controls on your form?
running multiple DLOOKUPs can be painfully slow, as can be running multiple ELOOKUPs. If you are capturing a bunch of information from this query to present on your form, you might consider opening the query as a recordset (do this only once), then fill in the textboxes from that recordset. If the record you are pulling from in the query varies for each textbox, then you can use the recordset.findfirst method to find the record you want to pull from. Because you are only opening the recordset once, this would be much quicker than multiple DLOOKUP or ELOOKUP calls.
Another way to address this if the form already has a recordsource, but you still have a number of other controls that are found in another table or query, you could create a subform, and link that subform to the main form based on some value that exists in each.
Another way to address this if the form already has a recordsource, but you still have a number of other controls that are found in another table or query, you could create a subform, and link that subform to the main form based on some value that exists in each.
ASKER
Yes all of my DLOOKUPs are addressing the same query. What I'm doing is collecting data for a rolling 24 months period based on a selection on my main form. When the selection is made it then looks up the value for each month going back 24 months. So it needs to perform 24 lookup functions which takes forever.
If I was to bind the form to the query where the actual detail is how would I sum up the quantity for each of the months? I would need to show "0" for months that had no data instead of just showing the totals for the months that have actual data in them. The query I am using the DLOOKUP from is a summary query based on my detail query. It sums up the totals for all the months and then I pull the lookups from that query. I tried to make it faster by creating this summary query but it still seems to take forever.
If I was to bind the form to the query where the actual detail is how would I sum up the quantity for each of the months? I would need to show "0" for months that had no data instead of just showing the totals for the months that have actual data in them. The query I am using the DLOOKUP from is a summary query based on my detail query. It sums up the totals for all the months and then I pull the lookups from that query. I tried to make it faster by creating this summary query but it still seems to take forever.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok I'll get a sample DB uploaded tonight. It's linked to my SQL DB so I have to create some dummy tables to show you. I agree it's easier to show you the DB itself. I'll have it uploaded tonight.
ASKER
This is becoming more difficult than I expected. Having a hard time getting a sample DB uploaded because it's linked to my SQL DB. I am working on it though.
understood.
ASKER
This solution worked for me. I was able to do what you said and it's working much better this way. Thank you for your help!
glad to help.