different recordsources for multiple instances of subform

[uguimess]

I programmatically create a Tab Control with a few tabs. Each tab contains a different instance (I believe) of the same subform. I want each instance to have a diffent RecordSource, but they all seem to end up with the same recordsource. Not sure where to look.

Public Function CreateMyControl(iStation As Integer, iGroupSite As Integer) As Boolean

    Const adhcTwipsPerInch As Long = 1440
    
'    Dim frm As Form

    Dim intWidth As Integer
    Dim intHeight As Integer
    Dim intTop As Integer
    Dim intLeft As Integer
    Dim intTopLabel As Integer
    Dim intHeightLabel As Integer
    Dim intWidthLabel As Integer
    Dim intTabWidth As Integer
    Dim intTabHeight As Integer
    Dim intTabTop As Integer
    Dim intTabLeft As Integer
    Dim rst As ADODB.Recordset
    Dim strSQL As String
    Dim MyControlType As AcControlType
    Dim frm As Form
    Dim ctl As Control
    Dim iSite As Integer
    Dim sABC As String
    Dim sA As String
    Dim cTabCtl As TabControl
    Dim cSubFrm As SubForm
    Dim sLabelCaption As String

   
    Set frm = Forms!frm_Data_Entry_Main!sf_frm_T_Checklist.Form
    
    intWidth = 7.5833 * adhcTwipsPerInch
    intHeight = 2.5729 * adhcTwipsPerInch
    intTop = 0.3889 * adhcTwipsPerInch
    intLeft = 0.1493 * adhcTwipsPerInch
    intTopLabel = intTop - (0.1875 * adhcTwipsPerInch)
    intHeightLabel = 0.1875 * adhcTwipsPerInch
    intWidthLabel = 4 * adhcTwipsPerInch
    
    'If first item in the recordset, remove TabCtl.
    'If NOT first item, create TabCtl, subform controls and define source object.
    strSQL = "SELECT * FROM LU_Site WHERE FK_Station = " & iStation
    
    Set rst = New Recordset
    rst.Open strSQL, CurrentProject.Connection, _
     adOpenKeyset, adLockPessimistic
     
    If ControlExists("TabCtlSites", frm) Then
        Set cTabCtl = frm!TabCtlSites
    End If

    
    If iGroupSite = 1 Then
        'Show Sites All Together (if more than one), so no Tab Control needed. _
        Delete the Tab Control and _
        Put subform here instead of tab control.
        If ControlExists("TabCtlSites", frm) Then
            Call RemoveControl(frm.Name, "TabCtlSites")
        Else
            If DCount("ID", "LU_Site", "FK_Station = " & iStation) = 1 Then
                frm!lblSubform.Caption = "Checklist"
            Else
                frm!lblSubform.Caption = "Checklist for all Sites combined"
            End If
            Exit Function
        End If
        With CreateControl( _
          FormName:=frm.Name, _
          ControlType:=acSubform, _
          Section:=acDetail, _
          Left:=intLeft, _
          Top:=intTop, _
          Width:=intWidth, _
          Height:=intHeight)
            ' Set the name, SourceObject and recordsource.
            .Name = "sf_subfrm_Temp_Bird0"
            .SourceObject = "subfrm_Temp_Bird"
'            On Error Resume Next
            .Form.RecordSource = "SELECT * FROM Q_T_Bird_Data_Entry_Tabs"
        End With
        If ControlExists("sf_subfrm_Temp_Bird0", frm) Then
            Set cSubFrm = frm!sf_subfrm_Temp_Bird0
        End If
        sLabelCaption = "Checklist for all Sites combined"
        If rst.RecordCount = 1 Then sLabelCaption = "Checklist"
        With CreateControl( _
          FormName:=frm.Name, _
          ControlType:=acLabel, _
          Section:=acDetail, _
          Parent:=cSubFrm.Name, _
          Left:=intLeft, _
          Top:=intTopLabel, _
          Width:=intWidthLabel, _
          Height:=intHeightLabel)
            ' Set the name and caption.
            .Name = "lblSubform"
            .Caption = sLabelCaption
        End With
    Else
        'Show Sites separately, so we need Tab Control. _
        Delete the one Subform and _
        Create tab control.
        If ControlExists("sf_subfrm_Temp_Bird0", frm) Then
            Call RemoveControl(frm.Name, "sf_subfrm_Temp_Bird0")
        Else
            Exit Function
        End If
        intTabWidth = 7.8229 * adhcTwipsPerInch
        intTabHeight = 3.4167 * adhcTwipsPerInch
        intTabTop = 0 * adhcTwipsPerInch
        intTabLeft = 0 * adhcTwipsPerInch
        With CreateControl( _
          FormName:=frm.Name, _
          ControlType:=acTabCtl, _
          Section:=acDetail, _
          Left:=intTabLeft, _
          Top:=intTabTop, _
          Width:=intTabWidth, _
          Height:=intTabHeight)
            ' Set the name
            .Name = "TabCtlSites"
        End With
        sABC = "A"
        iSite = 1
        While Not rst.EOF
            If rst.AbsolutePosition > 2 Then
                Call AddPage(rst.AbsolutePosition, sABC, rst.Fields("TheName"))
                'Add new pages
            Else
                'Edit existing pages.
                Call RenamePage(rst.AbsolutePosition, sABC, rst.Fields("TheName"))
            End If
            'Add the Subform control.
            sABC = AutoIncr(sABC)
            With CreateControl( _
              FormName:=frm.Name, _
              ControlType:=acSubform, _
              Section:=acDetail, _
              Parent:="Page" & rst.AbsolutePosition, _
              Left:=intLeft, _
              Top:=intTop, _
              Width:=intWidth, _
              Height:=intHeight)
                ' Set the name, SourceObject and recordsource.
                .Name = "sf_subfrm_Temp_Bird" & rst.AbsolutePosition
                .SourceObject = "subfrm_Temp_Bird"
'                On Error Resume Next
                [COLOR=#ff0000].Form.RecordSource = "SELECT * FROM Q_T_Bird_Data_Entry_Tabs WHERE Site_ID = " & iSite[/color]
            End With
            rst.MoveNext
            iSite = iSite + 1
        Wend
    rst.Close
    End If
    
    CreateMyControl = True
    
End Function

-----------------------------------------
Where would we be if we didn't try?

 

[formerTexan]

Hello Uguimess,

Are your problems with the recordsource or recordset?

Looking at your module, I surmise that you potentially want to end up with multiple tabs/subforms with the same recordsource, but each showing a recordset based on a different site (isite).

Let me know if this is on target.

Cheers, Bill

 

[uguimess]

Right. So would you use the base query with no filters, as set in the properties dialog, and just specify a different filter for each instance in the loop?

...
                .Form.Filter = "([Q_T_Bird_Data_Entry_Tabs].[Site_ID]= " & iSite & ")"
...

This worked for the first iteration through the loop as also happened in my original code. Then I am told:

"Run-time error 2467: The expression you entered refers to an object that is closed or doesn't exist"

I am wondering if there is an issue with my referencing the instances of the subform.

-----------------------------------------
Where would we be if we didn't try?

 

[formerTexan]

Hi Uguimess,

I dug back through old projects and found a one where I had set up multiple form instances. In this case I used an unbound form and on its Load event created a recordset and bound this recordset to the form. The caveat is that unbound forms require more work to pass values to controls and move through the recordset.

Private Sub Form_Load()

Dim sPK As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim iPos As Integer
Dim lngPK As Long

        lngPK = pfGetPK 'get recordset criteria
        Set db = CurrentDb
        Set rs = db.OpenRecordset("SELECT Table1.XID, Table1.fldText FROM Table1 WHERE (((Table1.XID)=" & lngPK & "));")
        Set Me.Recordset = rs
        'bind controls
        Me.txtXID = rs!XID
        Me.txtfldText = rs!fldText
    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing

End

I'm not sure if keeping your subforms' bound and assigning the recordsource on their load event will work without going to unbound forms. I don't recall if I used the unbound arrangement out of dire necessity or for unrelated reasons.

Cheers
Bill

Cheers, Bill