Referencing Subform Properties using Variables

[150563a]

I have a form that has 5 sub forms on it.

I want to know how to reference the form properties of these sub forms. This I can do but what I am having trouble with is substituting the form name with a variable.

Below is what I have that works

    groupHeight = 0
    FormHeader = stdHeader
    formDetail = stdDetail
    FormFooter = stdFooter
    ' Count Number of Records
    Set dbs = CurrentDb
    strSQL = "SELECT FitmentChart.FitmentMakeID, FitmentChart.FitmentTypeID, FitmentChart.FitmentModelID, Products.DisContinued, Products.Superceded, Products.CanBeSold, FitmentChart.FitmentPartTypeID FROM Products INNER JOIN (FitmentLocation INNER JOIN (FitmentPartType INNER JOIN FitmentChart ON FitmentPartType.FitmentPartTypeID = FitmentChart.FitmentPartTypeID) ON FitmentLocation.FitmentLocationID = FitmentChart.FitmentLocationID) ON Products.ID = FitmentChart.ProductCodeID WHERE (((FitmentChart.FitmentMakeID)= " & [Forms]![VehicleSearch]![LookupMake] & ") AND ((FitmentChart.FitmentTypeID)= " & [Forms]![VehicleSearch]![LookupType] & ") AND ((FitmentChart.FitmentModelID)= " & [Forms]![VehicleSearch]![LookupModel] & ") AND ((Products.DisContinued)=False) AND ((Products.Superceded)=False) AND ((Products.CanBeSold)=True) AND ((FitmentChart.FitmentPartTypeID)=14));"
    Set rst = dbs.OpenRecordset(strSQL)
    Do Until rst.EOF
        groupHeight = groupHeight + 1
        rst.MoveNext
    Loop
    rst.Close
    Set dbs = Nothing
    If groupHeight > 0 Then
        groupHeight = FormHeader + (formDetail * groupHeight) + FormFooter
        Me.VehicleSearchLabour.Top = groupTop * twipExchange
        Me.VehicleSearchLabour.Height = groupHeight * twipExchange
        Forms!VehicleSearch!VehicleSearchLabour.Requery
        groupTop = groupTop + groupHeight
        Me.VehicleSearchLabour.Visible = True
    Else
        Me.VehicleSearchLabour.Top = groupTop * twipExchange
        Me.VehicleSearchLabour.Height = groupHeight * twipExchange
        Me.VehicleSearchLabour.Visible = True
    End If
 

Below is what I have tried but does not work

 
    Dim dbsLoop As Database, rstLoop As Recordset
    Dim strSQLloop As String
    Set dbsLoop = CurrentDb
    strSQLloop = "SELECT FitmentPartType.FitmentPartTypeID, FitmentPartType.VehicleSearchOrder, FitmentPartType.Description, FitmentPartType.VehicleSearchForm FROM FitmentPartType ORDER BY FitmentPartType.VehicleSearchOrder;"
    Set rstLoop = dbsLoop.OpenRecordset(strSQLloop)
    Do Until rstLoop.EOF
 
        Dim dbs As Database, rst As Recordset
        Dim strSQL As String
        Dim groupTop, groupHeight, FormHeader, formDetail, FormFooter, stdHeader, stdDetail, stdFooter As Integer
        Dim twipExchange As Double
        twipExchange = 566.929
        groupTop = 0
        stdHeader = 1
        stdDetail = 0.75
        stdFooter = 0.1
        groupHeight = 0
        FormHeader = stdHeader
        formDetail = stdDetail
        FormFooter = stdFooter
        Set dbs = CurrentDb
        strSQL = "SELECT FitmentChart.FitmentMakeID, FitmentChart.FitmentTypeID, FitmentChart.FitmentModelID, Products.DisContinued, Products.Superceded, Products.CanBeSold, FitmentChart.FitmentPartTypeID FROM Products INNER JOIN (FitmentLocation INNER JOIN (FitmentPartType INNER JOIN FitmentChart ON FitmentPartType.FitmentPartTypeID = FitmentChart.FitmentPartTypeID) ON FitmentLocation.FitmentLocationID = FitmentChart.FitmentLocationID) ON Products.ID = FitmentChart.ProductCodeID WHERE (((FitmentChart.FitmentMakeID)= " & [Forms]![VehicleSearch]![LookupMake] & ") AND ((FitmentChart.FitmentTypeID)= " & [Forms]![VehicleSearch]![LookupType] & ") AND ((FitmentChart.FitmentModelID)= " & [Forms]![VehicleSearch]![LookupModel] & ") AND ((Products.DisContinued)=False) AND ((Products.Superceded)=False) AND ((Products.CanBeSold)=True) AND ((FitmentChart.FitmentPartTypeID)= " & rstLoop![FitmentPartTypeID] & "));"
        Set rst = dbs.OpenRecordset(strSQL)
        Do Until rst.EOF
            groupHeight = groupHeight + 1
            rst.MoveNext
        Loop
        rst.Close
        Set dbs = Nothing
        If groupHeight > 0 Then
            groupHeight = FormHeader + (formDetail * groupHeight) + FormFooter
            rstLoop![VehicleSearchform].Top = groupTop * twipExchange
            rstLoop![VehicleSearchForm].Height = groupHeight * twipExchange
            rstLoop![VehicleSearchForm].Requery
            groupTop = groupTop + groupHeight
            rstLoop![VehicleSearchForm].Visible = True
        Else
            rstLoop![VehicleSearchForm].Top = groupTop * twipExchange
            rstLoop![VehicleSearchForm].Height = groupHeight * twipExchange
            rstLoop![VehicleSearchForm].Visible = True
        End If
        rstLoop.MoveNext
    Loop
    rstLoop.Close
    Set dbsLoop = Nothing
 
 

Basically I have a table named 'FitmentPartType' that contains a field called 'VehicleSearchForm' that holds the names of the sub forms on my form. The code steps through this table and is suppose to show / hide and adjust position properties of the sub form that each record refers to.

[MMcCarthy]

The only thing I can think of offhand is to have just one subform object (sfrmObject) and change it dynamically.

Me.sfrmObject.SourceObject = rstLoop![VehicleSearchForm]

' you will have to add these to the recordset
Me.sfrmObject.LinkMasterFields = rstLoop!MasterID
Me.sfrmObject.LinkChildFields = rstLoop!ChildID

Me.sfrmObject.Top = groupTop * twipExchange

etc.

[150563a]

Thanks for your reply.

I tried your suggestion and must admit it didn't mean much to me as I am only just starting to use record sets. It also quite possibly could have been my explination.

I had another suggestion on another forum and it worked.

Me(rstLoop![VehicleSearchForm]).Top = groupTop * twipExchange

I had tried this with me. but had no success. So it must be that when you use brackets it takes the contents as a variable not characters to find and therefore looks up the variable contents.

[MMcCarthy]

Thanks for your reply.

I tried your suggestion and must admit it didn't mean much to me as I am only just starting to use record sets. It also quite possibly could have been my explination.

I had another suggestion on another forum and it worked.

Me(rstLoop![VehicleSearchForm]).Top = groupTop * twipExchange

I had tried this with me. but had no success. So it must be that when you use brackets it takes the contents as a variable not characters to find and therefore looks up the variable contents.

When you use brackets it takes a string value. Your problem earlier was you were trying to directly refer to the subform control with a string.