473,324 Members | 2,548 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Auto populating Table from Data's from other Table

Hi I have this project, and I've been working on it for days. I decided to add something like a tracking record on my project, the users Login and Logout time will be tracked and stored on the [login_out] table, I've already manage to store the login/logout time of the user, what i cant seem to do is to store the information of the user who logged-in into the [login_out] table. There is a separate table for the users information called [userINFO] where all of his information including: [userINFO]![UserType], [userINFO]![Firstname], [userINFO]![Lastname], [userINFO]![Username], [userINFO]![Position] are stored in.

question is how do i auto populate the [login_out] table from the data's in [userINFO] when a user login.

Expand|Select|Wrap|Line Numbers
  1. Set db = CurrentDb
  2.     Set rs = db.OpenRecordset("SELECT * FROM login_out")
  3.  
  4. rs.AddNew
  5. rs("Login") = Time()
  6. rs.Update
so far this is the only thing that I have since im new in VBA
Jun 26 '15 #1

✓ answered by zmbd

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:

Expand|Select|Wrap|Line Numbers
  1. SELECT tbl_EventHistory.EventHistory_pk
  2. , tbl_EventHistory.event_datetime, tbl_users.User_EmplyID
  3. , stdEvent.stdEvent_Dscp, tbl_EventHistory.event_coment
  4. FROM (tbl_EventHistory 
  5.     INNER JOIN tbl_users 
  6.        ON tbl_EventHistory.fk_tbluser = tbl_users.users_pk)
  7.     INNER JOIN stdEvent 
  8.        ON tbl_EventHistory.fk_tblevent = stdEvent.stdEvent_pk
  9. WHERE (((tbl_EventHistory.event_datetime) 
  10.           Between #6/26/2015# And #6/27/2015#)
  11.      AND 
  12.       ((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:
Expand|Select|Wrap|Line Numbers
  1. [EventHistory_PK][fk_tbluser][fk_tblevent][event_datetime][event_comment]
  2. [1]              [1]         [1]          [06/26/2015 12:01:00 AM][No Login Errors]
  3. [2]              [1]         [2]          [06/26/2015 02:01:00 AM][No Logout Errors]
  4. (...)
What I will see from the query
Expand|Select|Wrap|Line Numbers
  1. [EventHistory_PK][event_datetime][User_EmplyID][stdEvent_Dscp][event_coment]
  2. [1][06/26/2015 12:01:00 AM][QCQALAB3412353][SignIN][No Login Errors]
  3. [10][06/26/2015 02:01:00 AM][EXTCLAB356555][SignIN][No Login Errors]
  4. [105][06/26/2015 08:01:00 AM][QCQALAB3412353][SignIN][No Login Errors]
  5. [315][06/26/2015 12:01:00 PM][QAQCSPR0000016][SignIN][5 Login Errors - invalid password - emails sent]
  6. (...)
  7.  
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...

2 1426
How are you tracking the login/logout times? I might be able to help you figure something out, but would like to be able to test it first-
Jun 26 '15 #2
zmbd
5,501 Expert Mod 4TB
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT tbl_EventHistory.EventHistory_pk
  2. , tbl_EventHistory.event_datetime, tbl_users.User_EmplyID
  3. , stdEvent.stdEvent_Dscp, tbl_EventHistory.event_coment
  4. FROM (tbl_EventHistory 
  5.     INNER JOIN tbl_users 
  6.        ON tbl_EventHistory.fk_tbluser = tbl_users.users_pk)
  7.     INNER JOIN stdEvent 
  8.        ON tbl_EventHistory.fk_tblevent = stdEvent.stdEvent_pk
  9. WHERE (((tbl_EventHistory.event_datetime) 
  10.           Between #6/26/2015# And #6/27/2015#)
  11.      AND 
  12.       ((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:
Expand|Select|Wrap|Line Numbers
  1. [EventHistory_PK][fk_tbluser][fk_tblevent][event_datetime][event_comment]
  2. [1]              [1]         [1]          [06/26/2015 12:01:00 AM][No Login Errors]
  3. [2]              [1]         [2]          [06/26/2015 02:01:00 AM][No Logout Errors]
  4. (...)
What I will see from the query
Expand|Select|Wrap|Line Numbers
  1. [EventHistory_PK][event_datetime][User_EmplyID][stdEvent_Dscp][event_coment]
  2. [1][06/26/2015 12:01:00 AM][QCQALAB3412353][SignIN][No Login Errors]
  3. [10][06/26/2015 02:01:00 AM][EXTCLAB356555][SignIN][No Login Errors]
  4. [105][06/26/2015 08:01:00 AM][QCQALAB3412353][SignIN][No Login Errors]
  5. [315][06/26/2015 12:01:00 PM][QAQCSPR0000016][SignIN][5 Login Errors - invalid password - emails sent]
  6. (...)
  7.  
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...
Jun 27 '15 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: keliie | last post by:
I have a relatively simple (I assume) issue which I am at a complete loss to address. My issues is: I want to populate fields in my tables with summary data from the same table. Let me explain: ...
1
by: hgriva | last post by:
Hi, i have a question ------------------------------------- i have 2 tables user_security and customer table structures ----------------------------- user_security
1
by: soahil | last post by:
Hello to everybody! I want that during inserting values in the master/detail form whenever I select a Company ID and enter BILL Amount against that company and SAVE that record then It should SAVE...
1
by: rhepsi | last post by:
HII all, im working on postgresql database where i want to copy the data from one table to other table.... when im trying to write the sql query.. ERROR: 42601: syntax error at or near...
3
by: Hanuma | last post by:
Hi I want the structure of the one table should copy to new table. Can i do this one in our sql server? please tell me the solution. thanks in advance and will appreciated.
6
by: Rolandas | last post by:
Hello, I have one table which I want to append it with new records, e.g. let's call this table TABLE1. The table from which I want to append is TABLE2. This table is made from queries, so it is...
2
by: calbaptedm | last post by:
Have a table with my master information for individuals and would like to automatically export the data to other tables based on the criteria selected. On my master table you can select multiple...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.