Parent Form navigation, controlled by Subform

[mlesniak001]

Hi everyone,

I've been banging my head against a wall over this problem, and am about to give up. but I hope that someone can help me with this one.

the idea is simmilar to thread

http://www.tek-tips.com/viewthread.cfm?qid=768863.

basically, I have a parent form (form_A) with 2 subforms (form_B and form_C).

form_A has the following data fields:
KPI, Dimension

form_B has the following data fields:
Dimension

form_C has the following data fields:
Dimension, Parameter


together, the user can see all the Dimensions for a KPI, and the Parameters that are in the Dimension.

the problem is that (without any VBA), the user has to manually navigate form_A to see the correct set of Parameters in form_C. It would be much nicer if form_C would update, as the user navigates form_B. But, since form_B is shown as a datasheet, I cannot make form_C a subform of form_B.

So far, I have been able to force navigation of form_A from form_B - but there is a big problem. for now, here is the VBA that is launched, when the focus is set to form_B, field Dimension:

Private Sub Dimension_GotFocus()
    Dim rs As Recordset
    Dim temp As Integer
    If Me.NewRecord = 0 Then
        Set rs = Me.Parent.RecordsetClone
        rs.MoveFirst
        rs.FindFirst "[Dimension] = " & Me![Form_B.Dimension]
        temp = rs.AbsolutePosition
        Me.Parent.Recordset.MoveFirst
        Me.Parent.Recordset.Move temp
    End If
End Sub

Here is the problem: as soon as form_A updates, so does form_B, thereby relaunching the VBA using the first row in the list (this re-launch actually happens twice, though I'm not sure why). If this re-launcing of the VBA could be suppressed, then it would work fine.

then again, maybe I have the code in the wrong place (currently "On GetFocus" for the field). or maybe the Parent_form + 2 subforms design simply will not work.

any advice is greatly appreciated.

Cheers!

 

[Remou]

Hi
If I have understood your problem, I think you will find this faq702-5860 useful. It offers a different approach to yours.