Solved: Track logged in users - Access 2003

[Duncs]

I posted for some help on a related problem last week, but I've now got something else I need to do.

I've created a table tblUsers with the following fields:

ID - Autonumber
ComputerID - Text
LogonID - Text
DateIn - Date/Time
DateOut - Date/Time

What I need to do is when a user opens the database, insert into the table their ComputerID & LogonID as well as the date/time that they opened the database. Then, when they are finished, it should update the appropriate record with the date/time they closed the database.

In the form open event, I've put the following:

Dim strSQL as String

strSQL = "INSERT INTO tblUsers (DateIn, ComputerID, LogonID) VALUES #" & Now() & "#, '" & Environ("computername") & "', '" & Environ("username") & "';"
docmd.runsql strSQL

This is giving me an error, saying that there is a syntax error in the INSERT INTO statement. I can't see what is wrong, unless I'm missing something glaringly obvious.

Also, what I don't know how to do is then tie this information up to the close event, so that when the user closes the form, and so the database, it will update their login record with the date/time they closed it.

Anyone help?

Many TIA

Duncs

[Duncs]

Ooopps, silly me!

My Form_Open now looks like this...


Dim strSQL As String

strSQL = "INSERT INTO tblUsers (DateIn, ComputerID, UserName) " & _
"VALUES(#" & Now() & "#, '" & _
Environ("computername") & "', '" & Environ("username") & "');"
DoCmd.RunSQL strSQL


This inserts, into tblUsers, the information I need when the user logs in...the Date / Time they load the database, their Computer ID & their network Logon ID.

Now what I want to do is, when they close the database, I want to update the DateOut field with the Date/Time they close it. I'm guessing that I'll need to retrieve form the table, the details for the user’s entry.

In the forms Unload event, I have the following:


Dim strSQL As String

strSQL = "UPDATE tblUsers SET tblUsers.DateOut = Now() " & _
"WHERE (ComputerID='" & Environ("computername") & "') " & _
"AND (tblUsers.UserName='" & Environ("username") & "');"


This works perfectly, except...it will update all instances where the records match the criteria, with the current Date/Time. As this log will be used as a source of when, how long and how often an agent has used the database, I need a way to identify the specific record relating to their current login, and update that.

Is it a simple case of adding additional criteria to the code above, to check for the 'DateOut' field being null, or is there more to it / a more elegant way of achieving it?

Cheers

Duncs

[CreganTur]

Check for a record for that person where the DateOut field is Null or blank- you'll need to decide which, depending on how you have your table setup.