Generic Code that any button on any form in a DB can call ??

[jdowski]

Hi Access Experts,

Be gentle with me....I'm an Excel guy playing in Access.

We have several DBs that our department uses for month end reporting. Each DB has several form with buttons that run macros/code & and/or print reports. What I am trying to do is have each button caption for each button, on the various forms, when clicked, to display the current date & time so users will know the last time the macro behind the button was ran. I have created a function called "Timestamp" that gives me the value I want.

Function DateStamp()

DateStamp = Format(Now(), "mm/dd/yy - h:mm am/pm")

End Function

I created another function tied to a single button the calls the function and that works too.

Function UpdateButton()
Me.Command111.Caption = DateStamp
End Function

But now I would like to know how to make the code that calls the Timestamp function generic enough so I can point any button to it and it will run. I would like to be able to use this for all the forms in this database.

Thanks,

Joe Dowski
Oxford, CT. USA.

 

[Philem]

Hullo. That should be easy enough. Try this UNTESTED pseudo-code:

OnClick cmdButton()
'do your code
DateStamp()

Since you've created it as a function, you can call it from anywhere in the db.

HTH

P

 

[mdmilner]

Re: Generic Code that any button on any form in a DB can cal

Have the button clicked pass it's name to the Public Function that assigns the timestamp to the Caption. Change the function to use the provided value to assign the correct date to the button. When closing the form, you may wish to suppress the save dialog that appears for the form if you wish the Caption values to stay for the next time the mdb is opened.

I was curious if this was the best approach. Captions are the text that actually appear on the button to identify it. Optionally, you could change the ControlTip Text property so that when you leave the mouse over the button for more than a couple seconds, you can see any message you wish, including the full text "This Macro was Run last at m/d/y hh:mm"

Or, set both. Let the user see the TimeStamp, and then use Tip to give them a much longer explanation.

 

[jdowski]

Re: Generic Code that any button on any form in a DB can cal

mdmilner,

So how exactly do you "pass" the button's name to the public function I've created. Another question and/or issue I have is that the caption doesn't stay on the button if I move to another tab on this tabular form and then come back, the caption becomes blank.....

Have the button clicked pass it's name to the Public Function that assigns the timestamp to the Caption. Change the function to use the provided value to assign the correct date to the button. When closing the form, you may wish to suppress the save dialog that appears for the form if you wish the Caption values to stay for the next time the mdb is opened.

I was curious if this was the best approach. Captions are the text that actually appear on the button to identify it. Optionally, you could change the ControlTip Text property so that when you leave the mouse over the button for more than a couple seconds, you can see any message you wish, including the full text "This Macro was Run last at m/d/y hh:mm"

Or, set both. Let the user see the TimeStamp, and then use Tip to give them a much longer explanation.

 

[mdmilner]

Re: Generic Code that any button on any form in a DB can cal

Jdowski,

Working backwards, you said your captions become blank.
Are they blank or can you just not see them? Go into design mode and look at the properties. Is the Caption the value you changed it to?

What I'd meant by passing a value - Functions can accept variables or values passed to them. I've removed the error trapping but, inserting the first into your command button, and the second as your function.

Private Sub Command30_Click()

AddStamp (Forms!frmMakeChanges.Command30)

End Sub

Public Function AddStamp(ByVal strfrm As Object)

strfrm.Caption = Format(Now(), "mm/dd/yy - h:mm am/pm")

End Function

You could also make VBA assign the value you're sending instead of typing it in as I did, thus allowing you to use precisely identical code everywhere.