Log on Table... Help!!

[Sandspirit]

I am trying to capture the log on/off times for users. I have created a
History table with the fields autoid (autonumber), username, login
time, logout time. I have created a form that has the following VBA
for the Open property. It captures the CurrentUser and the Login time
as now(). The problem I am having is the Log out time. I want the
system to match up the record that has already been created from the
login and just add the Log out time without creating another record,
but to just fill in the Log out Field. I have tried the following
syntax at the Form_Close() propertybut it keeps giving me a syntax
error that the ";" is missing. I have tried putting the ";" literally
everywhere but to no avail.

The syntax for the Form_Close()

Private Sub Form_Close()
Dim sSQL As String
sSQL = "INSERT INTO LogHistory (LogOut) VALUES (now())"
sSQL = sSQL + " where (loghistory.username) = CurrentUser"
DBEngine(0)(0).Execute sSQL


End Sub

The Form_Open() syntax is..

Private Sub Form_Open(Cancel As Integer)
Dim sSQL As String
sSQL = "INSERT INTO LogHistory (UserName,LogIn) VALUES (""" &
CurrentUser & """, now())"
DBEngine(0)(0).Execute sSQL
End Sub



PLEASE HELP!

Thank you!

Sandspirit

 

[John Vinson]

I am trying to capture the log on/off times for users. I have created a
History table with the fields autoid (autonumber), username, login
time, logout time. I have created a form that has the following VBA
for the Open property. It captures the CurrentUser and the Login time
as now(). The problem I am having is the Log out time. I want the
system to match up the record that has already been created from the
login and just add the Log out time without creating another record,
but to just fill in the Log out Field. I have tried the following
syntax at the Form_Close() propertybut it keeps giving me a syntax
error that the ";" is missing. I have tried putting the ";" literally
everywhere but to no avail.

The syntax for the Form_Close()

Private Sub Form_Close()
Dim sSQL As String
sSQL = "INSERT INTO LogHistory (LogOut) VALUES (now())"
sSQL = sSQL + " where (loghistory.username) = CurrentUser"
DBEngine(0)(0).Execute sSQL

The main problem is that you're using an INSERT statement - which is
designed to create a *new record*. I'd suggest that you want an UPDATE
query instead, to update the existing record.

Another part of the problem is that you have CurrentUser *INSIDE* the
quotes, so it's trying to search for username equal to the literal
text string CurrentUser (rather than the value of that variable). You
also need quotemarks, and I'd suggest putting # date/time delimiters
around the time.

You also need to have criteria to find the *most recent* login; I
assume that the table might contain many records for each user.

Try changing this to:

sSQL = "UPDATE LogHistory SET Logout = (#" & now() & "#)" _
& " where (loghistory.username) = """ & CurrentUser & """" _
& " AND (loghistory.LogIn) = #" & _
& DMax("[LogIn]", "[loghistory]", _
"[Username] = """ & CurrentUser & """") & "#;"

The multiple " marks are required in order to insert a " within a
string constant delimited by ". If you go into debug mode, sSQL should
be something like

UPDATE LogHistory SET Logout = #03/10/2006 17:05:20# WHERE
(loghistory.username) = "JSMITH" AND (loghistory.LogIn) =
#03/10/2006 08:13:20#;

John W. Vinson[MVP]

John W. Vinson[MVP]

 

[Guest]

Hi there Sandspirit:

I'm an Access 2003 user and trying to come up with something like what you
have. I was wondering if you can email me the the table(s) (structures only.
tables with data too if you don't mind.) from your database. Thanks.

(e-mail address removed)

Chai

 

[Sandspirit]

thanks John, but I just get a syntax error when I use the code. I do
not know what is wrong. Please help.

This is exactly how I copied the information...

Private Sub Form_Close()

Dim sSQL As String
sSQL = "UPDATE LogHistory SET Logout = (#" & now() & "#)" _
& " where (loghistory.username) = """ & CurrentUser & """" _
& " AND (loghistory.LogIn) = #" & _
& DMax("[LogIn]", "[loghistory]", _
"[Username] = """ & CurrentUser & """") & "#;"
DBEngine(0)(0).Execute sSQL

End Sub

Thanks,

Sandspirit

 

[Sandspirit]

I got it. Thanks JOHN! There was a slight syntax error at the end of
the 3rd line. It worked like this:

Private Sub Form_Close()
Dim sSQL As String
sSQL = "UPDATE LogHistory SET Logout = (now())" _
& " where (loghistory.username) = """ & CurrentUser & """" _
& " AND (loghistory.LogIn) = #" _
& DMax("[LogIn]", "[loghistory]", _
"[Username] = """ & CurrentUser & """") & "#;"
DBEngine(0)(0).Execute sSQL

End Sub

I changed the Now () to not include the delimiters as well. Just a
personal thing. ;-) Thank you soooooooooooooo much for your help. I
still have a ways to go but that definitely saved my butt (for now).
Now I have to find a way to create a table that tracks all the updates
to these forms I have that have like 20 controls on them. I saw a
posting about version control but it was for like 3 years ago and when
I replied the author never wrote back. Oh well. ;-(

;-)))))))

Sandspirit

 

[Sandspirit]

Hi Chai,

I was not ignoring you, I just wanted to make sure I had things working
well B4 I replied to your request. I am unable to email that to you as
I am in a banking environment and all things are monitored. It might be
just the structure, but I do not want "anyone" to think I am sending
bank info to a 3rd party. Sowwy. But I have listed the fields below
and their formats.

Username (text)
AutoID (auto number) - key
LogIn (general date)
LogOut (general date)

Place the VBA code in a blank form that does not have a record source.
Paste the code as follows:

At the Form_Open()

Dim sSQL As String
sSQL = "INSERT INTO LogHistory (UserName,LogIn) VALUES (""" &
CurrentUser & """, now())"
DBEngine(0)(0).Execute sSQL
End Sub


At the Form_Close()

Dim sSQL As String
sSQL = "UPDATE LogHistory SET Logout = (now())" _
& " where (loghistory.username) = """ & CurrentUser & """" _
& " AND (loghistory.LogIn) = #" _
& DMax("[LogIn]", "[loghistory]", _
"[Username] = """ & CurrentUser & """") & "#;"
DBEngine(0)(0).Execute sSQL

End Sub


Save the form.

Now you need to place an event on the Main Switchboard/Open Page of
your database to open the form in hidden mode. Opening the form as you
know will allow the Username and Now() time to be placed in the table
automatically. FYI.. The reason the form is open in hidden mode is so
that no one can close it accidentally. Closing the form will place the
logoff information on the table when in actuality the user may still be
logged on.

At the Switchboard_Open()

DoCmd.openform "utilfrmLogHistory", acnormal, , , , acHidden


I hope this helps!

-Sandspirit