Update Table with log out time

S

Steve Albert

I am using vb code to track user names and log in times with the following
code found in this forum:

Private Sub Form_Load()
Dim t As DAO.TableDef, texists As Boolean, Curr_Usr As String
texists = False
Curr_Usr = fOSUserName
For Each t In CurrentDb.TableDefs
If t.Name = "users_logged_in" Then
texists = True
Exit For
End If
Next t
Set t = Nothing
If Not texists Then
create_table
End If
CurrentDb.Execute "INSERT INTO users_logged_in ( [USER], TIME_IN )
VALUES('" & fOSUserName & "', '" & Now() & "')"
Me.TimerInterval = 100
DoCmd.OpenForm "Logon"
End Sub
Private Sub create_table()
Dim t As DAO.TableDef, db As DAO.Database, f1 As DAO.Field, f2 As
DAO.Field, f3 As DAO.Field

Set db = CurrentDb: Set t = New DAO.TableDef: Set f1 = New DAO.Field:
Set f2 = New DAO.Field: Set f3 = New DAO.Field

t.Name = "users_logged_in"

f1.Name = "USER"
f1.Type = dbText

f2.Name = "TIME_IN"
f2.Type = dbDate

f3.Name = "TIME_OUT"
f3.Type = dbDate

t.Fields.Append f1
t.Fields.Append f2
t.Fields.Append f3

db.TableDefs.Append t

Set f1 = Nothing: Set f2 = Nothing: Set f3 = Nothing: Set t = Nothing:
Set db = Nothing

'Application.SetHiddenAttribute acTable, "users_logged_in", True
Application.RefreshDatabaseWindow

End Sub
Private Function fOSUserName() As String 'Returns the network login name

Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If
End Function

Private Sub Form_Close()
CurrentDb.Execute "INSERT INTO users_logged_in ( [USER], TIME_OUT )
VALUES('" & fOSUserName & "', '" & Now() & "')"
End Sub

Private Sub Form_Timer()
Me.Visible = False
Me.TimerInterval = 0
End Sub
_________________
The problem is that the INSERT INTO Statement creates another row in the
table. I want it to find the last instance of "fOSUserName" in the table, and
put the "TIME_OUT" date/time in the SAME row with the user name and "TIME_IN"
date/time that's already there.

Thanks.

- Steve
 
J

John Spencer

Assumption:
There is only one record in the Users_Logged_In table for any user that has
a null Time_out field. .

Private Sub Form_Close()
CurrentDb.Execute "UPDATE users_logged_in " & _
" SET TIME_OUT = Now() " & _
" WHERE User =""" & FosUser() & """ AND Time_Out is Null"
End Sub

That might be written as
CurrentDb.Execute "UPDATE users_logged_in " & _
" SET TIME_OUT = Now() " & _
" WHERE User = FosUser() AND Time_Out is Null"

You could probably restrict the update further by only checking the Time_In
field for a value that is within 24 hours of the current date time
or looking for the record for the user with the latest time_in for that user
and only updating that record if it existed. See below for an idea of what
the query would look like
UPDATE Users_logged_IN
SET Time_Out = Now()
WHERE User = fOSUser
and Time_Out is Null
and Time_In =
(SELECT Max(tmp.Time_in)
FROM Users_Logged_In as Tmp
WHERE Tmp.User = Users_Logged_In.User)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Steve Albert said:
I am using vb code to track user names and log in times with the following
code found in this forum:

Private Sub Form_Load()
Dim t As DAO.TableDef, texists As Boolean, Curr_Usr As String
texists = False
Curr_Usr = fOSUserName
For Each t In CurrentDb.TableDefs
If t.Name = "users_logged_in" Then
texists = True
Exit For
End If
Next t
Set t = Nothing
If Not texists Then
create_table
End If
CurrentDb.Execute "INSERT INTO users_logged_in ( [USER], TIME_IN )
VALUES('" & fOSUserName & "', '" & Now() & "')"
Me.TimerInterval = 100
DoCmd.OpenForm "Logon"
End Sub
Private Sub create_table()
Dim t As DAO.TableDef, db As DAO.Database, f1 As DAO.Field, f2 As
DAO.Field, f3 As DAO.Field

Set db = CurrentDb: Set t = New DAO.TableDef: Set f1 = New DAO.Field:
Set f2 = New DAO.Field: Set f3 = New DAO.Field

t.Name = "users_logged_in"

f1.Name = "USER"
f1.Type = dbText

f2.Name = "TIME_IN"
f2.Type = dbDate

f3.Name = "TIME_OUT"
f3.Type = dbDate

t.Fields.Append f1
t.Fields.Append f2
t.Fields.Append f3

db.TableDefs.Append t

Set f1 = Nothing: Set f2 = Nothing: Set f3 = Nothing: Set t = Nothing:
Set db = Nothing

'Application.SetHiddenAttribute acTable, "users_logged_in", True
Application.RefreshDatabaseWindow

End Sub
Private Function fOSUserName() As String 'Returns the network login name

Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If
End Function

Private Sub Form_Close()
CurrentDb.Execute "INSERT INTO users_logged_in ( [USER], TIME_OUT )
VALUES('" & fOSUserName & "', '" & Now() & "')"
End Sub

Private Sub Form_Timer()
Me.Visible = False
Me.TimerInterval = 0
End Sub
_________________
The problem is that the INSERT INTO Statement creates another row in the
table. I want it to find the last instance of "fOSUserName" in the table,
and
put the "TIME_OUT" date/time in the SAME row with the user name and
"TIME_IN"
date/time that's already there.

Thanks.

- Steve
Click to expand...
 
S

Steve Albert

John,

Thanks for the quick reply. It works well. I tried using the Time_In =
(SELECT Max(tmp.Time_in) FROM Users_Logged_In as Tmp WHERE Tmp.User =
Users_Logged_In.User)

I get a run time error. How would you write out the "Where" statement?

Thanks.

- Steve


John Spencer said:
Assumption:
There is only one record in the Users_Logged_In table for any user that has
a null Time_out field. .

Private Sub Form_Close()
CurrentDb.Execute "UPDATE users_logged_in " & _
" SET TIME_OUT = Now() " & _
" WHERE User =""" & FosUser() & """ AND Time_Out is Null"
End Sub

That might be written as
CurrentDb.Execute "UPDATE users_logged_in " & _
" SET TIME_OUT = Now() " & _
" WHERE User = FosUser() AND Time_Out is Null"

You could probably restrict the update further by only checking the Time_In
field for a value that is within 24 hours of the current date time
or looking for the record for the user with the latest time_in for that user
and only updating that record if it existed. See below for an idea of what
the query would look like
UPDATE Users_logged_IN
SET Time_Out = Now()
WHERE User = fOSUser
and Time_Out is Null
and Time_In =
(SELECT Max(tmp.Time_in)
FROM Users_Logged_In as Tmp
WHERE Tmp.User = Users_Logged_In.User)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Steve Albert said:
I am using vb code to track user names and log in times with the following
code found in this forum:

Private Sub Form_Load()
Dim t As DAO.TableDef, texists As Boolean, Curr_Usr As String
texists = False
Curr_Usr = fOSUserName
For Each t In CurrentDb.TableDefs
If t.Name = "users_logged_in" Then
texists = True
Exit For
End If
Next t
Set t = Nothing
If Not texists Then
create_table
End If
CurrentDb.Execute "INSERT INTO users_logged_in ( [USER], TIME_IN )
VALUES('" & fOSUserName & "', '" & Now() & "')"
Me.TimerInterval = 100
DoCmd.OpenForm "Logon"
End Sub
Private Sub create_table()
Dim t As DAO.TableDef, db As DAO.Database, f1 As DAO.Field, f2 As
DAO.Field, f3 As DAO.Field

Set db = CurrentDb: Set t = New DAO.TableDef: Set f1 = New DAO.Field:
Set f2 = New DAO.Field: Set f3 = New DAO.Field

t.Name = "users_logged_in"

f1.Name = "USER"
f1.Type = dbText

f2.Name = "TIME_IN"
f2.Type = dbDate

f3.Name = "TIME_OUT"
f3.Type = dbDate

t.Fields.Append f1
t.Fields.Append f2
t.Fields.Append f3

db.TableDefs.Append t

Set f1 = Nothing: Set f2 = Nothing: Set f3 = Nothing: Set t = Nothing:
Set db = Nothing

'Application.SetHiddenAttribute acTable, "users_logged_in", True
Application.RefreshDatabaseWindow

End Sub
Private Function fOSUserName() As String 'Returns the network login name

Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If
End Function

Private Sub Form_Close()
CurrentDb.Execute "INSERT INTO users_logged_in ( [USER], TIME_OUT )
VALUES('" & fOSUserName & "', '" & Now() & "')"
End Sub

Private Sub Form_Timer()
Me.Visible = False
Me.TimerInterval = 0
End Sub
_________________
The problem is that the INSERT INTO Statement creates another row in the
table. I want it to find the last instance of "fOSUserName" in the table,
and
put the "TIME_OUT" date/time in the SAME row with the user name and
"TIME_IN"
date/time that's already there.

Thanks.

- Steve
Click to expand...
Click to expand...
 
J

John Spencer

Try the following instead. It could error if there is no record in the
database for fOsUser or if there was no record for the specific user with a
time in the Time_In field

UPDATE Users_logged_IN
SET Time_Out = Now()
WHERE User = fOSUser()
and Time_Out is Null
and Time_In =
(SELECT Max(tmp.Time_in)
FROM Users_Logged_In as Tmp
WHERE Tmp.User = fOSUser())



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Steve Albert said:
John,

Thanks for the quick reply. It works well. I tried using the Time_In =
(SELECT Max(tmp.Time_in) FROM Users_Logged_In as Tmp WHERE Tmp.User =
Users_Logged_In.User)

I get a run time error. How would you write out the "Where" statement?

Thanks.

- Steve


John Spencer said:
Assumption:
There is only one record in the Users_Logged_In table for any user that
has
a null Time_out field. .

Private Sub Form_Close()
CurrentDb.Execute "UPDATE users_logged_in " & _
" SET TIME_OUT = Now() " & _
" WHERE User =""" & FosUser() & """ AND Time_Out is Null"
End Sub

That might be written as
CurrentDb.Execute "UPDATE users_logged_in " & _
" SET TIME_OUT = Now() " & _
" WHERE User = FosUser() AND Time_Out is Null"

You could probably restrict the update further by only checking the
Time_In
field for a value that is within 24 hours of the current date time
or looking for the record for the user with the latest time_in for that
user
and only updating that record if it existed. See below for an idea of
what
the query would look like
UPDATE Users_logged_IN
SET Time_Out = Now()
WHERE User = fOSUser
and Time_Out is Null
and Time_In =
(SELECT Max(tmp.Time_in)
FROM Users_Logged_In as Tmp
WHERE Tmp.User = Users_Logged_In.User)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Steve Albert said:
I am using vb code to track user names and log in times with the
following
code found in this forum:

Private Sub Form_Load()
Dim t As DAO.TableDef, texists As Boolean, Curr_Usr As String
texists = False
Curr_Usr = fOSUserName
For Each t In CurrentDb.TableDefs
If t.Name = "users_logged_in" Then
texists = True
Exit For
End If
Next t
Set t = Nothing
If Not texists Then
create_table
End If
CurrentDb.Execute "INSERT INTO users_logged_in ( [USER], TIME_IN )
VALUES('" & fOSUserName & "', '" & Now() & "')"
Me.TimerInterval = 100
DoCmd.OpenForm "Logon"
End Sub
Private Sub create_table()
Dim t As DAO.TableDef, db As DAO.Database, f1 As DAO.Field, f2 As
DAO.Field, f3 As DAO.Field

Set db = CurrentDb: Set t = New DAO.TableDef: Set f1 = New
DAO.Field:
Set f2 = New DAO.Field: Set f3 = New DAO.Field

t.Name = "users_logged_in"

f1.Name = "USER"
f1.Type = dbText

f2.Name = "TIME_IN"
f2.Type = dbDate

f3.Name = "TIME_OUT"
f3.Type = dbDate

t.Fields.Append f1
t.Fields.Append f2
t.Fields.Append f3

db.TableDefs.Append t

Set f1 = Nothing: Set f2 = Nothing: Set f3 = Nothing: Set t =
Nothing:
Set db = Nothing

'Application.SetHiddenAttribute acTable, "users_logged_in", True
Application.RefreshDatabaseWindow

End Sub
Private Function fOSUserName() As String 'Returns the network login
name

Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If
End Function

Private Sub Form_Close()
CurrentDb.Execute "INSERT INTO users_logged_in ( [USER], TIME_OUT )
VALUES('" & fOSUserName & "', '" & Now() & "')"
End Sub

Private Sub Form_Timer()
Me.Visible = False
Me.TimerInterval = 0
End Sub
_________________
The problem is that the INSERT INTO Statement creates another row in
the
table. I want it to find the last instance of "fOSUserName" in the
table,
and
put the "TIME_OUT" date/time in the SAME row with the user name and
"TIME_IN"
date/time that's already there.

Thanks.

- Steve
Click to expand...
Click to expand...
Click to expand...
 
S

Steve Albert

Hmmm... Still get the same runtime error. Otherwise, it does what I want by
looking for a TIME_OUT that is Null.

Thanks.

- Steve

John Spencer said:
Try the following instead. It could error if there is no record in the
database for fOsUser or if there was no record for the specific user with a
time in the Time_In field

UPDATE Users_logged_IN
SET Time_Out = Now()
WHERE User = fOSUser()
and Time_Out is Null
and Time_In =
(SELECT Max(tmp.Time_in)
FROM Users_Logged_In as Tmp
WHERE Tmp.User = fOSUser())



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Steve Albert said:
John,

Thanks for the quick reply. It works well. I tried using the Time_In =
(SELECT Max(tmp.Time_in) FROM Users_Logged_In as Tmp WHERE Tmp.User =
Users_Logged_In.User)

I get a run time error. How would you write out the "Where" statement?

Thanks.

- Steve


John Spencer said:
Assumption:
There is only one record in the Users_Logged_In table for any user that
has
a null Time_out field. .

Private Sub Form_Close()
CurrentDb.Execute "UPDATE users_logged_in " & _
" SET TIME_OUT = Now() " & _
" WHERE User =""" & FosUser() & """ AND Time_Out is Null"
End Sub

That might be written as
CurrentDb.Execute "UPDATE users_logged_in " & _
" SET TIME_OUT = Now() " & _
" WHERE User = FosUser() AND Time_Out is Null"

You could probably restrict the update further by only checking the
Time_In
field for a value that is within 24 hours of the current date time
or looking for the record for the user with the latest time_in for that
user
and only updating that record if it existed. See below for an idea of
what
the query would look like
UPDATE Users_logged_IN
SET Time_Out = Now()
WHERE User = fOSUser
and Time_Out is Null
and Time_In =
(SELECT Max(tmp.Time_in)
FROM Users_Logged_In as Tmp
WHERE Tmp.User = Users_Logged_In.User)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I am using vb code to track user names and log in times with the
following
code found in this forum:

Private Sub Form_Load()
Dim t As DAO.TableDef, texists As Boolean, Curr_Usr As String
texists = False
Curr_Usr = fOSUserName
For Each t In CurrentDb.TableDefs
If t.Name = "users_logged_in" Then
texists = True
Exit For
End If
Next t
Set t = Nothing
If Not texists Then
create_table
End If
CurrentDb.Execute "INSERT INTO users_logged_in ( [USER], TIME_IN )
VALUES('" & fOSUserName & "', '" & Now() & "')"
Me.TimerInterval = 100
DoCmd.OpenForm "Logon"
End Sub
Private Sub create_table()
Dim t As DAO.TableDef, db As DAO.Database, f1 As DAO.Field, f2 As
DAO.Field, f3 As DAO.Field

Set db = CurrentDb: Set t = New DAO.TableDef: Set f1 = New
DAO.Field:
Set f2 = New DAO.Field: Set f3 = New DAO.Field

t.Name = "users_logged_in"

f1.Name = "USER"
f1.Type = dbText

f2.Name = "TIME_IN"
f2.Type = dbDate

f3.Name = "TIME_OUT"
f3.Type = dbDate

t.Fields.Append f1
t.Fields.Append f2
t.Fields.Append f3

db.TableDefs.Append t

Set f1 = Nothing: Set f2 = Nothing: Set f3 = Nothing: Set t =
Nothing:
Set db = Nothing

'Application.SetHiddenAttribute acTable, "users_logged_in", True
Application.RefreshDatabaseWindow

End Sub
Private Function fOSUserName() As String 'Returns the network login
name

Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If
End Function

Private Sub Form_Close()
CurrentDb.Execute "INSERT INTO users_logged_in ( [USER], TIME_OUT )
VALUES('" & fOSUserName & "', '" & Now() & "')"
End Sub

Private Sub Form_Timer()
Me.Visible = False
Me.TimerInterval = 0
End Sub
_________________
The problem is that the INSERT INTO Statement creates another row in
the
table. I want it to find the last instance of "fOSUserName" in the
table,
and
put the "TIME_OUT" date/time in the SAME row with the user name and
"TIME_IN"
date/time that's already there.

Thanks.

- Steve
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

What is the error? What does the actual code look like?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Steve Albert said:
Hmmm... Still get the same runtime error. Otherwise, it does what I want
by
looking for a TIME_OUT that is Null.

Thanks.

- Steve

John Spencer said:
Try the following instead. It could error if there is no record in the
database for fOsUser or if there was no record for the specific user with
a
time in the Time_In field

UPDATE Users_logged_IN
SET Time_Out = Now()
WHERE User = fOSUser()
and Time_Out is Null
and Time_In =
(SELECT Max(tmp.Time_in)
FROM Users_Logged_In as Tmp
WHERE Tmp.User = fOSUser())



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Steve Albert said:
John,

Thanks for the quick reply. It works well. I tried using the Time_In =
(SELECT Max(tmp.Time_in) FROM Users_Logged_In as Tmp WHERE Tmp.User =
Users_Logged_In.User)

I get a run time error. How would you write out the "Where" statement?

Thanks.

- Steve


:

Assumption:
There is only one record in the Users_Logged_In table for any user
that
has
a null Time_out field. .

Private Sub Form_Close()
CurrentDb.Execute "UPDATE users_logged_in " & _
" SET TIME_OUT = Now() " & _
" WHERE User =""" & FosUser() & """ AND Time_Out is Null"
End Sub

That might be written as
CurrentDb.Execute "UPDATE users_logged_in " & _
" SET TIME_OUT = Now() " & _
" WHERE User = FosUser() AND Time_Out is Null"

You could probably restrict the update further by only checking the
Time_In
field for a value that is within 24 hours of the current date time
or looking for the record for the user with the latest time_in for
that
user
and only updating that record if it existed. See below for an idea of
what
the query would look like
UPDATE Users_logged_IN
SET Time_Out = Now()
WHERE User = fOSUser
and Time_Out is Null
and Time_In =
(SELECT Max(tmp.Time_in)
FROM Users_Logged_In as Tmp
WHERE Tmp.User = Users_Logged_In.User)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

message
I am using vb code to track user names and log in times with the
following
code found in this forum:

Private Sub Form_Load()
Dim t As DAO.TableDef, texists As Boolean, Curr_Usr As String
texists = False
Curr_Usr = fOSUserName
For Each t In CurrentDb.TableDefs
If t.Name = "users_logged_in" Then
texists = True
Exit For
End If
Next t
Set t = Nothing
If Not texists Then
create_table
End If
CurrentDb.Execute "INSERT INTO users_logged_in ( [USER],
TIME_IN )
VALUES('" & fOSUserName & "', '" & Now() & "')"
Me.TimerInterval = 100
DoCmd.OpenForm "Logon"
End Sub
Private Sub create_table()
Dim t As DAO.TableDef, db As DAO.Database, f1 As DAO.Field, f2 As
DAO.Field, f3 As DAO.Field

Set db = CurrentDb: Set t = New DAO.TableDef: Set f1 = New
DAO.Field:
Set f2 = New DAO.Field: Set f3 = New DAO.Field

t.Name = "users_logged_in"

f1.Name = "USER"
f1.Type = dbText

f2.Name = "TIME_IN"
f2.Type = dbDate

f3.Name = "TIME_OUT"
f3.Type = dbDate

t.Fields.Append f1
t.Fields.Append f2
t.Fields.Append f3

db.TableDefs.Append t

Set f1 = Nothing: Set f2 = Nothing: Set f3 = Nothing: Set t =
Nothing:
Set db = Nothing

'Application.SetHiddenAttribute acTable, "users_logged_in", True
Application.RefreshDatabaseWindow

End Sub
Private Function fOSUserName() As String 'Returns the network login
name

Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If
End Function

Private Sub Form_Close()
CurrentDb.Execute "INSERT INTO users_logged_in ( [USER], TIME_OUT )
VALUES('" & fOSUserName & "', '" & Now() & "')"
End Sub

Private Sub Form_Timer()
Me.Visible = False
Me.TimerInterval = 0
End Sub
_________________
The problem is that the INSERT INTO Statement creates another row in
the
table. I want it to find the last instance of "fOSUserName" in the
table,
and
put the "TIME_OUT" date/time in the SAME row with the user name and
"TIME_IN"
date/time that's already there.

Thanks.

- Steve
Click to expand...
Click to expand...
Click to expand...
 
S

Steve Albert

John,

Here is the code, which produces a runtime error that says there's a syntx
error.

Private Sub Form_Close()

CurrentDb.Execute "UPDATE users_logged_in " & " SET TIME_OUT = Now() " & "
WHERE User =""" & fOSUserName() & """ AND Time_Out is Null" & " WHERE User
=""" & fOSUserName() & """ AND Time_Out is Null" & """ And Time_In = SELECT
Max(tmp.Time_in) FROM Users_Logged_In as Tmp WHERE Tmp.User =
Users_Logged_In.User"

End Sub

John Spencer said:
What is the error? What does the actual code look like?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Steve Albert said:
Hmmm... Still get the same runtime error. Otherwise, it does what I want
by
looking for a TIME_OUT that is Null.

Thanks.

- Steve

John Spencer said:
Try the following instead. It could error if there is no record in the
database for fOsUser or if there was no record for the specific user with
a
time in the Time_In field

UPDATE Users_logged_IN
SET Time_Out = Now()
WHERE User = fOSUser()
and Time_Out is Null
and Time_In =
(SELECT Max(tmp.Time_in)
FROM Users_Logged_In as Tmp
WHERE Tmp.User = fOSUser())



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John,

Thanks for the quick reply. It works well. I tried using the Time_In =
(SELECT Max(tmp.Time_in) FROM Users_Logged_In as Tmp WHERE Tmp.User =
Users_Logged_In.User)

I get a run time error. How would you write out the "Where" statement?

Thanks.

- Steve


:

Assumption:
There is only one record in the Users_Logged_In table for any user
that
has
a null Time_out field. .

Private Sub Form_Close()
CurrentDb.Execute "UPDATE users_logged_in " & _
" SET TIME_OUT = Now() " & _
" WHERE User =""" & FosUser() & """ AND Time_Out is Null"
End Sub

That might be written as
CurrentDb.Execute "UPDATE users_logged_in " & _
" SET TIME_OUT = Now() " & _
" WHERE User = FosUser() AND Time_Out is Null"

You could probably restrict the update further by only checking the
Time_In
field for a value that is within 24 hours of the current date time
or looking for the record for the user with the latest time_in for
that
user
and only updating that record if it existed. See below for an idea of
what
the query would look like
UPDATE Users_logged_IN
SET Time_Out = Now()
WHERE User = fOSUser
and Time_Out is Null
and Time_In =
(SELECT Max(tmp.Time_in)
FROM Users_Logged_In as Tmp
WHERE Tmp.User = Users_Logged_In.User)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

message
I am using vb code to track user names and log in times with the
following
code found in this forum:

Private Sub Form_Load()
Dim t As DAO.TableDef, texists As Boolean, Curr_Usr As String
texists = False
Curr_Usr = fOSUserName
For Each t In CurrentDb.TableDefs
If t.Name = "users_logged_in" Then
texists = True
Exit For
End If
Next t
Set t = Nothing
If Not texists Then
create_table
End If
CurrentDb.Execute "INSERT INTO users_logged_in ( [USER],
TIME_IN )
VALUES('" & fOSUserName & "', '" & Now() & "')"
Me.TimerInterval = 100
DoCmd.OpenForm "Logon"
End Sub
Private Sub create_table()
Dim t As DAO.TableDef, db As DAO.Database, f1 As DAO.Field, f2 As
DAO.Field, f3 As DAO.Field

Set db = CurrentDb: Set t = New DAO.TableDef: Set f1 = New
DAO.Field:
Set f2 = New DAO.Field: Set f3 = New DAO.Field

t.Name = "users_logged_in"

f1.Name = "USER"
f1.Type = dbText

f2.Name = "TIME_IN"
f2.Type = dbDate

f3.Name = "TIME_OUT"
f3.Type = dbDate

t.Fields.Append f1
t.Fields.Append f2
t.Fields.Append f3

db.TableDefs.Append t

Set f1 = Nothing: Set f2 = Nothing: Set f3 = Nothing: Set t =
Nothing:
Set db = Nothing

'Application.SetHiddenAttribute acTable, "users_logged_in", True
Application.RefreshDatabaseWindow

End Sub
Private Function fOSUserName() As String 'Returns the network login
name

Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If
End Function

Private Sub Form_Close()
CurrentDb.Execute "INSERT INTO users_logged_in ( [USER], TIME_OUT )
VALUES('" & fOSUserName & "', '" & Now() & "')"
End Sub

Private Sub Form_Timer()
Me.Visible = False
Me.TimerInterval = 0
End Sub
_________________
The problem is that the INSERT INTO Statement creates another row in
the
table. I want it to find the last instance of "fOSUserName" in the
table,
and
put the "TIME_OUT" date/time in the SAME row with the user name and
"TIME_IN"
date/time that's already there.

Thanks.

- Steve
Click to expand...
Click to expand...
Click to expand...
 
S

Steve Albert

Sorry John,

I gave you messed up code. Here is the code producing the syntax error:

CurrentDb.Execute "Update Users_Logged_In " & " Set Time_Out = Now()" WHERE
User =""" & fOSUserName() & """ And Time_Out is Null" & """ & And Time_In =
(SELECT Max(tmp.Time_in) FROM Users_Logged_In as Tmp WHERE Tmp.User =
fOSUserName())"

John Spencer said:
What is the error? What does the actual code look like?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Steve Albert said:
Hmmm... Still get the same runtime error. Otherwise, it does what I want
by
looking for a TIME_OUT that is Null.

Thanks.

- Steve

John Spencer said:
Try the following instead. It could error if there is no record in the
database for fOsUser or if there was no record for the specific user with
a
time in the Time_In field

UPDATE Users_logged_IN
SET Time_Out = Now()
WHERE User = fOSUser()
and Time_Out is Null
and Time_In =
(SELECT Max(tmp.Time_in)
FROM Users_Logged_In as Tmp
WHERE Tmp.User = fOSUser())



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John,

Thanks for the quick reply. It works well. I tried using the Time_In =
(SELECT Max(tmp.Time_in) FROM Users_Logged_In as Tmp WHERE Tmp.User =
Users_Logged_In.User)

I get a run time error. How would you write out the "Where" statement?

Thanks.

- Steve


:

Assumption:
There is only one record in the Users_Logged_In table for any user
that
has
a null Time_out field. .

Private Sub Form_Close()
CurrentDb.Execute "UPDATE users_logged_in " & _
" SET TIME_OUT = Now() " & _
" WHERE User =""" & FosUser() & """ AND Time_Out is Null"
End Sub

That might be written as
CurrentDb.Execute "UPDATE users_logged_in " & _
" SET TIME_OUT = Now() " & _
" WHERE User = FosUser() AND Time_Out is Null"

You could probably restrict the update further by only checking the
Time_In
field for a value that is within 24 hours of the current date time
or looking for the record for the user with the latest time_in for
that
user
and only updating that record if it existed. See below for an idea of
what
the query would look like
UPDATE Users_logged_IN
SET Time_Out = Now()
WHERE User = fOSUser
and Time_Out is Null
and Time_In =
(SELECT Max(tmp.Time_in)
FROM Users_Logged_In as Tmp
WHERE Tmp.User = Users_Logged_In.User)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

message
I am using vb code to track user names and log in times with the
following
code found in this forum:

Private Sub Form_Load()
Dim t As DAO.TableDef, texists As Boolean, Curr_Usr As String
texists = False
Curr_Usr = fOSUserName
For Each t In CurrentDb.TableDefs
If t.Name = "users_logged_in" Then
texists = True
Exit For
End If
Next t
Set t = Nothing
If Not texists Then
create_table
End If
CurrentDb.Execute "INSERT INTO users_logged_in ( [USER],
TIME_IN )
VALUES('" & fOSUserName & "', '" & Now() & "')"
Me.TimerInterval = 100
DoCmd.OpenForm "Logon"
End Sub
Private Sub create_table()
Dim t As DAO.TableDef, db As DAO.Database, f1 As DAO.Field, f2 As
DAO.Field, f3 As DAO.Field

Set db = CurrentDb: Set t = New DAO.TableDef: Set f1 = New
DAO.Field:
Set f2 = New DAO.Field: Set f3 = New DAO.Field

t.Name = "users_logged_in"

f1.Name = "USER"
f1.Type = dbText

f2.Name = "TIME_IN"
f2.Type = dbDate

f3.Name = "TIME_OUT"
f3.Type = dbDate

t.Fields.Append f1
t.Fields.Append f2
t.Fields.Append f3

db.TableDefs.Append t

Set f1 = Nothing: Set f2 = Nothing: Set f3 = Nothing: Set t =
Nothing:
Set db = Nothing

'Application.SetHiddenAttribute acTable, "users_logged_in", True
Application.RefreshDatabaseWindow

End Sub
Private Function fOSUserName() As String 'Returns the network login
name

Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If
End Function

Private Sub Form_Close()
CurrentDb.Execute "INSERT INTO users_logged_in ( [USER], TIME_OUT )
VALUES('" & fOSUserName & "', '" & Now() & "')"
End Sub

Private Sub Form_Timer()
Me.Visible = False
Me.TimerInterval = 0
End Sub
_________________
The problem is that the INSERT INTO Statement creates another row in
the
table. I want it to find the last instance of "fOSUserName" in the
table,
and
put the "TIME_OUT" date/time in the SAME row with the user name and
"TIME_IN"
date/time that's already there.

Thanks.

- Steve
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Try the following. What you posted definitely has syntax errors

Private Sub Form_Close()
Dim strSQL as String

strSQL = "UPDATE users_logged_in " & _
" SET TIME_OUT = Now() " & _
" WHERE User = fOSUserName() AND Time_Out is Null " & _
" And Time_In = ( SELECT Max(tmp.Time_in) " & _
" FROM Users_Logged_In as Tmp " & _
" WHERE Tmp.User = fOOSUserName )"

Debug.Print StrSQL ' this will print the sql string for troubleshooting
purposes
'Once things work you can delete this line

CurrentDb.Execute StrSQL, DbFailOnerror

End Sub

The string you had would evaluate to the following. Not the TWO WHERE
Clauses in the main query and note the absence of parentheses around the
subquery
UPDATE users_logged_in
SET TIME_OUT = Now()
WHERE User = fOSUserName() AND Time_Out is Null
WHERE User = fOSUserName() AND Time_Out is Null
And Time_In = SELECT Max(tmp.Time_in)
FROM Users_Logged_In as Tmp
WHERE Tmp.User = Users_Logged_In.User

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Steve Albert said:
John,

Here is the code, which produces a runtime error that says there's a syntx
error.

Private Sub Form_Close()

CurrentDb.Execute "UPDATE users_logged_in " & " SET TIME_OUT = Now() " &
"
WHERE User =""" & fOSUserName() & """ AND Time_Out is Null" & " WHERE
User
=""" & fOSUserName() & """ AND Time_Out is Null" & """ And Time_In =
SELECT
Max(tmp.Time_in) FROM Users_Logged_In as Tmp WHERE Tmp.User =
Users_Logged_In.User"

End Sub

John Spencer said:
What is the error? What does the actual code look like?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Steve Albert said:
Hmmm... Still get the same runtime error. Otherwise, it does what I
want
by
looking for a TIME_OUT that is Null.

Thanks.

- Steve

:

Try the following instead. It could error if there is no record in
the
database for fOsUser or if there was no record for the specific user
with
a
time in the Time_In field

UPDATE Users_logged_IN
SET Time_Out = Now()
WHERE User = fOSUser()
and Time_Out is Null
and Time_In =
(SELECT Max(tmp.Time_in)
FROM Users_Logged_In as Tmp
WHERE Tmp.User = fOSUser())



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

message
John,

Thanks for the quick reply. It works well. I tried using the Time_In
=
(SELECT Max(tmp.Time_in) FROM Users_Logged_In as Tmp WHERE Tmp.User
=
Users_Logged_In.User)

I get a run time error. How would you write out the "Where"
statement?

Thanks.

- Steve


:

Assumption:
There is only one record in the Users_Logged_In table for any user
that
has
a null Time_out field. .

Private Sub Form_Close()
CurrentDb.Execute "UPDATE users_logged_in " & _
" SET TIME_OUT = Now() " & _
" WHERE User =""" & FosUser() & """ AND Time_Out is
Null"
End Sub

That might be written as
CurrentDb.Execute "UPDATE users_logged_in " & _
" SET TIME_OUT = Now() " & _
" WHERE User = FosUser() AND Time_Out is Null"

You could probably restrict the update further by only checking the
Time_In
field for a value that is within 24 hours of the current date time
or looking for the record for the user with the latest time_in for
that
user
and only updating that record if it existed. See below for an idea
of
what
the query would look like
UPDATE Users_logged_IN
SET Time_Out = Now()
WHERE User = fOSUser
and Time_Out is Null
and Time_In =
(SELECT Max(tmp.Time_in)
FROM Users_Logged_In as Tmp
WHERE Tmp.User = Users_Logged_In.User)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

message
I am using vb code to track user names and log in times with the
following
code found in this forum:

Private Sub Form_Load()
Dim t As DAO.TableDef, texists As Boolean, Curr_Usr As String
texists = False
Curr_Usr = fOSUserName
For Each t In CurrentDb.TableDefs
If t.Name = "users_logged_in" Then
texists = True
Exit For
End If
Next t
Set t = Nothing
If Not texists Then
create_table
End If
CurrentDb.Execute "INSERT INTO users_logged_in ( [USER],
TIME_IN )
VALUES('" & fOSUserName & "', '" & Now() & "')"
Me.TimerInterval = 100
DoCmd.OpenForm "Logon"
End Sub
Private Sub create_table()
Dim t As DAO.TableDef, db As DAO.Database, f1 As DAO.Field, f2
As
DAO.Field, f3 As DAO.Field

Set db = CurrentDb: Set t = New DAO.TableDef: Set f1 = New
DAO.Field:
Set f2 = New DAO.Field: Set f3 = New DAO.Field

t.Name = "users_logged_in"

f1.Name = "USER"
f1.Type = dbText

f2.Name = "TIME_IN"
f2.Type = dbDate

f3.Name = "TIME_OUT"
f3.Type = dbDate

t.Fields.Append f1
t.Fields.Append f2
t.Fields.Append f3

db.TableDefs.Append t

Set f1 = Nothing: Set f2 = Nothing: Set f3 = Nothing: Set t =
Nothing:
Set db = Nothing

'Application.SetHiddenAttribute acTable, "users_logged_in",
True
Application.RefreshDatabaseWindow

End Sub
Private Function fOSUserName() As String 'Returns the network
login
name

Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If
End Function

Private Sub Form_Close()
CurrentDb.Execute "INSERT INTO users_logged_in ( [USER],
TIME_OUT )
VALUES('" & fOSUserName & "', '" & Now() & "')"
End Sub

Private Sub Form_Timer()
Me.Visible = False
Me.TimerInterval = 0
End Sub
_________________
The problem is that the INSERT INTO Statement creates another row
in
the
table. I want it to find the last instance of "fOSUserName" in
the
table,
and
put the "TIME_OUT" date/time in the SAME row with the user name
and
"TIME_IN"
date/time that's already there.

Thanks.

- Steve
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

That is messed up also. You can tell because you have 15 quote marks. I
think the one between Now() and Where needs to be eliminated.

"Update Users_Logged_In " & " Set Time_Out = Now()" WHERE
User =""" & fOSUserName() & """ And Time_Out is Null" & """ & And Time_In =
(SELECT Max(tmp.Time_in) FROM Users_Logged_In as Tmp WHERE Tmp.User =
fOSUserName())"



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Steve Albert said:
Sorry John,

I gave you messed up code. Here is the code producing the syntax error:

CurrentDb.Execute "Update Users_Logged_In " & " Set Time_Out = Now()"
WHERE
User =""" & fOSUserName() & """ And Time_Out is Null" & """ & And Time_In
=
(SELECT Max(tmp.Time_in) FROM Users_Logged_In as Tmp WHERE Tmp.User =
fOSUserName())"

John Spencer said:
What is the error? What does the actual code look like?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Steve Albert said:
Hmmm... Still get the same runtime error. Otherwise, it does what I
want
by
looking for a TIME_OUT that is Null.

Thanks.

- Steve

:

Try the following instead. It could error if there is no record in
the
database for fOsUser or if there was no record for the specific user
with
a
time in the Time_In field

UPDATE Users_logged_IN
SET Time_Out = Now()
WHERE User = fOSUser()
and Time_Out is Null
and Time_In =
(SELECT Max(tmp.Time_in)
FROM Users_Logged_In as Tmp
WHERE Tmp.User = fOSUser())



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

message
John,

Thanks for the quick reply. It works well. I tried using the Time_In
=
(SELECT Max(tmp.Time_in) FROM Users_Logged_In as Tmp WHERE Tmp.User
=
Users_Logged_In.User)

I get a run time error. How would you write out the "Where"
statement?

Thanks.

- Steve


:

Assumption:
There is only one record in the Users_Logged_In table for any user
that
has
a null Time_out field. .

Private Sub Form_Close()
CurrentDb.Execute "UPDATE users_logged_in " & _
" SET TIME_OUT = Now() " & _
" WHERE User =""" & FosUser() & """ AND Time_Out is
Null"
End Sub

That might be written as
CurrentDb.Execute "UPDATE users_logged_in " & _
" SET TIME_OUT = Now() " & _
" WHERE User = FosUser() AND Time_Out is Null"

You could probably restrict the update further by only checking the
Time_In
field for a value that is within 24 hours of the current date time
or looking for the record for the user with the latest time_in for
that
user
and only updating that record if it existed. See below for an idea
of
what
the query would look like
UPDATE Users_logged_IN
SET Time_Out = Now()
WHERE User = fOSUser
and Time_Out is Null
and Time_In =
(SELECT Max(tmp.Time_in)
FROM Users_Logged_In as Tmp
WHERE Tmp.User = Users_Logged_In.User)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

message
I am using vb code to track user names and log in times with the
following
code found in this forum:

Private Sub Form_Load()
Dim t As DAO.TableDef, texists As Boolean, Curr_Usr As String
texists = False
Curr_Usr = fOSUserName
For Each t In CurrentDb.TableDefs
If t.Name = "users_logged_in" Then
texists = True
Exit For
End If
Next t
Set t = Nothing
If Not texists Then
create_table
End If
CurrentDb.Execute "INSERT INTO users_logged_in ( [USER],
TIME_IN )
VALUES('" & fOSUserName & "', '" & Now() & "')"
Me.TimerInterval = 100
DoCmd.OpenForm "Logon"
End Sub
Private Sub create_table()
Dim t As DAO.TableDef, db As DAO.Database, f1 As DAO.Field, f2
As
DAO.Field, f3 As DAO.Field

Set db = CurrentDb: Set t = New DAO.TableDef: Set f1 = New
DAO.Field:
Set f2 = New DAO.Field: Set f3 = New DAO.Field

t.Name = "users_logged_in"

f1.Name = "USER"
f1.Type = dbText

f2.Name = "TIME_IN"
f2.Type = dbDate

f3.Name = "TIME_OUT"
f3.Type = dbDate

t.Fields.Append f1
t.Fields.Append f2
t.Fields.Append f3

db.TableDefs.Append t

Set f1 = Nothing: Set f2 = Nothing: Set f3 = Nothing: Set t =
Nothing:
Set db = Nothing

'Application.SetHiddenAttribute acTable, "users_logged_in",
True
Application.RefreshDatabaseWindow

End Sub
Private Function fOSUserName() As String 'Returns the network
login
name

Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If
End Function

Private Sub Form_Close()
CurrentDb.Execute "INSERT INTO users_logged_in ( [USER],
TIME_OUT )
VALUES('" & fOSUserName & "', '" & Now() & "')"
End Sub

Private Sub Form_Timer()
Me.Visible = False
Me.TimerInterval = 0
End Sub
_________________
The problem is that the INSERT INTO Statement creates another row
in
the
table. I want it to find the last instance of "fOSUserName" in
the
table,
and
put the "TIME_OUT" date/time in the SAME row with the user name
and
"TIME_IN"
date/time that's already there.

Thanks.

- Steve
Click to expand...
Click to expand...
Click to expand...
 
S

Steve Albert

Hi John,

I now get this error:

Undefined function 'fOSUserName' in expression. (Error 3085).

Here is what prints out from debug:
UPDATE users_logged_in SET TIME_OUT = Now() WHERE User = fOSUserName()
AND Time_Out is Null And Time_In = ( SELECT Max(tmp.Time_in) FROM
Users_Logged_In as Tmp WHERE Tmp.User = fOSUserName )

John Spencer said:
Try the following. What you posted definitely has syntax errors

Private Sub Form_Close()
Dim strSQL as String

strSQL = "UPDATE users_logged_in " & _
" SET TIME_OUT = Now() " & _
" WHERE User = fOSUserName() AND Time_Out is Null " & _
" And Time_In = ( SELECT Max(tmp.Time_in) " & _
" FROM Users_Logged_In as Tmp " & _
" WHERE Tmp.User = fOOSUserName )"

Debug.Print StrSQL ' this will print the sql string for troubleshooting
purposes
'Once things work you can delete this line

CurrentDb.Execute StrSQL, DbFailOnerror

End Sub

The string you had would evaluate to the following. Not the TWO WHERE
Clauses in the main query and note the absence of parentheses around the
subquery
UPDATE users_logged_in
SET TIME_OUT = Now()
WHERE User = fOSUserName() AND Time_Out is Null
WHERE User = fOSUserName() AND Time_Out is Null
And Time_In = SELECT Max(tmp.Time_in)
FROM Users_Logged_In as Tmp
WHERE Tmp.User = Users_Logged_In.User

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Steve Albert said:
John,

Here is the code, which produces a runtime error that says there's a syntx
error.

Private Sub Form_Close()

CurrentDb.Execute "UPDATE users_logged_in " & " SET TIME_OUT = Now() " &
"
WHERE User =""" & fOSUserName() & """ AND Time_Out is Null" & " WHERE
User
=""" & fOSUserName() & """ AND Time_Out is Null" & """ And Time_In =
SELECT
Max(tmp.Time_in) FROM Users_Logged_In as Tmp WHERE Tmp.User =
Users_Logged_In.User"

End Sub

John Spencer said:
What is the error? What does the actual code look like?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hmmm... Still get the same runtime error. Otherwise, it does what I
want
by
looking for a TIME_OUT that is Null.

Thanks.

- Steve

:

Try the following instead. It could error if there is no record in
the
database for fOsUser or if there was no record for the specific user
with
a
time in the Time_In field

UPDATE Users_logged_IN
SET Time_Out = Now()
WHERE User = fOSUser()
and Time_Out is Null
and Time_In =
(SELECT Max(tmp.Time_in)
FROM Users_Logged_In as Tmp
WHERE Tmp.User = fOSUser())



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

message
John,

Thanks for the quick reply. It works well. I tried using the Time_In
=
(SELECT Max(tmp.Time_in) FROM Users_Logged_In as Tmp WHERE Tmp.User
=
Users_Logged_In.User)

I get a run time error. How would you write out the "Where"
statement?

Thanks.

- Steve


:

Assumption:
There is only one record in the Users_Logged_In table for any user
that
has
a null Time_out field. .

Private Sub Form_Close()
CurrentDb.Execute "UPDATE users_logged_in " & _
" SET TIME_OUT = Now() " & _
" WHERE User =""" & FosUser() & """ AND Time_Out is
Null"
End Sub

That might be written as
CurrentDb.Execute "UPDATE users_logged_in " & _
" SET TIME_OUT = Now() " & _
" WHERE User = FosUser() AND Time_Out is Null"

You could probably restrict the update further by only checking the
Time_In
field for a value that is within 24 hours of the current date time
or looking for the record for the user with the latest time_in for
that
user
and only updating that record if it existed. See below for an idea
of
what
the query would look like
UPDATE Users_logged_IN
SET Time_Out = Now()
WHERE User = fOSUser
and Time_Out is Null
and Time_In =
(SELECT Max(tmp.Time_in)
FROM Users_Logged_In as Tmp
WHERE Tmp.User = Users_Logged_In.User)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

message
I am using vb code to track user names and log in times with the
following
code found in this forum:

Private Sub Form_Load()
Dim t As DAO.TableDef, texists As Boolean, Curr_Usr As String
texists = False
Curr_Usr = fOSUserName
For Each t In CurrentDb.TableDefs
If t.Name = "users_logged_in" Then
texists = True
Exit For
End If
Next t
Set t = Nothing
If Not texists Then
create_table
End If
CurrentDb.Execute "INSERT INTO users_logged_in ( [USER],
TIME_IN )
VALUES('" & fOSUserName & "', '" & Now() & "')"
Me.TimerInterval = 100
DoCmd.OpenForm "Logon"
End Sub
Private Sub create_table()
Dim t As DAO.TableDef, db As DAO.Database, f1 As DAO.Field, f2
As
DAO.Field, f3 As DAO.Field

Set db = CurrentDb: Set t = New DAO.TableDef: Set f1 = New
DAO.Field:
Set f2 = New DAO.Field: Set f3 = New DAO.Field

t.Name = "users_logged_in"

f1.Name = "USER"
f1.Type = dbText

f2.Name = "TIME_IN"
f2.Type = dbDate

f3.Name = "TIME_OUT"
f3.Type = dbDate

t.Fields.Append f1
t.Fields.Append f2
t.Fields.Append f3

db.TableDefs.Append t

Set f1 = Nothing: Set f2 = Nothing: Set f3 = Nothing: Set t =
Nothing:
Set db = Nothing

'Application.SetHiddenAttribute acTable, "users_logged_in",
True
Application.RefreshDatabaseWindow

End Sub
Private Function fOSUserName() As String 'Returns the network
login
name

Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If
End Function

Private Sub Form_Close()
CurrentDb.Execute "INSERT INTO users_logged_in ( [USER],
TIME_OUT )
VALUES('" & fOSUserName & "', '" & Now() & "')"
End Sub

Private Sub Form_Timer()
Me.Visible = False
Me.TimerInterval = 0
End Sub
_________________
The problem is that the INSERT INTO Statement creates another row
in
the
table. I want it to find the last instance of "fOSUserName" in
the
table,
and
put the "TIME_OUT" date/time in the SAME row with the user name
and
"TIME_IN"
date/time that's already there.

Thanks.

- Steve
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

What did you name the module in which you saved the code for fOSUserName? If
you named the module fOSUserName, rename it: modules cannot be named the
same as functions or subs.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Steve Albert said:
Hi John,

I now get this error:

Undefined function 'fOSUserName' in expression. (Error 3085).

Here is what prints out from debug:
UPDATE users_logged_in SET TIME_OUT = Now() WHERE User = fOSUserName()
AND Time_Out is Null And Time_In = ( SELECT Max(tmp.Time_in) FROM
Users_Logged_In as Tmp WHERE Tmp.User = fOSUserName )

John Spencer said:
Try the following. What you posted definitely has syntax errors

Private Sub Form_Close()
Dim strSQL as String

strSQL = "UPDATE users_logged_in " & _
" SET TIME_OUT = Now() " & _
" WHERE User = fOSUserName() AND Time_Out is Null " & _
" And Time_In = ( SELECT Max(tmp.Time_in) " & _
" FROM Users_Logged_In as Tmp " & _
" WHERE Tmp.User = fOOSUserName )"

Debug.Print StrSQL ' this will print the sql string for troubleshooting
purposes
'Once things work you can delete this line

CurrentDb.Execute StrSQL, DbFailOnerror

End Sub

The string you had would evaluate to the following. Not the TWO WHERE
Clauses in the main query and note the absence of parentheses around the
subquery
UPDATE users_logged_in
SET TIME_OUT = Now()
WHERE User = fOSUserName() AND Time_Out is Null
WHERE User = fOSUserName() AND Time_Out is Null
And Time_In = SELECT Max(tmp.Time_in)
FROM Users_Logged_In as Tmp
WHERE Tmp.User = Users_Logged_In.User

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Steve Albert said:
John,

Here is the code, which produces a runtime error that says there's a
syntx
error.

Private Sub Form_Close()

CurrentDb.Execute "UPDATE users_logged_in " & " SET TIME_OUT = Now() "
&
"
WHERE User =""" & fOSUserName() & """ AND Time_Out is Null" & " WHERE
User
=""" & fOSUserName() & """ AND Time_Out is Null" & """ And Time_In =
SELECT
Max(tmp.Time_in) FROM Users_Logged_In as Tmp WHERE Tmp.User =
Users_Logged_In.User"

End Sub

:

What is the error? What does the actual code look like?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

message
Hmmm... Still get the same runtime error. Otherwise, it does what I
want
by
looking for a TIME_OUT that is Null.

Thanks.

- Steve

:

Try the following instead. It could error if there is no record in
the
database for fOsUser or if there was no record for the specific
user
with
a
time in the Time_In field

UPDATE Users_logged_IN
SET Time_Out = Now()
WHERE User = fOSUser()
and Time_Out is Null
and Time_In =
(SELECT Max(tmp.Time_in)
FROM Users_Logged_In as Tmp
WHERE Tmp.User = fOSUser())



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

message
John,

Thanks for the quick reply. It works well. I tried using the
Time_In
=
(SELECT Max(tmp.Time_in) FROM Users_Logged_In as Tmp WHERE
Tmp.User
=
Users_Logged_In.User)

I get a run time error. How would you write out the "Where"
statement?

Thanks.

- Steve


:

Assumption:
There is only one record in the Users_Logged_In table for any
user
that
has
a null Time_out field. .

Private Sub Form_Close()
CurrentDb.Execute "UPDATE users_logged_in " & _
" SET TIME_OUT = Now() " & _
" WHERE User =""" & FosUser() & """ AND Time_Out is
Null"
End Sub

That might be written as
CurrentDb.Execute "UPDATE users_logged_in " & _
" SET TIME_OUT = Now() " & _
" WHERE User = FosUser() AND Time_Out is Null"

You could probably restrict the update further by only checking
the
Time_In
field for a value that is within 24 hours of the current date
time
or looking for the record for the user with the latest time_in
for
that
user
and only updating that record if it existed. See below for an
idea
of
what
the query would look like
UPDATE Users_logged_IN
SET Time_Out = Now()
WHERE User = fOSUser
and Time_Out is Null
and Time_In =
(SELECT Max(tmp.Time_in)
FROM Users_Logged_In as Tmp
WHERE Tmp.User = Users_Logged_In.User)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

message
I am using vb code to track user names and log in times with
the
following
code found in this forum:

Private Sub Form_Load()
Dim t As DAO.TableDef, texists As Boolean, Curr_Usr As
String
texists = False
Curr_Usr = fOSUserName
For Each t In CurrentDb.TableDefs
If t.Name = "users_logged_in" Then
texists = True
Exit For
End If
Next t
Set t = Nothing
If Not texists Then
create_table
End If
CurrentDb.Execute "INSERT INTO users_logged_in ( [USER],
TIME_IN )
VALUES('" & fOSUserName & "', '" & Now() & "')"
Me.TimerInterval = 100
DoCmd.OpenForm "Logon"
End Sub
Private Sub create_table()
Dim t As DAO.TableDef, db As DAO.Database, f1 As DAO.Field,
f2
As
DAO.Field, f3 As DAO.Field

Set db = CurrentDb: Set t = New DAO.TableDef: Set f1 = New
DAO.Field:
Set f2 = New DAO.Field: Set f3 = New DAO.Field

t.Name = "users_logged_in"

f1.Name = "USER"
f1.Type = dbText

f2.Name = "TIME_IN"
f2.Type = dbDate

f3.Name = "TIME_OUT"
f3.Type = dbDate

t.Fields.Append f1
t.Fields.Append f2
t.Fields.Append f3

db.TableDefs.Append t

Set f1 = Nothing: Set f2 = Nothing: Set f3 = Nothing: Set t
=
Nothing:
Set db = Nothing

'Application.SetHiddenAttribute acTable, "users_logged_in",
True
Application.RefreshDatabaseWindow

End Sub
Private Function fOSUserName() As String 'Returns the network
login
name

Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If
End Function

Private Sub Form_Close()
CurrentDb.Execute "INSERT INTO users_logged_in ( [USER],
TIME_OUT )
VALUES('" & fOSUserName & "', '" & Now() & "')"
End Sub

Private Sub Form_Timer()
Me.Visible = False
Me.TimerInterval = 0
End Sub
_________________
The problem is that the INSERT INTO Statement creates another
row
in
the
table. I want it to find the last instance of "fOSUserName" in
the
table,
and
put the "TIME_OUT" date/time in the SAME row with the user
name
and
"TIME_IN"
date/time that's already there.

Thanks.

- Steve
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to Call a Function 4
Update form field 2
Module not doing anything 1
Using code from Dev Ashish to get userid on updated records. 2
Update username 6
Retrieving and setting user logon name in a form 2
macro help 2
retreving the network login name in a text box 3

Top