Date/Time Update

[dr223]

I have a form which is also build with a subform. In the form I add the date and time when a record is changed to a respective table in the database. So that, when the next administrator logs in he can see when the form was last updated. I used the following code:

Private Sub Form_BeforeUpdate(Cancel As Integer) On Error GoTo BeforeUpdate_Err
' Set bound controls to system date and time.

DateModified = Date
TimeModified = Time()
BeforeUpdate_End:
Exit Sub

BeforeUpdate_Err: MsgBox Err.Description, vbCritical & vbOKOnly, _ "Error Number " & Err.Number & " Occurred"
Resume BeforeUpdate_End
End Sub

This works successfully, and imports the date and time when a record has been last modified or added. The problem I am faced with is that, whenever I change the fields within the subform, the date and time is not updated to reflect to the change. How can I rectify this?

Thank you

 

[RuralGuy]

Are you aware then one field set to Now() will record both date *and* time and save disk space and cpu cycles? If you want to time stamp the RecordSource of the SubForm then you will need similar code in the form displayed in the SubFormControl.

 

[rainman89]

also if you place the code in the afterupdate of the field then it will record the change

 

[boblarson]

And, trust me on this one (I learned the hard way) you want ONE date/time field which records date and time (so NOW would be good). You can then do all of the formatting, date calculations etc. on it that you want. If you keep them separate, it makes figuring out those things worse because you just have to put them back together again.

 

[dr223]

date/time update

Thank you for your suggestions, I tried to add the same code in the subform control before Event update and after Event update. No luck!!! what wrong am I doing ?

Many thanks

 

[boblarson]

What IS or ISN'T it doing? When you say it isn't working, what is "not working" specifically?

 

[dr223]

Hi,

It doesnt display the date and time when a change is made on the subform.

Sorry for not being clear in the initial statement

Thanks

 

[RuralGuy]

It would be useful if we knew the RecordSource of both the MainForm and the SubForm.

 

[boblarson]

I'm also wondering if you have actually put the code in the event or actually just typed it into the event PROPERTY of the form. So, just verify for me that you have actually put the code in like this:
http://www.btabdevelopment.com/main/QuickTutorials/Wheretoputcodeforevents/tabid/56/Default.aspx
(of course in the appropriate event - the example shows CURRENT but that isn't where you want it)

Also, you aren't by chance running Access 2007 and need to do this:

http://www.btabdevelopment.com/main...owtoenablecodeandmacros/tabid/57/Default.aspx

 

[RuralGuy]

Hi,

It doesnt display the date and time when a change is made on the subform.

Sorry for not being clear in the initial statement

Thanks

Do the MainForm and the SubForm share the same RecordSource? If not, are you trying to timestamp the MainForm RecordSource when changes are made to the SubForm? If they do not have the same RecordSource, wouldn't there be two different timestamps?

 

[dr223]

The Recordsource for the form is StudentDetails and for the subform is FeesDetails

I have created a form with 4 fields Student_Id, Surname, Forename, AOS_Code, NumHus
(Stored in Student Details)
with a subform built up of the Payer Code, Payer Name, Details, Amount (Stored in FeeDetais).

Then I created a relationship between the two tables and whenever a respective student_Id is called the other details in the subform are displayed on the form for view / amendments.

When amendments are done on the subform the effect doesnt take place on the date/time update field


thank you

 

[dr223]

Thank you guys, it is working now.

Many thanks to boblarson, you are a star!!!!!