• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1239
  • Last Modified:

Synchronised scrolling of subforms on tab control in Access 2007

Hi All,

I had a continuous form in Access 2007 which had too many fields to be displayed within the 55cm limit so I thought that I would make a tab control with fourteen tabs.
Each tab has a continuous subform with 5 or 6  fields in it.
The first field of each subform is WTD_Test_Date.
All subforms get their data from the same query so they all have the same list of WTD_Test_Date.

I would like to be able to either scroll one subform and when I click on other tabs have them already at the same position as the one I scrolled on or ...
preferrably, be able to click on a WTD_Test_Date and when I look at other tabs have them already focussed on the same record.

I have seen this done if the subforms are on the main form rather than a tab control :

On Current:
Me.Parent.subFrm2.Form.Recordset.FindFirst ("WTD_Test_Date = " & Me.WTD_Test_Date)

but am at a loss as to adapting this to the tab control.

Thanks for any suggestions,
Stuart
0
DiscoStubacca
Asked:
DiscoStubacca
  • 6
  • 4
1 Solution
 
Rey Obrero (Capricorn1)Commented:
the tab controls have  does not effect the codes that refers to the subform placed to it..

try this

with Me.Parent.subFrm2.Form.Recordset
          .FindFirst ("WTD_Test_Date = " & Me.WTD_Test_Date)
          Me.Parent.subFrm2.Form.bookmark=.bookmark
 

end with
0
 
DiscoStubaccaAuthor Commented:
I tried:

    With Me.Parent.Child138.Form.Recordset
          .FindFirst ("WTD_Test_Date = " & Me.WTD_Test_Date)
          Me.Parent.Child138.Form.Bookmark = .Bookmark
    End With

in the On Current event of the 1st subform and receive this on opening the form:

    Error 2455 (You entered an expression that has an invalid reference to the property        Form/Report.) in procedure Form_Current of VBA Document Form_WTD Report Subf Dam 1

That second line inside the With .... End With doesn't look right??

Cheers,
Stuart
0
 
Gustav BrockCIOCommented:
Here is how to do this.

On the main form, place a textbox, say, txtSyncSubforms.
Apply this control source to it:

=SyncSubforms([subControlFirst]![ID],[subControlSecond]![ID], .., [subControlLast]![ID])

replacing subControlxxxx and ID with the actual names of your subform controls and the ID which, of course, must be unique.

Behind the form add this code:
Option Compare Database
Option Explicit

' Sync multiple subforms.
' 2012-06-27. Cactus Data ApS, CPH

    ' Index for Split to separate the name of the subform control from
    ' the name of the control with the key.
    '   [subControlAny]![ID]
    ' will be split into:
    '   [subControlAny]
    ' and:
    '   [ID]
    Enum ControlName
        SubForm = 0
        Key = 1
    End Enum

Private Function SyncSubforms(ParamArray sControls() As Variant) As Variant

' Array sControls() holds the values of the key controls on the subform controls
' to be held in sync.

    ' Name of visible textbox on main form bound to this function.
    Const cControl  As String = "txtSyncSubforms"
    
    ' Static to store the value of the key of the last synced record.
    Static wLastID  As Variant
    
    Dim rst         As DAO.Recordset
    Dim wSubform    As Form
    
    ' Array to hold the names of the subform controls and key controls.
    Dim aControls() As String
    
    Dim bmk         As Variant
    Dim wNew        As Boolean
    Dim wThisID     As Variant
    Dim wIndex      As Integer
    
    ' If any key value is Null, we have moved to a new record.
    ' No syncing shall take place.
    For wIndex = LBound(sControls()) To UBound(sControls())
        wThisID = sControls(wIndex).Value
        If IsNull(wThisID) Then
            ' New record. Don't sync.
            wNew = True
            Exit For
        ElseIf IsNull(wLastID) Then
            ' Initial opening of form.
            ' Set wLastID to the value of the current key of the first subform.
            wLastID = wThisID
            ' Stop further processing.
            wNew = True
            Exit For
        ElseIf wThisID <> wLastID Then
            ' This key is the new value to sync the other subforms to.
            ' Store the current key.
            wLastID = wThisID
            Exit For
        End If
    Next
    
    If wNew = True Then
        ' New record or initial opening. Do nothing.
    Else
        ' ControlSource of cControl will read like:
        '   =SyncSubforms([subControlFirst]![ID],[subControlSecond]![ID], .., [subControlLast]![ID])
        '
        ' Build array of the names of the subform controls with the key controls:
        '   [subControlFirst]![ID]
        '   [subControlSecond]![ID]
        '   ...
        '   [subControlAny]![ID]
        '   ...
        '   [subControlLast]![ID]
        ' by extracting arg names between "(" and ")".
        aControls = Split(Replace(Split(Me(cControl).ControlSource, "(")(1), ")", ""), ",")
        
        ' Loop to locate and sync those subforms that haven't changed.
        For wIndex = LBound(aControls()) To UBound(aControls())
            If sControls(wIndex) <> wThisID Then
                ' This subform is to be synced.
                ' Extract name of subform control using Split:
                '   [subControlAny]
                Set wSubform = Me(Split(aControls(wIndex), "!")(ControlName.SubForm)).Form
                ' Position subform at top record.
                wSubform.SelTop = 1
                Set rst = wSubform.RecordsetClone
                ' Find record for current key.
                ' Extract name of control on subform using Split:
                '   [ID]
                rst.FindFirst Split(aControls(wIndex), "!")(ControlName.Key) & " = " & wThisID
                If Not rst.NoMatch Then
                    bmk = rst.Bookmark
                    wSubform.Bookmark = bmk
                End If
                rst.Close
            End If
        Next
  
    End If
  
    Set rst = Nothing
    Set wSubform = Nothing
  
    SyncSubforms = wLastID

End Function

Open in new window

If you ID as a date value - as WTD_Test_Date indicates - you must modify the FindFirst line:

  rst.FindFirst Split(aControls(wIndex), "!")(ControlName.Key) & " = #" & Format(wThisID, "yyyy/mm/dd") & "#"

/gustav
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
DiscoStubaccaAuthor Commented:
Thanks Gustav,

I'm almost there, I implemented what you posted, changing the findfirst line so that dates would work.

It works if I select a date on the second subform (which is on the second tab) but if I select a date on any other subform the textbox on the mainform shows an error and the subforms do not sync.

All the subforms have the same record source (with different selections)  and the field I am syncing is the same across all subforms.

Cheers,
Stuart
0
 
DiscoStubaccaAuthor Commented:
I put some error catching in and get this message if I select a date on any subform apart from subform 2:

Error -2146500594 (Method 'Form' of object'_SubForm failed) in procedure syncSubforms of VBA Document Form_WTD Report

Hope that helps.
Stuart
0
 
Gustav BrockCIOCommented:
Sounds like the subform control doesn't hold a true form but only a table or query.
Is that the case?

/gustav
0
 
DiscoStubaccaAuthor Commented:
No, all the subforms are actual forms.
Because they all look the same I just made one and copied it fourteen times on to all the tabs then changed the source for each textbox except WTD_Test_Date as it is the same source field from query WTD_Report which is the record source for all the subforms.
The main form is just there for a title, syncforms textbox and to hold the tab control with the subforms.
0
 
DiscoStubaccaAuthor Commented:
Strange that your code works perfectly for the 2nd subform in that,if I click a date there, they all sync to that date but if I Click  any other subforms date the textbox on the main form errors and no syncing.
I would have thought the first subform if any.
0
 
Gustav BrockCIOCommented:
But is it really named "object'_SubForm"? And is that the subform name or the name of the subform control?

What is the exact expression you use in the textbox on the mainform?

/gustav
0
 
DiscoStubaccaAuthor Commented:
There is no subform called "object '_subform' I haven't called anything '_subform', the Subform names are in the style of [WTD Report Subf Dam 1] and Subform Control names are the default 'Child138' ... 'Child150'.

But anyway, finally got it to work, in Subform 2 there was an extra space in the subform control name.
Once this was changed to match all the other names all the subforms now sync when a date is clicked on.
Funny how the only subform that was different was the one which worked.

Thanks for your help,
Stuart
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now