Jackie,
To begin with, it's quite easy to do it, but before I get to the "how",
allow me to propose another alternative that will do the job just as well,
will be easier to implement and transaprent to the user, with just one
condition: that each user (student) has a personal, unique Windows logon
name (which, I suppose, should be the case). The trick then is to use an
expression that returns the user's Windows logon name, so you identify them
without them even realizing. In case this method of identifying the user
does not work, you will need to make some changes, but the overall concept
is the same. Let me know and I'll tell you.
Now, to the specifics:
You will need:
* A table to log users logging in and out
* An event to trigger some code to register the logon on db open
* An event to trigger some code to register the logoff on db close
The table should be something like (name assumptions will be used further
down):
[tblLogonTrace]
fldID (autonumber / PK)
fldUser (text)
fldLogon (date/time)
fldLogoff (date/time)
For the db open and close events, the easiest / best way is to use a form
that will open on db startup (defined through Tools > Startup), which will
fire the startup code and store the record's ID for use at logoff; the same
piece of code will hide the form, so it remains invisible for the rest of
the session. When the user exit the database, the form will be forced to
close, and its Close event will fire the logoff registration code.
So, open a new form in design view, and add a single textbox on it. With the
textbox selected, display Properties, select tab Other and go to the first
one (Name) and change its name to SessionID. Save the form as, say,
frmRegister.
Now select the whole form (by clicking on the little grey square in the
cross-section of the two rulers), display properties again, go to tab Events
and place the cursor next to On Open; click on the little button with the
three dots that appears on the right, and select Code Builder. You will be
taken to the VB editor screen, and the cursor will be in between two lines
that look like:
Private Sub Form_Open(Cancel As Integer)
End Sub
Paste the following code between those lines:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblLogonTrace")
With rs
.AddNew
Me.SessionID = .Fields(0)
.Fields(1) = Environ("UserName")
.Fields(2) = Now()
.Update
.Close
End With
Set rs = Nothing
Set db = Nothing
Me.Visible = False
Go back to the form design, and follow the same process for the form's On
Close event; the lines that automatically come up are:
Private Sub Form_Close(Cancel As Integer)
End Sub
Paste the following code between those lines:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT * FROM tblLogonTrace WHERE "
strSQL = strSQL & "fldID = " & Me.SessionID
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)
With rs
.MoveFirst
.Fields(3) = Now()
.Update
.Close
End With
Set rs = Nothing
Set db = Nothing
While you are in the VE editor screen, go to Tools > References and scroll
down to find the appropriate Microsoft DAO X.X Object Library reference and
tick it. If you are working with A97 use DAO 3.51, if with A2K or later use
DAO 3.6 reference.
Close the VB window, return to your form and save. The only thing left to do
is go to Tools > Startup and choose the form to open at startup.
Note: if you have some other form (or a swichboard) that you would like to
open automatically at startup, then add this line of code:
DoCmd.OpenForm "MyStartupForm"
at the end of the code of the On Open event, right before the Exit Sub. This
will open the form right after the logon registration process is completed
(change MyStartupForm to the actual name of your form).
I hope this is all clear and it helps; if not, post back with questions.
Nikos
