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

Hmmm... there's lots here that I think is not necessary. Let me address the code first. You have this structure:

If IsNull(Lkup) Then
    Some code here
Else
    If chkIn.Value = True Then
        Some code here
    Else
        Some code here
    End If

lblcounter.Caption = counter

Notice something? Where's the last "End IF"?

Second, you really don't need all that. If you want a simple IN/OUT then you can just use an option group with two option buttons. With option groups, if you select IN, OUT will automatically unselect... and vice-versa. You only have to keep track of the option group value, not two checkboxes.

If Me.grpInOut = 1 Then
    Me.grpInOut = 2
Else
    Me.grpInOut = 1
End If


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Oh, that ELookUp looks interesting but for some things, a simple DMin or DMax function will work just the same.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Thanks for the advice. The last end if is at the very end of the code below the error handler statemnet, the code compiles without any error "else without end if", I must not have pasted it. The option group couldn't work because I don't want any user interaction, the imput comes from a barcode scanner and the pc will be unattended most of the time. Could your example work in this scenario? I'll test the DMax also. Again Thank you for your time and advise.

Ah, you're getting a reading from a barcode. I didn't know that. Your code will certainly work. Here it is cleaned up a bit. You also needed to declare "counter". I don't know how may records you have so I declared it as long integer. I didn't see the variable MsgBoxValue used anywhere so I took it out of the DIM section.

You also seemed to have a lot of redundancy which I cut out. See if I interpreted it correctly.

Rule of Thumb: put the On Error line at the very top.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

I suggest you view my last reply in the web forum to view the proper indentations, etc. The e-mail generated by my reply may not do it justice.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Thanks again. I pasted your code directly in the form under the afterupdate section, and started with an empty table. I'm getting the same end result, all inputs are marked as "in" as if it is ignoring some part of the if condition.

Two thoughts:

If the table is empty, what do you want the chkIn to be as default? Select a default (True or False) in your table. If the default was set to True when you created the table, it could be the cause.

Check to make sure the ELookUp constituents are correct and typed exactly as you need it, especially the first string.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Well, when the table is empty then the "IsNull" is true and the chkIn value is set to true in the first part of the If statement. The default values for both chk boxes are blank, I did not select assigned values. I double checked all arguments of the Dlookup statement, and all constitute to the table fields....Do you think I should assign a starting default value?

Thanks again

When I place a break in the code and hover the mouse over the lkup, it shows the right number typed in the text box but also shows a -1 besides it, the immediate window also shows the number and -1 (example: 1234 -1)