automatic update to a field (Date/Time)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi:
Is there a wa to add an automatic update to a field (Date/Time) in a table
when any edit or change is made to the existing fields in the table?
When someone goes into an existing table to make a change to any field, the
field Date/Time will be updated with the Date andTime. These are tables that
already have existing data, the only blank field is the new Date/Time.
 
Add 2 Date fields to your table.
DOC (DateOfCreation)
DOLE (DateOfLastEdit)
On the form's BeforeUpdate event...

Private Sub Form_BeforeUpdate(Cancel As Integer)
If (IsNull(DOC)) Then
DOC = Date
DOLE = Date
Else
DOLE = Date
End If
End Sub

This will set DOC to the date you created the record, and DOLE every time
you edit it.
hth
Al Camp
 
Thank you for this information. I have created the 2 fields in my table.
Currently, that table is opened via a qry that opens the table by slecting a
comman button from the form. I unsure where the remaining piece is
entered...is it a default value on the table for the 2 new fields?
 
Hi. Thank you for this info. The 2 new flds in the tbl have been created.
Currently that table is opened by a open qry via a command button on the
form. I'm not familar with the BeforeUpdate, etc. Is this entered in the
def. value of the 2 new fields created?
 
Here is the command:
Private Sub Command31_Click()
On Error GoTo Err_Command31_Click

Dim stDocName As String

stDocName = "A01_G00_qry"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command31_Click:
Exit Sub

Err_Command31_Click:
MsgBox Err.Description
Resume Exit_Command31_Click

End Sub
 
CP,
I'm having a bit of trouble understanding your question...
Are you having trouble understanding how to use my code when the Form's
BeforeUpdate event occurs??

Make sure that the 2 fields (DOC and DOLE) I asked you to add are
included in the query behind your form.
Place them on the form, and give them a ControlSource of [DOC] and
[DOLE] respectively.
1. On the properties for the Form itself, find the Before Update event.
2. Place the cursor in the BeforeUpdate box.
3. See a little arrow on the right? Click it and select Event
Procedure.
4. See a little box on the right with 3 dots? Click it.
5. You should see...

Private Sub Form_BeforeUpdate(Cancel As Integer)

End Sub

6. Now place my code between these two lines...

Private Sub Form_BeforeUpdate(Cancel As Integer)
If (IsNull(DOC)) Then
DOC = Date
DOLE = Date
Else
DOLE = Date
End If
End Sub

That's it. Try creating a new record, and watch DOC get updated with
today's date. Leave that record and come back, edit any field on the form,
and watch DOLE get updated to today's date. And every time you edit that
record again, DOLE weill continue to update correctly.

hth
Al Camp
 
CP,
Your form's RecordSource property should be set to the name of a table or
a query that will produce the recordset of records you want to work on.
I don't understand what you're doing by clicking a button and opening a
query? The table or query indicated in your form RecordSource is all you
need to provide your data to the form.

Please see my response to your previous post, and let's try to stay with
one problem at a time.
Be patient... if someone can't respond to your question right away, don't
repost the question. That won't speed up the process, and will only add to
the confusion.
hth
Al Camp
 
I tried this and it worked for the DOC but not for the DOLE. I changed it
slightly as follows and then it worked for both the DOC and the DOLE.
Thanks.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If (IsNull(DOC)) Then
DOC = Date
DOLE = Date
Else
DOLE = Now()
End If
End Sub


AlCamp said:
CP,
I'm having a bit of trouble understanding your question...
Are you having trouble understanding how to use my code when the Form's
BeforeUpdate event occurs??

Make sure that the 2 fields (DOC and DOLE) I asked you to add are
included in the query behind your form.
Place them on the form, and give them a ControlSource of [DOC] and
[DOLE] respectively.
1. On the properties for the Form itself, find the Before Update event.
2. Place the cursor in the BeforeUpdate box.
3. See a little arrow on the right? Click it and select Event
Procedure.
4. See a little box on the right with 3 dots? Click it.
5. You should see...

Private Sub Form_BeforeUpdate(Cancel As Integer)

End Sub

6. Now place my code between these two lines...

Private Sub Form_BeforeUpdate(Cancel As Integer)
If (IsNull(DOC)) Then
DOC = Date
DOLE = Date
Else
DOLE = Date
End If
End Sub

That's it. Try creating a new record, and watch DOC get updated with
today's date. Leave that record and come back, edit any field on the form,
and watch DOLE get updated to today's date. And every time you edit that
record again, DOLE weill continue to update correctly.

hth
Al Camp


CP said:
Thank you for this information. I have created the 2 fields in my table.
Currently, that table is opened via a qry that opens the table by slecting
a
comman button from the form. I unsure where the remaining piece is
entered...is it a default value on the table for the 2 new fields?
Click to expand...
Click to expand...
 
Back
Top