Auto updating date and time fields in access 2016 when duplicating a record

Question

I'm new to programming, but I am currently developing a new client database for the company I work for using VBA for Microsoft Access 2016, and macros.

Within this system, there are a set of forms which act as our individual note-taking/to-do lists and sometimes we duplicate a record to save manually inputting the same data many times over. On each form there are two Date/Time fields which auto input the current date and time, one displays date only and the other is time only.

At the moment, to duplicate a record I'm using the pre-programmed macro 'duplicate record' function in the command button wizard. This creates a carbon copy of the record, including the date and time. What I'd like to do is be able to click that button, and have it copy the record whilst auto-updating the date and time fields to the current system time.

The pre-programmed macro is as follows in the On Click event:

OnError
    Go to Next
  Macro Name
RunMenuCommand
    Command SelectRecord

If [MacroError]=0 Then
  RunMenuCommand
    Command Copy
End If

If [MacroError]=0 Then
  RunMenuCommand
    Command RecordsGoToNew
End If

If [MacroError]=0 Then
  RunMenuCommand
    Command SelectRecord
End If

If [MacroError]=0 Then
  RunMenuCommand
    Command Paste
End If

If [MacroError]<>0 Then
  MessageBox
    Message =[MacroError].[Description]
    Beep Yes
    Type None
    Title
End If

Is there something I can add to this macro to make the date and time fields auto update on duplicating a record, or should I use VBA for this procedure?

If you need any more information, just ask.

Seems to me it would just be INF simpler if you used a string SQL variables to do the insert and the date/time fields could just include DATE() & NOW() respectively. — Doug Coats
Macros are rather limited. Convert it to vba, and just set the values you want. — Erik A
I also recommend VBA, never used macros. But macro does have a SetValue method. If your two fields are both date/time type then the field with the time really has both date and time components. Why have two fields? — June7
The reason for having separate date and time fields was due to my boss requesting them, so it was part of the brief. Could anyone give me an idea as to how I would go about coding that button in VBA so that it auto-updates those fields when duplicating the record? I know VBA basics but am still very new to the language. — Japidoo

Cahaba Data Cahaba Data · Accepted Answer · 2018-02-14T22:35:11

Not sure where you are in the design of this application such that you can change table properties/design. If so then set up one date/time field and put its default property to be: =Now()

I believe that will put in the current date/time even in a copy of another record that already has a date/time value.

Then if people want the display of date & time to be separate; just do that with unbound text boxes set up to use LEFT( ) or RIGHT( ) methods to split apart the appearance of the information.

A lot easier to manage this way.

Auto updating date and time fields in access 2016 when duplicating a record

1 Answers