Access Forms - need to capture and display last run time

[SKV]

Hello,

I have several (8) command buttons on my form. Each of them indeed pull/process data from different sources and not need to be refreshed all the times as some of the datasources remain pretty static (changes once a month)

In order to prevent the user to run all these time consuming queries, I want to display date and time when the action was triggered last time for each command button. This will help the user to know how recent the data is and therefore not require refresh.

I know bare minimum in access and learned that I might have to create a table to capture time stamps but dont know how to display it in the form and have it refreshed every time user presses the button.

Please guide

 

[Trevor G]

If you select your form and select design view within the tools you can add labels next to the buttons and when you click the button the Caption of the labels can show the date and time they where last clicked. So each button has events and on the On Click Event you would add a line of code below all the code that is already there. An example would be:

LblOne.Caption=Now()

 

[SKV]

Hello Trevor,

Thanks for your reply. I will try this. I believe that this will help capture the date/time in the current session. What I am also looking for is if something was run last session (say last week) then I should able see that. I believe I have to capture these time-stamps in some table so that it can shown in the form.

what is a better way to achieve it?

Thanks
sachin

 

[Joe4]

Is the Form with the buttons an unbound Form?
If so, one thing you could do is create a one record table that has fields for each command button. Then you could add code to the VBA code that the button runs that capture the current time and updates the appropriate field with it.

If you then change your Form from an unbound Form to a bound Form, binding it to this one record table, you can easily display when each button was last clicked/run.

I use this method a lot. To avoid an issue with multiple records in the one record table, I update the Properties on the Form to not allow any new record additions or record deletions, and disable all record navigation on that form.

 

[SKV]

Joe - Thanks, it makes sense to me. However I am kind of handicapped (have limited knowledge in Access) in thinking of how to implement this. Can you give me some more direction/details or even share a small dB and I will replicate.
Thanks a lot in advance.

 

[Joe4]

Take it one step at a time:

1. Create your table, with each of the Date/Time Stamp fields you want.
2. Populate the first record in this table with some dummy dates, like 1/1/2000 or something like that. The goal is to just have one record of data in it.
3. Assuming your current Form is unbound, open up its Properties and change its Record Source property to your new one record table (on the Data tab).
4. While in the Data tab of Properties of the Form, change the "Allow Additions" and "Allow Deletions" options to "No".
5. In the Form in Design View, select the Field List, and drag the Date/Time fields to where you want them to show up on the Form.
6. Then, in the VBA code for each button, enter a line of code that updates the appropriate Date/Time Stamp field, like this:

Me.[DateStampFieldName1] = Now()

That should do it!

 

[SKV]

Joe - Thanks a lot.......it worked like a charm..........I am assuming that for each date capture (for each cmd btn) I have to create a new field.....so for 2 buttons, I created 2 fields (fld_btn1, fld_btn)...
Curious if I have table with 2 fields only ie. button ID and TimeStamp Value .....then have 1 rcd for each button....... is that possible???

 

[Joe4]

Why do you want one record for each button? A single record with a field for each button value should suffice. If you have multiple records, the form won't work properly.