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
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access 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
Closed Thread
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 15th, 2005, 07:13 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}"

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
  #2 (permalink)  
Old August 16th, 2005, 07:27 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
  #3 (permalink)  
Old August 16th, 2005, 07:29 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
  #4 (permalink)  
Old August 16th, 2005, 09:13 AM
Authorized User
 
Join Date: Aug 2005
Location: , , .
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

  #5 (permalink)  
Old August 16th, 2005, 09:51 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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.

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
Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
  #6 (permalink)  
Old August 16th, 2005, 09:55 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
  #7 (permalink)  
Old August 16th, 2005, 12:28 PM
Authorized User
 
Join Date: Aug 2005
Location: , , .
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

  #8 (permalink)  
Old August 16th, 2005, 12:35 PM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
  #9 (permalink)  
Old August 16th, 2005, 02:13 PM
Authorized User
 
Join Date: Aug 2005
Location: , , .
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

  #10 (permalink)  
Old August 16th, 2005, 03:08 PM
Authorized User
 
Join Date: Aug 2005
Location: , , .
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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)

Closed Thread


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 VBA 1 August 19th, 2005 01:51 PM



All times are GMT -4. The time now is 10:20 AM.


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