Sorry about that Tom, here they are again.
Cheers,
Sorry about that Tom, here they are again.
Cheers,
Vlad - thank you for re-posting the database (FE and BE). After relinking the two tables from BE to FE, I did the following:
- Opened FE
- Closed FE
- Re-Opened FE
At this point, I thought the six (6) records with a NULL value in DbExit would have automatically received the date. They didn't... see attached snapshot of the table once I re-opened the DB. I was hoping the updating of records would happen automatically once the DB is closed/opened.
What am I missing?
Thanks,
Tom
Hi Tom,
I needed to change a couple of things, try this one please and let me know.
Cheers,
Vlad -- I'll will do some more testing -- at first glance, however, it appears it's working great now.
In order for me to integrate the additional objects into my actual DB, pls allow me to ask a few more questions:
1. I presume table "Copy Of T902_Admin_SystemUserTracking" (FE) was/is only for testing the code, right. Basically, it allowed me to copy/paste records over and over again into linked table T902_Admin_SystemUserTracking. So, it is safe to say I can delete this table?
2. Can query "qrySortedDBEntries_ORIG" be deleted? I presume qrySortedDBEntries is the new query instead.
3. Once I rename the queries to my naming convention, is there are VBA that I need to search for and then replace it w/ new name?
4. My actual forms have more fields, so I just can't copy these demo forms into my DB. What (if any) code needs to be copied over into my forms?
5. And, lastly, while typing this post, I just noticed that my DB shut down automatically (I presume due to inactivity). If so, where do I change the timer (let's say from 5 min to 60 min.)?
Tom
Hi Tom,
Glad to hear it works.
1.Yes, not needed
2.Yes, not needed
3.You will need to make sure you update the new query names in VBA (the Startup function) and in the dependent queries (i.e. the append to temp table uses another query that get the max date).
4.You need the code in the module Startup (M06_DB_Startup) and add the idle time code to the hidden form (F104_N1S_DatabaseUsage); make sure you also copy the new tblSettings or add the new IdleTime field to your own settings table and reference that in the text box I added to F104_N1S_DatabaseUsage
5.tblSettings has the field; you can leave it there or move it to your own settings table if you have one, see above.
Cheers.
Vlad -- your assistance in this thread has been SUPERB!!!!!
Thousand thanks for the prompt and dedicated help. You are awesome!!
Cheers,
Tom
You're welcome Tom, good luck with your project!
Cheers,
Vlad -- I do not take your assistance for granted... one final question in this thread though.
I really would like to display leading zeros in my time. Right now, table format is set to "mm/dd/yyyy hh:nn AM/PM;@".
A DTS such as "03/13/2020 08:00 AM" is displayed, however, as "03/13/2020 8:00 AM". How can I force that leading "0" before the "8:00"?
Entering "=Format(Format([DbEntry],"hhnn"),"0000");@" returns garbled contents.
Sorry to keep on bugging you...
Hi Tom,
Where do you want to apply that. In a query or form use Format([YourDate],"mm/dd/yyyy hh:nn:ss AM/PM"):
Here is the recordsource for your F103_N1S_DatabaseUsageReview form where I added two calculayed fields:
SELECT Q103_N1S_DatabaseUsageReview.*, Format([DbEntry],"mm/dd/yyyy hh:nn:ss AM/PM") AS FormatedDBEntry, Format([DbExit],"mm/dd/yyyy hh:nn:ss AM/PM") AS FormatedDBExit
FROM Q103_N1S_DatabaseUsageReview
ORDER BY Q103_N1S_DatabaseUsageReview.DbEntry DESC;
I changed the control source of the two text boxes to use the new fields and this is the end result:
Cheers,
Vlad -- that'll work... great!!!!
Now, I did some additional testing and there's once scenario where the NULL DbExit value did not get populated. I've put a bunch of screenshots and bulleted explanations in the attached Word document.
I certainly would love to hear your feedback on this particular scenario.
Oh well... maybe it's my renaming of the objects. I'll roll back to original version and start over again. Thanks for validating it, Vlad.
Cheers,
Tom
Vlad - after having tested it various ways, it is still not working for me in the **integrated** solution (w/o having changed any objects). If I had to guess it's because my actual table T902_Admin_SystemUserTracking doesn't has existing records and doesn't have any comments in the column.
Everything seemed to work fine until the comment field was added. I really don't need it but wasn't sure where/how to remove it. Also the timer only seems to work in a brandnew form but not an existing form. All properties have been matched.
Oh well, after all these hours, the only thing that's working for me (in actual DB) is the formatting of the "hh:nn".
Hi Tom,
What exactly is not working? What do you mean your table doesn't have existing records, how do you know it isn't working without records? The comments are there to identify the records that were updated by the code from the "real" ones (so you could very easily inspect/delete/edit them in a query using Is Not Null in the criteria for the DBComments field). I am very sure that they are not the cause of any problems, but if you want to remove them delete the field in all the objects (the table and the queries) and delete the line in the VBA sub that populates them.
The idle time code needs to be in a form that stays open all the time through out the session, that is why I put it in the one you open hidden to log the usage. It needs three things to work: the code in the OnTimer event (and the other small sub immediately after), the Timer Interval set to a positive number (not 0 as the default) - I used 10000 for 10 seconds and the textbox called IdleTime that provides to the code the number of minutes of inactivity for the shut down. In my example I used a dlookup to get the value from the new tblSettings I created in the sample.
Can you put together a new sample with the new objects that you updated so I could have a look?
Cheers,
Vlad -- thank you for the follow-up.
I am currently away from home but I plan to put together a small example. Will try posting it tomorrow morning st the latest.
Thank you again !!!