Adding a date/time stamp to a form (2002)

Topic

New Reply

I have a form with 4 buttons on that each run different macros. One of the buttons only needs to be run periodically so I thought it’d be useful to have a date/time stamp to the side of it so users know when it was last run. In effect, when the button gets pressed, the current date and time is overwritten in the field. I have no idea how to achieve this but have seen it done before. Could someone please give me some basic step by step instructions on how I could achieve this? I’m at the edge of my Access knowledge (which isn’t alot!) with this one.

Reply | Quote

Replies

Create a new table in design view.
Add one field named TimeStamp, of type Date/Time.
Set its Format property to General Date.
Save the table as tblTimeStamp.
Open the table and datasheet view and enter the current date in the first record, then close the table again.

Open the form in design view.
Put a text box next to the command button, named txtTimeStamp.
Set its Format property to General Date.
Set its Control Source property to

=DLookup("TimeStamp","tblTimeStamp")

Set the Enabled property of the text box to No, and the Locked property to Yes.

Add the following code to the On Click event procedure of the command button:

Dim strSQL As String
strSQL = "UPDATE tblTimeStamp Set TimeStamp = Now()"
CurrentDb.Execute strSQL
Me.Recalc

This will update the TimeStamp field in the table (so that it is stored for later) and update the value of the text box.

Reply | Quote

The On Click event procedure of the command button is already set to the name of my macro that runs a series of queries. Where should I put the code?

Reply | Quote

If you are going to use Hans’s code, you will need to convert the macro you have into VBA code, and then copy Hans’s code into that macro.

Make a backup of your database (or a copy of the macro), and then follow these steps:

– In the database window, select the macro that runs the queries. (Do not open it!)
– Select the Tools Menu, Macro, Convert Macros to Visual Basic.
– Choose OK to accept the prompt about Error Handling and Comments
– You will now see a module in the VB Editor appear with a Function procedure.
– Copy Hans’s code and paste it into the procedure, just below the On Error GoTo statement.
– Close the VB editor
– Open your form and go to the properties of the button that runs the macro.
– Switch to the event tab and change the macro name in the On_Click event to [Event Procedure] (Use the drop down to select it.
– Click on the elipse button (the 3 dot button) and type the name of the Function procedure (that was created when you converted the macro) into the Sub, End Sub, statements to link the button to the function.

The form button should now run the VBA code that now contains Hans’s code and the queries that the original macro ran.

Reply | Quote

Thanks Rudi. Sorry if this is a dumb question but I’m getting unstuck on your last step when I have to click on the elipse button. Please clarify what sort of thing I should be typing and exactly where. Thanks.

Reply | Quote

When you click on the elipse button (after you select [Event Procedure], it will create a VB module with something that looks like this:

Private Sub Command0_Click() ‘The Command0 will be the name of your form button if you assigned a name to it at design time.

End Sub

You need to type the name of the function procedure inbetween the Private Sub and End Sub statements. The name of the function that must be pasted between these sub statements can be found in the VBA module when you converted the original macro. Look for the beginning of the macro where it starts with Function. For example if your macro was called RunQuery, the Function will be called:
Function FormName_RunQuery(). You must copy FormName_RunQuery into the Sub and End Sub statements.

Hope this is clearer.

Reply | Quote

The function found in the VBA module from the converted macro just shows as ‘Function getpriceeach() ‘. getpriceeach is the name of my macro. When I paste ‘getpriceeach()’ between the Sub and End Sub statements (for my button), I get ‘Compile Error Expected: =’.

Reply | Quote

You should omit the () after the function name when you call it in the On Click event procedure, just use

getpriceeach

or

Call getpriceeach

Reply | Quote

Edited by HansV to replace Word doc with cropped version of the Word document it contained.

Oops! Ok….I’m a bit further forward now but am getting a compile error in the VB code for the button. Please see attached screenshot.

Reply | Quote

DoCmd.RunCommand is not valid without an argument after it. I’d remove all instances of this line.

Reply | Quote

Now I get “Compile Error Invalid use of Me keyword”.

Reply | Quote

You can do one of the following:

1) Leave the function where it is, and replace Me with CodeContextObject:

CodeContextObject.Recalc

or

2) Select the entire function from Function getpriceeach() up to and including End Function.
Press Ctrl+X to cut the code to the clipboard.
Open the code module for the form.
Press Ctrl+End to go to the last line.
Press Ctrl+V to paste the function into this module.

Reply | Quote

Hans,

CodeContextObject is a great tip. I looked up the help in Access but still am in doubt to its use? The help says:
[indent]

---

You can use the CodeContextObject property to determine the object in which a macro (macro: An action or a set of actions that you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language.) or Visual Basic code is executing. Read-only Object.

---

[/indent]

Is CodeContextObject like a smarter Me statement, where Me determines the object based on the current module reference, and CodeContextObject is a reference to the object in reference, irrespective of in which module the code is running in?

TX

Reply | Quote

Yes, indeed.
In the module behind a form or report, you can use Me to refer to the form or report.
If the code in the form or report module calls a function or procedure that is stored in a general module, that function or procedure can *not* use Me, since the code is not stored in the module belonging to the form or report.
One workaround is to pass an argument of type Form or Report to the funciton or procedure.
Another, often easier one is to use CodeContextObject. It represents the object (form or report) from which the code is called, so it can be used to replace Me in code outside the form/report module.

Reply | Quote

I replaced Me with CodeContextObject.Recalc

When the button is pressed the macro now correctly runs each of my queries but at the end of this I get “Syntax error in update statement”.

Here is the code…

‘————————————————————
‘ getpriceeach
‘
‘————————————————————

Function getpriceeach()
On Error GoTo getpriceeach_Err

DoCmd.SetWarnings True
‘ 1. Add order info
DoCmd.OpenQuery “1 – ADD ORDER INFO”, acViewNormal, acEdit

‘ 1a. Group & sum
DoCmd.OpenQuery “1A – GROUP&SUM”, acViewNormal, acEdit

‘ 2. Group & max
DoCmd.OpenQuery “2 – GROUP & MAX”, acViewNormal, acEdit

‘ 3. Calculate price each
DoCmd.OpenQuery “3 – CALCULATE PRICE EACH”, acViewNormal, acEdit

DoCmd.SetWarnings True

Dim strSQL As String
strSQL = “UPDATE tblTimeStamp Set TimeStamp = Now()”
CurrentDb.Execute strSQL
CodeContextObject.Recalc
Exit Function

getpriceeach_Exit:
Exit Function

getpriceeach_Err:
MsgBox Error$
Resume getpriceeach_Exit

End Function

Reply | Quote

Could you post a stripped down copy of your database? See post 401925 for instructions.

Reply | Quote

I’ve stripped out almost everything from the database. When you open the Form and press the first button ‘1. Calculate Price Each’ it should update the date/time stamp to the side of it, but it doesn’t. In this stripped down version I’ve removed the queries the button would have also executed as my problem lies with the date/time not changing.

Reply | Quote

I didn’t know that TimeStamp is a reserved word in Jet SQL – see SQL Reserved Words.
I don’t know why exactly it is reserved, since it’s not used in Jet SQL. It probably has to do with SQL Server.
Anyway, this means that you must enclose TimeStamp in square brackets so that SQL sees it as a field name instead of a built-in term. (Alternatively, you could use another name than TimeStamp in the table and in the code).
If you change the line that sets strSQL to

strSQL = "UPDATE tblTimeStamp Set [TimeStamp] = Now()"

it’ll work correctly.

Sorry about this confusion. I learned something new too!

Reply | Quote

It works!! Thank you Hans and Rudi for your patience and support. It is VERY much appreciated.

Reply | Quote

Additionally, you must move the line Exit Function to below the Me.Recalc line, otherwise the time stamp will never be written (or remove it, there’s another Exit Function immediately below).

Reply | Quote