none
good afternoon RRS feed

  • Question

  • good afternoon

    i hope you have a great day today

    i am trying to add a now() field to a table every time a employer adds a new record. 

    I have a table resolutionstbl, this table has several fields and one of them is time_modified, in this field is where i need to add the table new record's date 

    Thursday, September 25, 2014 12:52 PM

Answers

  • I assume this in an Access database.

    You can do this in the form used to enter records in the table.

    • Open the form in design view.
    • Activate the Event tab of the Property Sheet (press F4 if you don't see the Property Sheet).
    • Click in the Before Insert event.
    • Select [Event Procedure] from the dropdown in this event.
    • Click the builder dots ... to the right of the dropdown arrow.
    • Make the code look like this:

    Private Sub Form_BeforeInsert(Cancel As Integer)
        Me.time_modified = Now
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, September 25, 2014 2:23 PM

All replies

  • Copy this code, right-click the sheet tab, select "View Code" and paste the code into the window that appears. Then save the workbook as a macro-enabled .xlsm.  This will add the date created as a constant - if you want time or a formula......

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Intersect(Target, Me.ListObjects("resolutionstbl").DataBodyRange) Is Nothing Then Exit Sub
        Application.EnableEvents = False
        With Intersect(Target.EntireRow, Me.ListObjects("resolutionstbl").ListColumns("time_modified").DataBodyRange)
            If .Value = "" Then .Value = Date
        End With
        Application.EnableEvents = True
    End Sub

    Thursday, September 25, 2014 2:18 PM
  • I assume this in an Access database.

    You can do this in the form used to enter records in the table.

    • Open the form in design view.
    • Activate the Event tab of the Property Sheet (press F4 if you don't see the Property Sheet).
    • Click in the Before Insert event.
    • Select [Event Procedure] from the dropdown in this event.
    • Click the builder dots ... to the right of the dropdown arrow.
    • Make the code look like this:

    Private Sub Form_BeforeInsert(Cancel As Integer)
        Me.time_modified = Now
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, September 25, 2014 2:23 PM