General discussion


MS Acess Dlookup, checkboxes and dates

By ecampos ·
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] & & [in]", "tblinyard", "[EmpNum]= '" & Me.Text3 & "'", "time desc")

If IsNull(Lkup) Then
Me.chkIn = True
Me.chkIn = Not (Me.chkIn)
End If
Me.chkOut = Not (Me.chkIn)

If Me.chkIn Then
counter = counter + 1
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

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.
'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")

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by john.a.wills In reply to MS Acess Dlookup, checkbo ...

I don't understand why there might be multiple records per person. Also, I suggest you get rid of ChkOut and use ChkIn alone for this indication.

Collapse -

by ecampos In reply to MS Acess Dlookup, checkbo ...

Multiple records to account for how many times and at what times the person passed thru the gate. If I use one box and mark in and out the same record replacing the previous time, then I cannot audit the activity thru the gate.

Related Discussions

Related Forums