lgoddgreat
First read:
Bytes > Sitemap > Microsoft Access / VBA Insights>Database Normalization and Table Structures
:You would
not copy the information from the [userINFO] table to the [login_out] table
What you need to establish is the unique primary key in the [userINFO] table. ( You might review:
Bytes > Sitemap > Microsoft Access / VBA Insights>How To Create User Permissions and Customized Menus in MS Access for one such example - PLEASE READ the entire thread, there are other links and information you may find very helpful) you would then reference this primary key
One way would be:
[login_out] table
[login_out]![PK] - autonumber or unique value (see first link)
[login_out]![FK_userINFO] - this is the reference you would use to the other table.
[login_out]![TimeIN]
[login_out]![TimeOUT]
I prefer:
[tbl_eventhistory]
[PK] autonumber
[FK_Users] Foreign key to user table numeric-long
[FK_Event] Foreign key to standard events table numeric-long
[Event_DateTime] Date/Time
[Event_Comment] Text(short-255)
The code I use for the user login varies, as of late, I use the API to pull the current user authenticated to the WindowsOS (
Function to Return UserName (NT Login) of Current User) However, a login form can be used too. In either case I use one of several methods to find the user information in the user table to pull the PK and I store that information for active use (at one time in the form, now in the new TempVars)
Why this format... because I can use a query to pull by date, user, event, etc... Say I want all users logged in today (06/26/2015 - midnight to midnight), then the SQL might be:
- SELECT tbl_EventHistory.EventHistory_pk
-
, tbl_EventHistory.event_datetime, tbl_users.User_EmplyID
-
, stdEvent.stdEvent_Dscp, tbl_EventHistory.event_coment
-
FROM (tbl_EventHistory
-
INNER JOIN tbl_users
-
ON tbl_EventHistory.fk_tbluser = tbl_users.users_pk)
-
INNER JOIN stdEvent
-
ON tbl_EventHistory.fk_tblevent = stdEvent.stdEvent_pk
-
WHERE (((tbl_EventHistory.event_datetime)
-
Between #6/26/2015# And #6/27/2015#)
-
AND
-
((tbl_EventHistory.fk_tblevent)=1));
Notice that how I've joined on the other two tables to pull in the human readable text instead of the related numeric fields... the data in table_event history might read:
- [EventHistory_PK][fk_tbluser][fk_tblevent][event_datetime][event_comment]
-
[1] [1] [1] [06/26/2015 12:01:00 AM][No Login Errors]
-
[2] [1] [2] [06/26/2015 02:01:00 AM][No Logout Errors]
-
(...)
What I will see from the query
- [EventHistory_PK][event_datetime][User_EmplyID][stdEvent_Dscp][event_coment]
-
[1][06/26/2015 12:01:00 AM][QCQALAB3412353][SignIN][No Login Errors]
-
[10][06/26/2015 02:01:00 AM][EXTCLAB356555][SignIN][No Login Errors]
-
[105][06/26/2015 08:01:00 AM][QCQALAB3412353][SignIN][No Login Errors]
-
[315][06/26/2015 12:01:00 PM][QAQCSPR0000016][SignIN][5 Login Errors - invalid password - emails sent]
-
(...)
-
I can see now that the same user logged in twice etc... Slight modification and I can see other events such as data editing etc...
The code is fairly simple.
The Login form
UserID field is unbound (I may use the API function to prefill the user id from the OS - depends) (I use a sha2 encrypted user name)
UserPassPhrase is unbound (once again, sha2 to scramble...)
the on click event of the "Sign On/Off" command button (I change the names btw from default to something meaningful) ... the UserID is sha2'd I then open a recordset on the usertable with the where condition set to this value because I pull several values from this record if the passphrase matches.
If all is good then I use a simple:
Database.Execute Method using a simple
insert SQL to insert the record in to the events history table. Set information such as the user's plan text name and current privilege state etc... in to the tempvars and the close any open recordsets.
Give it a try... and post back what you've decided to do (or if you get stuck) and one of use will lend a hand...