Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Lounger
    Join Date
    Oct 2007
    Location
    Cambridgeshire, UK
    Posts
    42
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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
    <code>
    =DLookup("TimeStamp","tblTimeStamp")
    </code>
    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:
    <code>
    Dim strSQL As String
    strSQL = "UPDATE tblTimeStamp Set TimeStamp = Now()"
    CurrentDb.Execute strSQL
    Me.Recalc
    </code>
    This will update the TimeStamp field in the table (so that it is stored for later) and update the value of the text box.

  3. #3
    Lounger
    Join Date
    Oct 2007
    Location
    Cambridgeshire, UK
    Posts
    42
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    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?

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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.
    Regards,
    Rudi

  5. #5
    Lounger
    Join Date
    Oct 2007
    Location
    Cambridgeshire, UK
    Posts
    42
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    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.

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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.
    Regards,
    Rudi

  7. #7
    Lounger
    Join Date
    Oct 2007
    Location
    Cambridgeshire, UK
    Posts
    42
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    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: ='.
    <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

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

    getpriceeach

    or

    Call getpriceeach

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

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

  10. #10
    Lounger
    Join Date
    Oct 2007
    Location
    Cambridgeshire, UK
    Posts
    42
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    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.
    Attached Images Attached Images
    • File Type: png x.png (22.8 KB, 0 views)

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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).
    Attached Images Attached Images

  12. #12
    Lounger
    Join Date
    Oct 2007
    Location
    Cambridgeshire, UK
    Posts
    42
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    Now I get "Compile Error Invalid use of Me keyword".

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  14. #14
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    Hans,

    CodeContextObject is a great tip. I looked up the help in Access but still am in doubt to its use? The help says:
    <hr>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.<hr>

    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
    Regards,
    Rudi

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •