Help Troubleshooting Before Update event - set [LastUpdate] to Now

[Ruth]

I've run into a problem getting a Before Update event to launch. I'm
trying to update the LastUpdate field with the current date and time
when data on a form is changed.

The control is on a subform, which is also located on a tab control.

I've checked the code on a "plain" form so I know it works, but I'm
missing something when trying to make it work on the subform.

Form Name: frmVisits
Subform Name: frmVisitOrgTab
Tab Name: pgVisitInfo
Control Name: LastUpdate (formatted as General Date)

Current Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Me![frmVisitOrgTab]![LastUpdate].Value = Now

End Sub

I've tried several different variations to refer to the control, but
nothing so far has changed the date! I have tried attaching the code
to both the main form and the subform. It's currently attached to the
main form.

Other variations already tried:
Me. LastUpdate.Value
Me![frmVisitOrgTab].Form![LastUpdate]
Forms![frmVisits].[Form]![frmVisitOrgTab].[Form]![LastUpdate]

I've also tried to accomplish with a macro. No luck.

Ideas, suggestions, guidance will be appreciated. Thanks!

Ruth

 

[Steve]

Your problem is a common error made by Access users! When referring to a
control on a subform, you need the name of the subform control on the main
form in the expression not the name of the subform. Open frmVisits in design
view. Select the border around the subform. This is the subform control.
Open properties and go to the Other tab. Note the name of the subform
control. Say the name is "RuthHays". To refer to LastUpdate on the subform
if your code is in the main form , the expression is:
Me!RuthHays.Form!LastUpdate
If your code is exyernal to the main form:
Forms!frmVisits!RuthHays.Form!LastUpdate

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Douglas J. Steele]

The code should be in the BeforeUpdate event of the form being used as a
subform. That means you'd simply refer to Me!LastUpdate.

Putting the code in the BeforeUpdate of the parent form does nothing: as
soon as focus moves from the subform to the parent form, the data on the
subform is saved (and vice versa)

 

[Ruth]

Nope. That didn't fix it. You were right about the subform name. I
don't think I ever knew (or had an issue with) that Access gives the
subform a "control" name. I don't write code, so I guess it's never
come up.

I tried your both of your naming conventions and neither worked.

Any other ideas? Anyone?

Thanks!

Ruth

 

[Ruth]

Hi Doug,
I was trying what Steve suggested when you replied. Thanks for the
suggestion! It worked like a charm! (And I could have sworn that I
tried that before!)

Thank you, thank you, thank you!

Ruth