Search code examples
vbams-accessms-access-2007subformbeforeupdate

How to set the Before Update property via VBA in Access 2007

I have a subform that changes its record source (Form.SourceObject) based on user input. I am developing a change tracking system that relies on the Before Update property of certain fields in the subform (in sheet view if it makes a difference). If I don't change the record source, the Before Update will trigger appropriately. If I do change it, it's lost and I need to reassign the trigger. Problem is, I have not been able to find anything describing how this can be done. I have tried using the following where "Tracker" is the code to be called:

Forms![PartsDatabaseX]![RepsSubformX]![Pack Rank].BeforeUpdate = "[Event Procedure]"

Error 2455 = "You entered an expression that has an invalid reference to the property BeforeUpdate"

Forms![PartsDatabaseX]![RepsSubformX]![Pack Rank].BeforeUpdate = "Tracker"

Error 2455 = "You entered an expression that has an invalid reference to the property BeforeUpdate"

Forms![PartsDatabaseX]![RepsSubformX]![Pack Rank].BeforeUpdate(Tracker)

Compile error = Expected Function or variable

Forms![PartsDatabaseX]![RepsSubformX]![Pack Rank].BeforeUpdate  "Tracker"

Error 438 = "Object doesn't support this property or method"

Edit-

After playing around with it more, I can assign the BeforeUpdate property to controls in the form properly assuming the corresponding before update event is present:

Private Sub Assign_Before_Update()
Me.FormCtl.BeforeUpdate = "[Event Procedure]"
End Sub

Private Sub FormCtl_BeforeUpdate(Cancel As Integer)
'Do stuff
End Sub

Unfortunately this method still fails with the subform...

I'm starting to think that the fact that I am changing the Form.SourceObject is what's blocking me from setting the property.

Solution

  • When you want to reach controls on a subform, you need to use the .Form property of the subform control.

    Forms![PartsDatabaseX] reaches the main form.

    Forms![PartsDatabaseX]![RepsSubformX] reaches the subform control. The subform control has properties like SourceObject, LinkMasterFields, and LinkChildFields, but doesn't have any controls of its own.

    Forms![PartsDatabaseX]![RepsSubformX].Form reaches the subform as a true Form object.

    Forms![PartsDatabaseX]![RepsSubformX].Form![Pack Rank] reaches the control on the subform.

    Forms![PartsDatabaseX]![RepsSubformX].Form![Pack Rank].BeforeUpdate reaches the control's event property.

    I'd recommend defining your behavior inside a Function (not a Sub) because then you can call that function directly from a control's event property using the syntax

    Control.BeforeUpdate = "=MyFunction()"

    or

    Forms![PartsDatabaseX]![RepsSubformX].Form![Pack Rank].BeforeUpdate = "=MyFunction()"