Experts:
I need some assistance with **populating** a field/record where a value **should have been populated** but for some reason has a NULL value.
First, please find attached sample database (frontend and backend). Once copied to your computer, please ensure to **re-link** the two (2) tables in the backend to the front.
Existing Process:
1. A user opens the frontend.
2. Table "T902_Admin_SystemUserTracking" should now show your name/computer in the latest record. Also, the latest record should show the date time stamp (DTS) of your most recent login in field "DbEntry".
3. Now, while you have this DB session open, the field "DbExit" should be blank. This field/record will be auto-populated once you close the current DB.
4. At this time, close the DB and re-log into it a 2nd time. Check out the table again and your previous session should now show a DTS for both "DbEntry" and "DbExit".
Assuming that's the case, so far so good...
Now, please allow me to describe a scenario where the above does not always work as intended.
Issue:
- Most of the time, I will see a DTS in both fields for any user who logged in/logged out of the database.
- However, once in a while (approximately 10% of the time), the "DbExit" is NOT populated with a DTS for a terminated session (see yellow highlighted cell in the attached JPG).
- For example, per the attached JPG, DbEntryID #3 (Fonda) is an example of such case. That is, upon exit, his DTS was NOT automatically added to the field "DbExit".
Note: In my actual database, I do NOT know how some users close the database. That is, did they actually close it or did they lose their network connection between frontend and backend?
Desired Solution:
- In order to have a complete record, I'd like to automatically identify and update any **past sessions** where there's a NULL value in field "DbExit".
Scenario #1:
- If any ID session has a NULL value where DBEntry Date <> Today's Date(), then take DBEntry DTS and add 0.5 hours and update record.
- Example: Record ID #3 has DBEntry = 03/10/2020. And, obviously 03/10/2020 <> Today's Date() = 03/12/2020.
- Thus, add 30 minutes to DTS 09:05 and replace NULL value with "03/10/2020 09:35".
- While this could be construed as "fudging", I am ok with that user's session being counted as 30 minutes.
- Note: Adding the 30 min only applies if there's a **single** instance of that user for that particular day. If there are multipe sessions (same user; same day), then see scenario #2.
Scenario #2:
- If any ID session has a NULL value where DBEntry Date = Today's Date(), then identify whether or not there are MULTIPLE sessions from **same user**
- In this example (see JPG), two users (Washington and Fonda) have accessed the system today. However, user = Washington entered system today only once. Hence, we must assume it is an active/current session (i.e., he hasn't logged out of the database yet).
- Alternatively, user = Fonda has 2 records where DBEntry Date = Today's Date(). Logically, the first session (Rec ID 5) must have terminated without the DTS being added to "DbExit".
- And RecID #7 must (could) be an active session. For purpose of this test, we must assume it is an active session.
- Thus, for Fonda Rec #7, I want to ID the active session's DBEntry (8:45 AM) and subtract 2 minutes. Then, I want to update Rec #5 and add "8:43" into field "DbExit".
- Ultimately, based on scenario #2, if this process were to work, it then would reflect that a user logged out and within 2 minutes logged back into the system.
In summary, while the existing solution works approximately 90% of the time as is, there are a few cases where DTS in field "DbExit" stays empty. I'd like to fix that through the procedures/scenarios described above.
Alternatively, if the existing VBA code has issues and it doesn't add the "DbExit" DTS because it only works when a DB is closed (but not shut down by the network), I'd welcome information/recommendaitons that would fix the DTS update regardless of how Access was shut down.
Thank you for your recommendations/suggestions in advance.
Cheers,
Tom