Trouble detecting if record exists

[serviceman via AccessMonster.com]

Hi All!
I'm trying to create a time clock screen where a user can clock in and out.
What I would like to have happen is this: If a user goes to clock in, and
there is no record for this date, then show the TimeIn field. If there IS a
record, then show the TimeOut field. the screen has a combo box where the
user selectd their ID, and the after update event has the following code:
Dim db As Database
Dim rs As DAO.Recordset
Dim sSQL As String


sSQL = "SELECT * FROM TimeClock WHERE EMP_ID = " & Me.cboEmpl & "
AND WorkDate = #" & Me.WorkDate & "#"
'Check the database
Set db = CurrentDb


Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
If Len(TimeOut) < 0 And Len(TimeIn) > 0 Then

Me.TimeIn.Visible = False
Me.TimeOut.Visible = True
Else
Me.TimeOut.Visible = False
Me.TimeIn.Visible = True

End If

rs.Close
Set rs = Nothing
Set db = Nothing
The problem is in the "If Len(TimeOut) < 0 And Len(TimeIn) > 0" portion; how
do I reference those fields in the recordset?
TIA,
Andy

 

[Jim Burke in Novi]

I would just use a DLookup instead of creating a recordset:

sSQL = ""EMP_ID = " & Me.cboEmpl & " AND WorkDate = #" & Me.WorkDate & "#"

If IsNUll(DLookup("TimeIn","TimeCLock", sSQL) then
Me.TimeOut.Visible = False
Me.TimeIn.Visible = True
Else
Me.TimeOut.Visible = True
Me.TimeIn.Visible = False
End If

In this case it's not really SQL, it's a WHERE clause without the word
WHERE, which is what DLookup uses.

Realistically you should always check for all possible conditions - what
if there's already a timein and a timeout, etc. Also, check to make sure they
selected a date and an employee ID, otherwise you could get an error. You
should also add error handling in the sub, i.e. On Error GoTo ... in case an
error ever does occur for whatever reason.