"iholder" <(E-Mail Removed)> wrote in message
news:51AD39B5-B339-42B1-AEF6-(E-Mail Removed)
> I need some help on setup up a syntax string for finding UserID and
> Log date and blank Time field.
>
> Here is my code
>
> Dim rs As DAO.Recordset
> Dim strLogDate As Date
> Dim strFind As String
> strLogDate = Date
> strUserId = Me.UserID
> strFind = "UserId = '" & strUserId & "'" {need the correct date
> and null string added here}
>
> 'open login table
> Set db = CurrentDb
> Set rs = db.OpenRecordset("tblLogIntracking", dbOpenDynaset)
> With rs
> .MoveLast
> 'find login record
> .FindFirst strFind
> If .NoMatch Then
> MsgBox "No records found"
> Exit Function
> Else
> MsgBox "Records found"
> End If
> 'add log time
> .Edit
> !LogOutTime = Time
> .Update
> End With
>
> rs.Close: Set rs = Nothing
> Set db = Nothing
>
>
> thank you
> ih
I'm not sure what you're asking with regard to the time field, and you
don't say what field in the table holds the date. Do you have a
LogInDate and a LogOutDate? A LogInTime and a LogOutTime? I'm going to
guess that you have just one date, LogDate, and two times, LogInTime and
LogOutTime, and that you want to update the record that has the current
date as the LogDate and has Null as the LogOutTime. In that case, your
code could be corrected as follows:
'----- start of revised code #1 -----
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strLogDate As String '*** NOTE CHANGE ***
Dim strFind As String
strLogDate = Format(Date, "\#mm/dd/yyyy\#")
strUserId = Me.UserID
strFind = _
"UserId = '" & strUserId & "'" & _
" AND LogDate = " & strLogDate & _
" AND LogOutTime Is Null"
'open login table
Set db = CurrentDb
Set rs = db.OpenRecordset("tblLogIntracking", dbOpenDynaset)
With rs
'find login record
.FindFirst strFind
If .NoMatch Then
MsgBox "No records found"
Else
MsgBox "Records found"
'add log time
.Edit
!LogOutTime = Time
.Update
End If
.Close
End With
Set rs = Nothing
Set db = Nothing
'----- end of revised code #1 -----
However, this would be more efficient:
'----- start of revised code #2 -----
Dim db As DAO.Database
Dim strLogDate As String
Dim strSQL As String
strLogDate = Format(Date, "\#mm/dd/yyyy\#")
strUserId = Me.UserID
strSQL = _
"UPDATE tblLogInTracking SET LogOutTime = Time() " & _
"WHERE UserId = '" & strUserId & "'" & _
" AND LogDate = " & strLogDate & _
" AND LogOutTime Is Null"
Set db = CurrentDb
With db
.Execute strSQL, dbFailOnError
If .RecordsAffected = 0 Then
MsgBox "No records found"
Else
MsgBox "Records found and updated"
End If
End With
Set db = Nothing
'----- end of revised code #2 -----
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
Similar Threads
