I’ve created a MS access database to keep track of traffic thru a gate using a scanner. If a person comes in a chk box is marked, if out the “In” box is unchecked and the “out” box checked and so on back and forth. I also want to maintain a tally of number of people in. Sounds very simple….The problem is when there are multiple records of the same person. The code marks all “in” boxes, a part of the If statement is ignored.
Thanks for all the help in advance.
**************Form Code:
Private Sub Text3_AfterUpdate()
On Error GoTo errorhandler
Dim Lkup As Variant
Dim lkupTime As Variant
Dim counter as Long
Lkup = ELookup(“[EmpNum] & [time] & [in]”, “tblinyard”, “[EmpNum]= ‘” & Me.Text3 & “‘”, “time desc”)
If IsNull(Lkup) Then
Me.chkIn = True
Else
Me.chkIn = Not (Me.chkIn)
End If
Me.chkOut = Not (Me.chkIn)
If Me.chkIn Then
counter = counter + 1
Else
counter = counter – 1
End If
Me.lblcounter.Caption = counter
Me.Label.Caption = Mid(Me.Text3, 1, 5) & “-” & Mid(Me.Text3, 6, 3)
SendKeys “{ENTER}”
Exit Sub
errorhandler:
MsgBox Err.Description, vbCritical, “Text3_AfterUpdate Error ” & Err.Number
Resume Next
End Sub
******* ELookup Function: (WILL BE TRUNCATED!!!!in this post)
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”)