Access - Dynamically setting row source for a sub report

Question

I have a table containing the names of tables in a column called TableName alongside descriptive information. I would like to follow the descriptive info with a subreport with a "SELECT TOP 5 * FROM <TableName>" which provides the top few rows for the table. How do I set the row source for each subreport to theTableName value? I am having trouble figuring out which event to use and/or how to access the master tablename for each subreport rendered. Advice?

Answer 1

Hi,

I've spent a bit of time working thru some of this, and have come up with a very quick method of populating a table that will work for the main report.

Here's my adaptation of Allen Brown's code that I posted previously.

===========================

Function FillTableDescriptions() 'DL

'Function CycleFields(ByVal MyTableName As String)

'Function courtesy Allen Brown

'https://bytes.com/topic/access/answers/190118-cycle-though-fields-recordset

'with modifications by Don Leverton

Dim MyDB As DAO.Database 'DL

Set MyDB = CurrentDb 'DL

Dim tdf As DAO.TableDef 'DL

Dim MyTableName As String

Dim rstAdd As DAO.Recordset 'DL

Set rstAdd = MyDB.OpenRecordset("tblTableDescriptions", dbOpenDynaset) 'DL

Dim rst As DAO.Recordset

Dim fld As DAO.Field

Dim i As Integer

Dim MyFieldDesc

Dim MySQL As String

For Each tdf In MyDB.TableDefs

    MyTableName = tdf.Name

    If Not InStr(1, MyTableName, "Sys") > 0 Then 'We're not interested in System Tables ...

        MySQL = "" 'Clear the variable

        MySQL = "SELECT "

        MySQL = MySQL & Chr(91) & MyTableName & Chr(93) 'If table name includes spaces, it must be wrapped in []

        MySQL = MySQL & ".* FROM "

        MySQL = MySQL & Chr(91) & MyTableName & Chr(93)

        MySQL = MySQL & ";"

        'Debug.Print MySQL

        On Error GoTo HandleErr

        Set rst = MyDB.OpenRecordset(MySQL)

        For i = 0 To rst.Fields.Count - 1

        Set fld = rst.Fields(i)

        MyFieldDesc = fld.Properties("Description")

        'Debug.Print fld.Name, fld.Type, MyFieldDesc

            With rstAdd

                .AddNew

                !TableName = MyTableName

                !FieldName = fld.Name

                !DataType = fld.Type

                !DataDescription = MyFieldDesc

                .Update

            End With

        Next i

End If

HandleErr:

    Select Case Err.Number

        Case 3270 'Property does not exist or is NULL

            MyFieldDesc = "Description for this field is Empty"

            Resume Next

        Case 0

            'Do Nothing

        Case Else

            MsgBox Err.Number & ": " & Err.Description

    End Select

Next tdf

Set fld = Nothing

rst.Close

Set rst = Nothing

Set rstAdd = Nothing

Set MyDB = Nothing

End Function

=========================================

I've also taken Scott's "dynamic report" idea into consideration (It's always fun for me to experiment with a new concept) and found this:

https://bytes.com/topic/access/insights/696050-create-dynamic-report-using-vba

I'm thinking that this function could be called from within the code above, as most of the variables needed would already be "in scope", and you are also already cycling thru the TableDef collection? Maybe not ... as it might be hard to come up with an event that would "kill both birds with one stone".

The first problem that I foresee with calling this FillTableDescriptionsfunction more than once is that you would be ".AddNew"-ing to the existing table ... so now I'm thinking of creating a "template table" with the correct structure, then deleting / recreating "tblTableDescriptions" on the fly.

One benefit to this might be that it would ensure that all of the information would be current as of the time that this function runs?

Thoughts?

Edit:

PS, My function is based on a single table (tblTableDescriptions) that stores both the Table AND field names , which I thought my avoid having to use any sort of union query.

This would still allow you to LinkMaster/LinkChild your subreport on table name.

Answer 2

Sorry for deleting my original reply, but I realized I was slightly off-base.

I did find something very similar using Google, and according to the thread ... it works.

https://social.msdn.microsoft.com/Forums/office/en-US/1d883a1a-d5bd-4c4b-8938-7ddd6a002a52/change-recordsource-of-subreport-in-vba?forum=accessdev

Answer 3

What about using a UNION query that has different table name for each part and criteria that references an Option Group.

The option group selection would control which part of the union query would output the data.

Answer 4

I'm a little unclear what you are trying to do. The first thing you need to do is define the business rules for determining which Recordsource to use. 

But Karl may have a point. If each subreport will have one of the tables in your table, then a UNION query as the Recordsource for ONE subreport and grouped on the tablename seems a good solution.