By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,671 Members | 1,278 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,671 IT Pros & Developers. It's quick & easy.

Date Modified for information in a Subform

P: 13
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!
Oct 22 '12 #1
Share this Question
Share on Google+
8 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_AfterUpdate()
  2.   Currentdb.Execute "UPDATE tbl_Main SET DateModified=Now() WHERE PrimaryKeyName=" & Me.ForeignKey
  3. 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.
Oct 22 '12 #2

P: 13
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!
Oct 22 '12 #3

TheSmileyCoder
Expert Mod 100+
P: 2,321
Your welcome.
Oct 22 '12 #4

NeoPa
Expert Mod 15k+
P: 31,276
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.     With Me
  3.         If Format(Nz(.DateField, ""), "yyyymmdd") = _
  4.            Format(Date(), "yyyymmdd") Then Exit Sub
  5.         Cancel = True
  6.         .DateField = Date()
  7.         Call DoCmd.RunCommand(acCmdSaveRecord)
  8.     End With
  9. End Sub
This way only a single (atomic) update is done, which includes all required changes together.
Oct 24 '12 #5

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.   Me.txt_DateChanged=Now()
  3.   Me.txt_ChangedByID=UserID()
  4. 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.
Oct 24 '12 #6

NeoPa
Expert Mod 15k+
P: 31,276
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 :-(
Oct 24 '12 #7

P: 13
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
Oct 26 '12 #8

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Oct 26 '12 #9

Post your reply

Sign in to post your reply or Sign up for a free account.