Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old August 15th, 2005, 08:09 PM
Authorized User
Join Date: Aug 2005
Location: , , .
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default Dlookup with If ..Then ..Else and Chkboxes

 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}"

If chkIn.Value = True Then
chkIn.Value = False
chkOut.Value = True
counter = counter + 1


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
'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
    '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
        ELookup = rs(0)
    End If

    Set rs = Nothing
    Set db = Nothing
    Exit Function

    MsgBox Err.Description, vbExclamation, "ELookup Error " & Err.Number
    Resume Exit_ELookup
    End Function
  #2 (permalink)  
Old August 19th, 2005, 01:51 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

If you are looking for one person, or just the latest person, just create this query, using Top 1. Then pass the person parameter to it if you are looking for one person, or take the Top 1 without the person parameter if you are looking for the most recent person. If you want to have only those that are In, then the criteria should be Yes or -1, etc.

Is this what you are after?



Similar Threads
Thread Thread Starter Forum Replies Last Post
dlookup problem alemok Access 0 April 17th, 2008 02:50 AM
Problem with Dlookup JAdkins Access VBA 1 January 11th, 2008 06:52 PM
Help with DLookup Devilboy13 Access VBA 4 August 30th, 2006 02:48 PM
DLookup Teqlump Access VBA 1 August 9th, 2006 11:48 PM
Dlookup with If ..Then ..Else and Chkboxes ecampos Access 16 August 18th, 2005 08:08 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.