"Date updated" in "one" record any time a "many record" changes?

[Elizabeth Swoope]

This is probably an easy one, but I'll be darned if I can figure it out!

I have a table with one record per student and another table with one record
for each class a student has or will take. I have a "date updated" field in
the student table that correctly changes to the current date whenever the
record is modified in any way.

However, I also want the "date updated" field to change whenever any change
is made to the "child" records, whether it's a new record, a record that's
deleted, a grade or note added, etc.

How do I get the "date updated" field in the students table to change any
time a record in the courses table changes?

 

[Elizabeth Swoope]

Additional info. The data is being entered in a form (for the student record)
with a continuous subform (for the courses for that student) and I am using
various event procedures to handle the data.

 

[Graham Mandeno]

Hi Elizabeth

Add the following function to the module of your subform:

Private Function UpdateChangeDate()
Dim strSQL as String
strSQL = "Update [StudentTable] set [DateUpdated]=Now() " _
& "where [StudentID]=" & Me![StudentID]
CurrentDb.Execute strSQL, dbFailOnError
End Function

(You should change the bits in square brackets to match your own object
names and add appropriate error handling code)

Now, call this function from Form_AfterUpdate and Form_AfterDelConfirm
(where Status = acDeleteOK).

 

[Elizabeth Swoope]

Hi Elizabeth

Add the following function to the module of your subform:

Private Function UpdateChangeDate()
Dim strSQL as String
strSQL = "Update [StudentTable] set [DateUpdated]=Now() " _
& "where [StudentID]=" & Me![StudentID]
CurrentDb.Execute strSQL, dbFailOnError
End Function

(You should change the bits in square brackets to match your own object
names and add appropriate error handling code)

Now, call this function from Form_AfterUpdate and Form_AfterDelConfirm
(where Status = acDeleteOK).
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

I'm sure your code works and I've just done something wrong (and thanks so
much for your quick reply).

The "Update" field in tblStudents does not change, regardless of whether I
delete records in tblStudentCourses or add them. I also don't get an error
message. The linking field is ID.

Here's my code:

Private Function UpdateChangeDate()
Dim strSQL As String
strSQL = "Update [tblStudents] set [Updated]=Now() " & "where [ID]=" _
& Me![ID]
CurrentDb.Execute strSQL, dbFailOnError
End Function

Private Sub Form_AfterDelConfirm(Status As Integer)
UpdateChangeDate
Status = acDeleteOK
End Sub

Private Sub Form_AfterUpdate()
UpdateChangeDate
End Sub

First thing I'll admit is that I have no idea whether I'm calling the
procedure correctly. I have programming experience in dBase II, Paradox, and
SAS and understand the concept of procedures, but VB is absolutely alien to
me. I have four books that I'm using to try to muddle through this simple
one-user app and I'm not muddling very well.

Again, thanks.

liz

 

[Graham Mandeno]

Hi Liz

The first thing to check is that your code is actually being executed.

Insert these two lines temporarily just before CurrentDb.Execute:

Debug.Print strSQL
Stop

If the code is actually being executed then it should pause at that point
and display the SQL string you are about to execute in the Immediate window.
Check this string for valid syntax, and check that the correct ID value is
in the string.

If the code doesn't get executed at all, then check the AfterUpdate and
AfterDelConfirm properties of your form to make sure they are both set to
[Event Procedure].

Also, you want to update the student record only if the delete is
successful, so your AfterDelConfirm code should be:

If Status = acDeleteOK Then UpdateChangeDate

 

[Klatuu]

To see the changes on your sub form, you will need to requery the subform
after you update the dates.

Hi Elizabeth

Add the following function to the module of your subform:

Private Function UpdateChangeDate()
Dim strSQL as String
strSQL = "Update [StudentTable] set [DateUpdated]=Now() " _
& "where [StudentID]=" & Me![StudentID]
CurrentDb.Execute strSQL, dbFailOnError
End Function

(You should change the bits in square brackets to match your own object
names and add appropriate error handling code)

Now, call this function from Form_AfterUpdate and Form_AfterDelConfirm
(where Status = acDeleteOK).
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

I'm sure your code works and I've just done something wrong (and thanks so
much for your quick reply).

The "Update" field in tblStudents does not change, regardless of whether I
delete records in tblStudentCourses or add them. I also don't get an error
message. The linking field is ID.

Here's my code:

Private Function UpdateChangeDate()
Dim strSQL As String
strSQL = "Update [tblStudents] set [Updated]=Now() " & "where [ID]=" _
& Me![ID]
CurrentDb.Execute strSQL, dbFailOnError
End Function

Private Sub Form_AfterDelConfirm(Status As Integer)
UpdateChangeDate
Status = acDeleteOK
End Sub

Private Sub Form_AfterUpdate()
UpdateChangeDate
End Sub

First thing I'll admit is that I have no idea whether I'm calling the
procedure correctly. I have programming experience in dBase II, Paradox, and
SAS and understand the concept of procedures, but VB is absolutely alien to
me. I have four books that I'm using to try to muddle through this simple
one-user app and I'm not muddling very well.

Again, thanks.

liz

 

[Elizabeth Swoope]

Graham,

I still feel like I'm swimming in cold molasses, but this helped a lot
(never mind my clumsiness with dealing with the VBA editor...).

One probably had to do with my ID being text rather than numeric, so I had
to modify the strSQL statement so that single quotes enclosed the ID. Once I
did that, the date updated properly when I added or changed records.

I had turned off the confirming of deletes, so the date wasn't updating when
I deleted records, but I turned it back on and it works. The person who uses
this database doesn't have deletion confirmation turned off so it won't be an
issue for her.

I really appreciate your solving this problem. That's two down and one to go!

Is there any chance you might have some useful input on the question I
posted in the Forms Design area? For some reason, my continuous subform will
not keep growing downwards as I add records. It will not display any more
"many" records than were showing when I accessed the main record. If I add
records, the old records scroll off the top rather than the new records being
added at the bottom and the subform growing downwards. If I can resolve that
problem, I'm pretty much done with this little project.

Once again, thanks!

liz

 

[Graham Mandeno]

Hi Liz

Well done! It's amazing how the solution can become obvious if you look at
a few things in debug mode

Klatuu has triggered a thought:- if the DateUpdated field is displayed on
your main form then you will not see the change immediately without
requerying the form. In this case, you would be better to do it this way:

With Me.Parent
!DateUpdated = Now
.Dirty = False
End With

I'll take a look at your other post.

 

[Elizabeth Swoope]

Graham,

The Updated field is on the main form only; there aren't any date fields on
the course records (the subform). It's working fine without requerying,
although I understand the concept. Several of the books I'm using as
reference books show that very thing for displaying a total on the main form
for a group of records in the subform.

I see that I have answers to another couple of questions waiting for me so
maybe I can get this project out of my hair soon.

Thanks to both of you for your help on this.

liz