Record of users

[Guest]

I am using the following on a simple startup form to open a specific form
based on the user who opens the database.

Dim USER As DAO.Recordset
Set USER = Me.RecordsetClone
USER.FindFirst "[rsUSER] = '" & Me.txtUSER & "'"
If USER.NoMatch Then
DoCmd.Quit
Else
DoCmd.OpenForm USER![rsForm], acNormal
End If
DoCmd.Close acForm, "startup"

This works great matching an ID from the Environ Variable. Can someone
suggest a solution to record the date and time each user opens the database
and hold the information in a table? Thank you

 

[Keith Wilby]

Can someone
suggest a solution to record the date and time each user opens the
database
and hold the information in a table? Thank you

Hi Jeff, this works for me in my main form's open event (libUserName() is a
custom function which returns the current windows username):

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("qtblLog", , dbAppendOnly)

With rs
.AddNew
!UserName = libUserName()
!fldCurrentUser = CurrentUser
!computername = Environ("COMPUTERNAME")
!LogIn = Now()
!Version = gcGUIVersion
.Update
.Bookmark = .LastModified
glngLogID = !logid
.Close
End With
Set rs = Nothing
Set db = Nothing

Obviously you'll need to change object names where necessary. Here's the
corresponding "log out" code:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("qtblLog")

With rs
.FindFirst "LogID = " & glngLogID
If Not .NoMatch Then
.Edit
!LogOut = Now()
.Update
End If
.Close
End With
Set rs = Nothing
Set db = Nothing

Keith.
www.keithwilby.com

 

[Douglas J Steele]

Far safer than using the ID from the Environ variable is to use the API call
shown in http://www.mvps.org/access/api/api0008.htm at "The Access Web".
It's trivial to reset the Environ variable.

To log whenever a user opens the database, have a form that opens when the
database first opens, and put code in its Open event to write to the table.

If you have a form that's always open while the user is in the application,
you can put code in that form's Unload event to write when the user leaves
the application. However, that's not as reliable, as they could simply power
off.

 

[Guest]

Thank you, as you suggested

I added the following to my on open event

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
With rst
.Open "tbl_UserLog", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic, adCmdTable
.AddNew
![Name] = [txtRGRHName]
![USER] = [txtUSER]
![Date] = [txtDate]
![Time] = [txtTime]
.Update
.Close
End With
--
Jeff C
Live Well .. Be Happy In All You Do

Far safer than using the ID from the Environ variable is to use the API call
shown in http://www.mvps.org/access/api/api0008.htm at "The Access Web".
It's trivial to reset the Environ variable.

To log whenever a user opens the database, have a form that opens when the
database first opens, and put code in its Open event to write to the table.

If you have a form that's always open while the user is in the application,
you can put code in that form's Unload event to write when the user leaves
the application. However, that's not as reliable, as they could simply power
off.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am using the following on a simple startup form to open a specific form
based on the user who opens the database.

Dim USER As DAO.Recordset
Set USER = Me.RecordsetClone
USER.FindFirst "[rsUSER] = '" & Me.txtUSER & "'"
If USER.NoMatch Then
DoCmd.Quit
Else
DoCmd.OpenForm USER![rsForm], acNormal
End If
DoCmd.Close acForm, "startup"

This works great matching an ID from the Environ Variable. Can someone
suggest a solution to record the date and time each user opens the database
and hold the information in a table? Thank you

 

[Douglas J Steele]

That's the general idea, but let me critique it a bit.

You shouldn't have fields named Date and Time in your table: those are
reserved words, and you should never use them for your own purposes. Also,
in Access, the Date/Time field is intended to hold both the date and the
time: there's no reason to have two separate fields. (If you want only the
date from a timestamp, the DateValue function will retrieve that part.
There's also a TimeValue function to retrieve only the time portion of a
timestamp)

Assuming txtRGRHName, txtUser and so on are the names of controls on your
form, you should be referring to them as Me!txtRGRHName, Me!txtUser and so
on.

There's really no reason to open a recordset for what you're doing: an
INSERT query would be better.

Try:

Dim strSQL As String

strSQL = "INSERT INTO tbl_UserLog (UserName, UserID, LoginDtm) " & _
"VALUES ("' & Me!txtRGRHName & "', '" & Me!txtUSER & "', " & _
Format(Now(), "\#mm\/dd\/yyyy hh\:nn\:ss\#") & ")"

CurrentDb.Execute strSQL, dbFailOnError


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you, as you suggested

I added the following to my on open event

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
With rst
.Open "tbl_UserLog", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic, adCmdTable
.AddNew
![Name] = [txtRGRHName]
![USER] = [txtUSER]
![Date] = [txtDate]
![Time] = [txtTime]
.Update
.Close
End With
--
Jeff C
Live Well .. Be Happy In All You Do

Far safer than using the ID from the Environ variable is to use the API call
shown in http://www.mvps.org/access/api/api0008.htm at "The Access Web".
It's trivial to reset the Environ variable.

To log whenever a user opens the database, have a form that opens when the
database first opens, and put code in its Open event to write to the table.

If you have a form that's always open while the user is in the application,
you can put code in that form's Unload event to write when the user leaves
the application. However, that's not as reliable, as they could simply power
off.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am using the following on a simple startup form to open a specific form
based on the user who opens the database.

Dim USER As DAO.Recordset
Set USER = Me.RecordsetClone
USER.FindFirst "[rsUSER] = '" & Me.txtUSER & "'"
If USER.NoMatch Then
DoCmd.Quit
Else
DoCmd.OpenForm USER![rsForm], acNormal
End If
DoCmd.Close acForm, "startup"

This works great matching an ID from the Environ Variable. Can someone
suggest a solution to record the date and time each user opens the database
and hold the information in a table? Thank you