CODEWITHSUNDEEP
formsms-accessms-access-2010subforms
freesoft
freesoft

Reputation: 1144

Access VBA: A subform can't get values from other subform

I have 3 forms. A form is the parent, and the others are inserted as subforms of the parent:

     f_p            form parent
    /  \
f_c1    f_c2        forms childs 1 and 2

I have programmed the "Current" event in form f_c1, so when the user changes the selected row in f_c1, some data should be copied to f_c2:

Private Sub Form_Current()

    Me.Parent("f_c2").Form("field_in_f_c2") = Me("field_in_f_c1")

End Sub

But that returns an error 2455:

You specified an expression that contains a non valid reference to the Form/Report property.

The error seems to be in the ".Form" part, since this works:

Debug.Print Me.Parent("f_c2").Name

but this doesn't work:

Debug.Print Me.Parent("f_c2").Form.Name

In other words, it looks like it is not possible to get into a child of the parent.

What can be happening?

Note: f_c1 is bound to the parent via a common field, f_c2 is not bound to the parent. I use f_c2 just as a scrollable container of controls.

Note 2: I have made a test: In a new blank form ("f_p_test"), I have inserted the form f_c2 two times ("f_c2_a" and "f_c2_b"). I have configured the "Click" event on a checkbox of f_c2 (so the event is available in both f_c2_a and f_c2_b). In this test, I can access f_c2_a fields from f_c2_b fields, and vice-versa.

Update: I've just tried a trick, but it doesn't work: I have added a button to the parent form, and asigned an event to it, so when I click the button, the values of "f_c2" textboxes are shown. The results: If I manually click on the button, then the values are shown. But, if I add a click simulation to the "Current" event of form f_c1, then the information is not shown, the same old error happens. Incredible!

Update 2: Solved, it was a race condition. The parent form is opened by other form, with:

DoCmd.OpenForm "f_p"

so the children (subforms f_c1 and f_c2) are opened just after the parent, but the children are not loaded at the same time. When child f_c1 is loaded, its "Current" event is run, and it tries to access to f_c2, but f_c2 is still not loaded, so the folloging code fails:

Private Sub Form_Current()                  ' "Current" event for f_c1
    Dim f_c2 As Form
    set f_c2 = Me.Parent("f_c2").Form       ' f_c2 could be not loaded
    ' rest of code
End Sub

The solution is to detect that f_c2 is still not loaded:

Private Sub Form_Current()                  ' "Current" event for f_c1
    Dim f_c2 As Form
    On Error Resume Next
    Set f_c2 = Me.Parent("f_c2").Form
    If Err <> 0 Then                        ' if f_c2 is still not loaded, then return
        Exit Sub
    End If
    On Error GoTo 0
    ' rest of code
End Sub

Fortunatelly, the f_c1's Form_Current() event is called two times automatically when the parent form f_p is opened: The first time, f_c2 is still not loaded; the second time, f_c2 is already loaded, so f_c1 data is successfully copied to controls of f_c2.

Upvotes: 1

Views: 3099

Answers (1)

E Mett
E Mett

Reputation: 2302

The subforms are sitting in subform (or child) control.

When you want to reference the subforms you must do it via the subform control.

Instead of: Me.Parent("f_c2").Form("field_in_f_c2")

It should be: Me.Parent.<name of subform control>.Form.field_in_f_c2

Upvotes: 1

Related Questions