I'm trying to get the most recent time record from a table. I found Allen Browne's Elookup() function at
http://allenbrowne.com/ser-42.html and it works (it adds a fourth "sort" argument to the Dlookup function). However, I cannot get my code to do exacly what I want.
The database keeps track of people going in and out of a gate. When a person comes in through the gate, a check mark is placed on the "in" ckeck box when he leaves the gate that box is cleared and the "out" box is checked, when he goes through the gate again the "out" is unchecked and the "in" check box is marked again, and so on..... (there may be an unknown number of times when this occurs)
My code works for the first 2 records. After that all records are selected as yes.
Any help is greatly appreciated, I've been spinning my wheels on this one...
The underlying table fields are:
This is the code in the form:
Private Sub Text3_AfterUpdate()
Dim MsgBoxValue As Integer
Dim Lkup As Variant
Dim lkupTime As Variant
Lkup = ELookup("[EmpNum]& [time]&[in]", "tblinyard", "[EmpNum]='" & [Text3] & "'", "time desc")
On Error GoTo errorhandler
If IsNull(Lkup) Then
chkIn.Value = True
counter = counter + 1
lblcounter.Caption = counter
Label.Caption = Mid(Text3.Text, 1, 5) & "-" & Mid(Text3.Text, 6, 3) 'Text3.Text
SendKeys "{ENTER}"
Else
If chkIn.Value = True Then
chkIn.Value = False
chkOut.Value = True
counter = counter + 1
Else
chkIn.Value = False
chkOut.Value = True
counter = counter - 1
End If
lblcounter.Caption = counter
Label.Caption = Mid(Text3.Text, 1, 5) & "-" & Mid(Text3.Text, 6, 3) 'Text3.Text
SendKeys "{ENTER}"
Exit Sub
errorhandler:
'MsgBox "No Picture Found or Unreadable Card"
'MsgBox "Error number " & Err.Number & ": " & Err.Description
MsgBox Err.Description
Resume Next
End If
End Sub
******* ELookup Function:
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.
'Author: Allen Browne.
allen@allenbrowne.com
'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
Dim rs As DAO.Recordset
Dim strSql As String
'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
ELookup = Null
Else
ELookup = rs(0)
End If
rs.Close
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