Access: Update a text box to a value when a new record is created in Access 2003/XP/2000/97

Question:  In an Access 2003/XP/2000/97 form, I want to check when the user adds a new record (is there any relevant event?) so that I can load a default date value in a text box. I tried to go to a new record each time the form was loaded, but that resulted in numerous unwanted records. How can I avoid this?

Answer:  On the Form object, there should be a "Before Insert" event. You should be able to place code there to set the value of the text box.

Download version in Access 2000 (example as demonstrated below)

Let's take a look at an example. Often times, it is useful to know when a record has been created in your Access database. So we've created a Customers table that contains a field called Create_Date. We will use this field to store the exact date and time of when the record was created.

This is an example of a form that we've created. There is code on the "Before Insert" event of this form to update the field called Create_Date. When you are viewing this form in "form mode", you can not see the field called Create_Date.

However, if you view this form in Design View, you will notice that there is, in fact, a field called Create_Date. This field has been set to "not visible" so that the user can not interfere in the "Before Insert" event code that will run.

When a new record is inserted into the Customers table, the Create_Date field will be updated with the current system date/time.

The VBA code is quite simple:

Private Sub Form_BeforeInsert(Cancel As Integer)

    'Set the Create_Date field in the Customers table to the current system
    'date when a new record is created
    Create_Date = Now()

End Sub