Date last revised

  • Thread starter Thread starter My-Yen
  • Start date Start date
M

My-Yen

Hi,

How do I show or save on the 'form' the last date that the
record was revised or created, automatically when it was
updated? Is there a control or do I have to program it in?

thanks,
My-Yen
 
You will need to program it in, but that is pretty simple.

Presuming you have two fields: DateCreated and DateUpdated. Put a
control bound to each of these fields on your form. These two controls can
be visible or invisible as you wish.

For a new record, you will update the DateCreated field by inserting the
following code in the Before Insert event of your form:

Me!DateCreated = Date()

For an existing record which has been updated, insert the following code in
the Before Update event of your form:

Me!DateUpdated = Date()


hth,
 
Thanks for your respond.
However, I am not sure where to put the code (e.g. Before
Insert event??).
I am a beginner, so I am not too familiar with where
things are...sorry.
Where can I find the "Before Insert event" and "Before
Update"?

Thanks!
MyQ
 
My-Yen,

Both of these Events can be found in the Properties of your Form.

So, open your Form in Design View, then:

1. Press ALT-Enter to open the Property Sheet for the form.

2. You will see another form with a ComboBox at the top which says "Form".
Leave that one alone. Below the ComboBox is a set of Tabs. Click the one
that says "Events".

3. In the grid below, locate the row labeled, Before Insert. It should be
blank. Click anywhere in the white space to the right of the label and you
will see a downward-pointing arrow appear, indicating that this is also a
ComboBox. Click the arrow and select "Event Procedure".

4. Then, notice that there is an ellipsis or three little dots (...) to the
right of the ComboBox. Click the ellipsis and you will open a code window.
You will see that Access has given you a space for entering some code in
this event - it will look something like the following:

Private Sub Form_BeforeInsert(Cancel As Integer)

End Sub

The Before Insert event will "fire" just before a New Record is saved to the
database, so you will want to update the DateCreated field on your form.

5. After the "Private Sub Form_BeforeInsert(Cancel as Integer)" line,
insert the following code:

me!DateCreated = Date()

Save and close the module.

When you want to record the date on which an existing record was modified,
follow the steps above, except look for the Event Property named Before
Update. Select EventProcedure and click the ellipsis. You will see a code
window that looks like the following:

Private Sub Form_BeforeUpdate(Cancel As Integer)

End Sub

On a line after the "Private Sub Form_BeforeUpdate(Cancel As Integer)", just
insert the following:

me!DateUpdated = Date()

Click the Save button, but before you close the code window, look for the
Menu Item named "Debug". Click that and select the first option "Compile".
Then close the code window.

hth,
 
Open your form in Design View. From the View menu select Code. This will
open the Visual Basic Editor. There are 2 drop down lists at the top, the
left one should already have Form selected. You can select BeforeInsert or
BeforeUpdate from that list and this is where you would paste your code.

Make sure you add the DateCreated and DateUpdated fields to your table
first.

Gordon Foster
 
It works!
Thanks Cheryl and Gordon for taking the time to respond!
Much appreciated!!
Regards,
MyQ
 
I forgot....How about subforms? When I was testing the
dateupdated field, it did not update when I only made
changes to a subform within the mainform.
Help.
thanks,
MyQ
 
Back
Top