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

Thanks Don. In my FillTableDescriptions subroutine, I drop the table and then recreate all of the field information (I think this is consistent with what you are suggesting). It takes just a few seconds to run for my database which has ~20 tables;  so, it is called from the Report's OnOpen. Thanks again! Here is a snippit of the final report for one table:

Answer 3

Also, I concur with your MoveFirst suggestion and am doing this. Thanks again!

Answer 4

If you decide to go with this, you will still need to deal with the duplication of data that will surely occur, every time the code runs.

I said:

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?

In order to use my proposed  idea (above):

1.) Create a copy of tblTableDescriptions (Structure Only) and save it as tblTemplate.

2.) Copy the code from this link  IsTableQuery function to a new or existing module, and compile your database.

After adding the IsTableQuery function to a module, and creating a template table named "tblTemplate" ... insert this into your existing code just before the line that reads:" Set rstAdd = MyDB.OpenRecordset("tblTableDescriptions", dbOpenDynaset) 'DL"

If IsTableQuery("", "tblTableDescriptions") Then

    MyDB.TableDefs.Delete ("tblTableDescriptions")

    DoEvents

   MyDB.TableDefs.Refresh

    DoCmd.CopyObject , "tblTableDescriptions", acTable, "tblTemplate"

    MyDB.TableDefs.Refresh

End If

Hope this helps ...

Edit: I have tested your code along with my proposed revisions (above), and it appears to be working quite well!

Just one more suggestion, right off the hop:

                ' Get field values from 1st row of table

                If Not rst.BOF Then

                    rst.MoveFirst 'DL Explicitly move to the first record

                    sampleval = rst.Fields(fld.Name)  ' pull the value from the first record returned

                    If IsNull(sampleval) Then

                        sampleval = ""

                    End If

                Else

                    sampleval = "No Rows"

                End If

                sampleval = CStr(sampleval)

                If Len(sampleval) > 255 Then

                    sampleval = Left(sampleval, 200) + " ..."

                End If

                !Value1 = CStr(sampleval)

IF you want to allow REALLY long "sampleval" 's ... (up to 63,999 chracters) you could change the Data Type of "Value1" to "Memo". Once this is set up, you could set the CanGrow / CanShrink properties for the textbox on the report, so that they could re-size accordingly.

Data Type Property