I am new to access, I have a table to log the techs in and out.
LoginID;TechID;LoginDate;LoginTime;LogOutDate;LogOutTime
I would like my LogOutDate and LogOutTime to be on the same line as the
LoginDate and LoginTime for each Tech.
I have a login and logout button on my main form. Eventually I need them to
have a username and password and a report to timestamp when they are in the
database. I am not sure how to achieve that.
I've seen something like this done before, and even done it myself. I
should mention that it's not a foolproof method - if a user closes the
database in a non-standard way (such as ctrl-alt-delete), the events
required to update the various timestamps may not execute.
Also, are you trying to track each time a user logs in, for example to
see who is using the system, when it's used, how often certain people
use it, etc? Or are you just trying to create something that lets you
know who is in the DB at a given time? I'm assuming the latter. Keep
in mind that there are multiple ways to accomplish this - below is
only one idea.
First, I'd modify your table a bit. It looks like you've separted the
date and time (for both login and logout) into separate fields. It's
not necessary to do this - both date and time can be stored in a
single field, and formatted appropriately on forms and reports (i.e.
you could have a report that shows the date in one column and the time
in another, yet both values are coming from the same database field).
I think that having separate database fields will only serve to
increase the size of the database, yet not offer any additional
functionality (though admittedly it wouldn't increase the size by a
lot unless you have a lot of users, in which case Access probably
isn't the best thing to be using for this). I'd have these fields:
LoginID
TechID
LoginDate (will also include the time)
LogoutDate (will also include the time)
Eventually, you will need to add a password field to that, and you'll
probably want to encrypt it or else anyone who has access to the back
end tables can see each user's password.
When a user clicks the Login button that you mentioned you have,
presumably they'd have to enter their login ID. The tool should first
check to see if the login ID they entered exists in the database (if
it doesn't, then the user doesn't have access to the tool). If it's
found, store the value in a global variable (for use later on when the
user logs out). Then, you'd probably want to execute an update
statement something like this:
UPDATE tblUsers SET LoginDate = Now(), LogoutDate = Null WHERE LoginID
= 'whatever the user entered';
So, once a user has logged in, there will be a date/time value for the
LoginDate, and the LogoutDate will be null. When the user clicks the
Logout button, you'd want to execute something like this:
UPDATE tblUsers SET LogoutDate = Now() WHERE LoginID = <userLoginID>;
In order to see who's currently logged on, you could use a query
something like this:
SELECT * FROM tblUsers WHERE LoginDate IS NOT NULL AND LogoutDate IS
NULL;
Of course, if the user exits the application by any means other than
clicking the logout button, the LogoutDate won't get updated, and so
the report won't be completely accurate. You may be able to at least
partially remedy this by making a call to the Logout button's
procedure in the form's OnClose event, or something like that (I'm not
sure exactly which events execute when you close Access by clicking
the X in the top-right corner).
