Date Modified for information in a Subform

[jmaher196]

Hello all!

I have a customer relationship management database that I am having a small problem with. I have built a dashboard for the employees to use, and the dashboard contains a subform. The subform is a query of any projects marked as Priority "1" and it contains about 20 records. I have added a DateModified field to the table that is used by multiple forms to record the most recent date that record was changed.

My problem now is to figure out how to update that field when record changes are made in the subform on the dashboard. So if one of the users edits the information for one of the projects in the subform, it will change the DateModified field basically. Any ideas?

Thanks in advance for your help!

[TheSmileyCoder]

Well presumably you have a foreign key linking the items in the subform to the main item. Using the AfterUpdate event of the subform, you could run a update query:

Private Sub Form_AfterUpdate()
  Currentdb.Execute "UPDATE tbl_Main SET DateModified=Now() WHERE PrimaryKeyName=" & Me.ForeignKey
End Sub

Im not 100% sure on the SQL syntax, its really not my strong suit, but the example should still outline the general principle. Whether you want to use Now() or Date() depends on your needs. I prefer to use NOW() but often just format the information to only display the date. That way I have the accurate information in the table while storing as much detail as possible.

[jmaher196]

Thank you for your quick reply! I will make the changes this afternoon and see if they work. I can give you feedback tomorrow. Thanks again!

[TheSmileyCoder]

Your welcome.

[NeoPa]

First, it's very important to decide whether your date field is a date field or a timestamp field. My advice differs here from Smiley's as I believe it's always important to know the difference and treat them differently. So many people get confused about this and fail to get their projects working properly when they try to treat dates as timestamps and vice-versa. If I'm working with simple date values and want to ignore anything after today I can simply say X <= Date(). If X turns out to be a timestamp value though, this will ignore anything from today too (as a timestamp from today (except on the stroke of midnight) is numerically greater than the date value on its own).

I'll continue on the assumption that you're working with dates specifically, as indicated by the question.
Typically, a suggestion just as Smiley has given would be recommended at this point. However, there is a way that allows the updates to be atomic, and therefore less exposed to partial updates. Let me outline that for you.

When you are processing the save you check the date value of the record. If it is not set to today then you need to do three things in succession.

1. Cancel the current update.
2. Set the date value to Date().
3. Resubmit the update.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    With Me
        If Format(Nz(.DateField, ""), "yyyymmdd") = _
           Format(Date(), "yyyymmdd") Then Exit Sub
        Cancel = True
        .DateField = Date()
        Call DoCmd.RunCommand(acCmdSaveRecord)
    End With
End Sub

This way only a single (atomic) update is done, which includes all required changes together.

[TheSmileyCoder]

Hi NeoPa. You have some good points about querying the date.

I wonder however, if you missed the part about the datefield being updated belongs to the main (parent) form and not the subform which is being updated?

Furthermore should you require the setting of fields in a forms beforeupdate event, you do not need to cancel the update first.

Private Sub Form_BeforeUpdate(Cancel As Integer)
  Me.txt_DateChanged=Now()
  Me.txt_ChangedByID=UserID()
End Sub

Is a code I use many forms, without first cancelling the update. I would be interested in knowing if you have any cases in which a cancel of the update is required before resubmitting it?
I know if you want to UNDO a entry while in the forms beforeUpdate event, it is necessary to cancel it first.

[NeoPa]

Smiley:
I wonder however, if you missed the part about the datefield being updated belongs to the main (parent) form and not the subform which is being updated?

Very likely. Especially as I don't see that even now when I look. To be fair, the explanation isn't too clear and I could be wrong, but I still interpret the question to refer to changes within the same record (which makes sense if you think about it). On the other hand, if that's not a correct interpretation, which frankly is quite possible, then my suggestion wouldn't be appropriate.

Smiley:
Furthermore should you require the setting of fields in a forms beforeupdate event, you do not need to cancel the update first.

You're probably correct I expect. I'd recently tried something along similar lines and found I had to, but when I tried to dig it up to check the details (for the post) I couldn't remember which database it was even in :-(

[jmaher196]

Sorry for the confusion, I will clarify some things. The field being used on the main table is a Date/Time field, using mm/dd/yyyy format. The subform on the dashboard is a query that pulls up all of the records that have been marked as a priority 1 so the user can have a quick list of all of the important ones. The user will then proceed to provide updates to individual records within the subform. Not all records will be updated. What I want to do is have the DateModified field for each individual record change to the current date once that record has been updated. Again, sorry for the confusion

[TheSmileyCoder]

If the date field is part of the recordsource for the subform, then add code to the subforms BeforeUpdate event as shown in #6, otherwise the approach from #2.


Remember that a dataField stores a date (And possibly a time as well). The format is merely how it is displayed. Consider that the information is stored the same way whether you are in the US or in europe, but in place its formatted mm/dd/yyyy and in another its dd/mm/yyyy. The storing is independent of the format AS LONG as you use the date time field, and not a text field.