Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #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
Reply With Quote
  #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?


Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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

All times are GMT -4. The time now is 03:19 PM.

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