Synchronizing Tab forms Sub forms.

[joel009]

I have a main form which shows Route details and a tab control on the form that displays Parts on the Route. There are 4 tabs each with a subform. I have all the subforms displaying the correct records in the correct order. What I would like to do is - if the user selects a row (part number) on a tab(subform) and then changes tabs to have the same row(part number) selected. I have been trying to use the main form detail tag to store the current row part number but that is not working, or at least I can't figure it out.
Would someone with experience with this please point me in a direction?
I thought it would be simple but sheesh.
I use the main forms tab control change event to do some things and figure that is where I need to set this up.

Private Sub tabctlBaseRouteAssignment_Change()

intTabPage = Me!tabctlBaseRouteAssignment

If intTabPage = 0 Then
cmdRecalcMinutes.Visible = True
cmdDelRouteAssignment.Visible = True
cmdReassignPartRoute.Visible = True
cmdAddToRoute.Visible = True
cmdUpdateStation.Visible = True
Else
cmdRecalcMinutes.Visible = False
cmdDelRouteAssignment.Visible = False
cmdReassignPartRoute.Visible = False
cmdAddToRoute.Visible = False
cmdUpdateStation.Visible = False
End If

End Sub

Thanks!

Joel

 

[MajP]

This is hard to say, because you do not explain how these subforms are related to each other or if there are any links already. I will assume they all have a related field called partNumber.

However, I would likely have a hidden control on the main form. Call it "txtBxLink". On the current event of each subform I would have

me.Parent.txtBoxLink = me.YourPartNumberField

Now you can simply link every subform to the textbox in the mainform. Something like
LinkMasterFields: [txtBoxLink]
LinkChildFields: [partNumber]

This should synch every subform with each other.

or you could write code on the tab afterupdate event, to move to the record stored in the txtBoxLink

 

[joel009]

Hi MajP!
Well, I looked at it for a couple hours, then posted this, then figured it out just like you said with a little difference. Please let me know if you think my solution will cause problems later.

Like you said, I created a text box on the main form and in the current event of all subforms I have your exact code (I would say great minds think alike but do not wish to offend .

To finish I placed this in the tab controls change event:

Private Sub tabctlBaseRouteAssignment_Change()

intTabPage = Me!tabctlBaseRouteAssignment

If intTabPage = 0 Then
cmdRecalcMinutes.Visible = True
cmdDelRouteAssignment.Visible = True
cmdReassignPartRoute.Visible = True
cmdAddToRoute.Visible = True
cmdUpdateStation.Visible = True
Else
cmdRecalcMinutes.Visible = False
cmdDelRouteAssignment.Visible = False
cmdReassignPartRoute.Visible = False
cmdAddToRoute.Visible = False
cmdUpdateStation.Visible = False
End If

Select Case intTabPage
Case 0
[Forms]![frmBaseRouteAssignment]![subfrmRouteEfficiencyDetail].[Form].Recordset.FindFirst "PartNumber = " & "'" & Me.txtCurPartNumber & "'" & ""
Case 1
[Forms]![frmBaseRouteAssignment]![subfrmViewLocations].[Form].Recordset.FindFirst "PartNumber = " & "'" & Me.txtCurPartNumber & "'" & ""
Case 2
[Forms]![frmBaseRouteAssignment]![View Times].[Form].Recordset.FindFirst "PartNumber = " & "'" & Me.txtCurPartNumber & "'" & ""
Case 3
[Forms]![frmBaseRouteAssignment]![View Travel Detail].[Form].Recordset.FindFirst "PartNumber = " & "'" & Me.txtCurPartNumber & "'" & ""
Case Else
Exit Sub
End Select

End Sub

It so far appears to be working fine. Whatever field on the line I click on in the subform it populates the text box on the main form and when I change tabs the same PartNumber is selected.

I might be able to clean it up a bit but it's working.

Thanks for the input!!!


Joel

 

[MajP]

Yep, that pretty much what I would have done, but I am lazy so:

 dim ctrl as access.control
 dim strWhere as string
 strWhere = "PartNumber = '" & Me.txtCurPartNumber & "'"
 for each ctrl in me.controls
  if ctrl.controltype = acsubform then
    ctrl.form.recordset.findfirst strWhere
  end if
 next ctrl

I can then keep adding subforms without moding the code. And do not need the select case because I will just synch them all.

 

[joel009]

much more dynamic. Thanks MajP!!

Joel