Hi.
I use Allen Browns Elookup instead of the dlookup. This gives me speed, and it works well, but it returns an annoying error.
I have a continous form with financial holdings.
To add a new record I simply press a combobox (name of box is prodID) and choose from a huge list. When I pick the correct holding, the elookup function returns the latest price. This works, but first I get an annoying error message.
When I click on this combo and choose a holding, I get the error;
"Elookup Error 3075", and Syntax error (missing operator) in query expresseion 'prodID='.
If i press ESC, or just OK on the error, everything works and i can continue with the record and the latest price is automatically added. After I click ok or ESC, I can change the contents of the combo as I please without any errors.
I think it happens because the elookup function is "volatile", and fires before the new holding has a primary key assigned. I use normal access forms, so the primary key (autonumber) is set when the first field is filled out.
When I press "OK" on the error, I assume that my dataentry is saved in the table, and then the elookup finds the correct price.
So the function works, but with an annoying error.
Further explanation on how this "looks";
I go to a new record. I click the combobox which expands to a long list. I find the correct one, and exactly when i press the correct one, the error comes. I then click "ok", and everything works.
There is no events in the combobox, no code, no nothing besides a standard msaccess control.
The Elookup code is not in the form itself, but in a separate module.
The elookup is done in the underlying query, and are based on the data in the query.
Here's the original code from Allen Browne (I havent changed anything):
I use Allen Browns Elookup instead of the dlookup. This gives me speed, and it works well, but it returns an annoying error.
I have a continous form with financial holdings.
To add a new record I simply press a combobox (name of box is prodID) and choose from a huge list. When I pick the correct holding, the elookup function returns the latest price. This works, but first I get an annoying error message.
When I click on this combo and choose a holding, I get the error;
"Elookup Error 3075", and Syntax error (missing operator) in query expresseion 'prodID='.
If i press ESC, or just OK on the error, everything works and i can continue with the record and the latest price is automatically added. After I click ok or ESC, I can change the contents of the combo as I please without any errors.
I think it happens because the elookup function is "volatile", and fires before the new holding has a primary key assigned. I use normal access forms, so the primary key (autonumber) is set when the first field is filled out.
When I press "OK" on the error, I assume that my dataentry is saved in the table, and then the elookup finds the correct price.
So the function works, but with an annoying error.
Further explanation on how this "looks";
I go to a new record. I click the combobox which expands to a long list. I find the correct one, and exactly when i press the correct one, the error comes. I then click "ok", and everything works.
There is no events in the combobox, no code, no nothing besides a standard msaccess control.
The Elookup code is not in the form itself, but in a separate module.
The elookup is done in the underlying query, and are based on the data in the query.
Here's the original code from Allen Browne (I havent changed anything):
Code:
Option Compare Database
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
