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

Ok, First, Have you looked at the Built in Database Documenter tool?

Second, I don't understand why the subreport (assume the subreport is on tblMyTableFields) in the first place. If you create a query that joins  tblMyTableDescriptions to  tblMyTableFields on Tablename there is no need for a subreport. You simply Group on Tablename. So I would eliminate THAT subreport. 

Third, So what you really want is a Single Subreport that displays a sample of the records in the table within that Group I would place that subreport in the Group Footer. I would then use the On Print event to populate the Recordsource of that subreport with a SQL statement pulled from the linked Tablename. 

Now I'm not sure how this would work as I haven't tried it. You would probably have to , dynamically, populate the ControlSources and Header captions of each control in that subreport. In theory I think its doable but I'm not sure.

P.S. Yes, now that I understand what you are trying to do a UNION is not applicable.

Answer 3

Scott,

Thanks for your thought filled response. I am a bit hung up on some of the details (lack of background on my side). In response to your items:

1. Yes, I did look at the documenter. Although it provides good technical details it does not appear to pull in the field descriptions or the table description from the properties. I may be missing something; so, please confirm that this is the case. Also, I create my own form for descriptive information on a per table basis which is comfortable for the documentation phase. Here is a snippit from my report on a couple of general tables being reported on (with the empty box being my unsuccessful attempt to pull in example data) ...

... report continues with field descriptions, table data, and then on to the next table for the next set of descriptive sub-reports.

2. So, the fields information is a sub-report. Let me know if you still feel that a sub-report is not appropriate. Not sure if I follow and think that maybe my description was not sufficient previously for you to understand.
3. I am fuzzy on scope / sub-report location here. So, I am placing a sub-report next to (but not within) the subreport discussed in item 2. I don't follow  when you say group footer (the pager footer is not desired as I want the data to be shown alongside the field data). My problem is that I can not find the right event (and item to tie the event to) in order to set the row source. I have tried the OnPaint - but, it does not seem to fire per sub-report. In other words each time it fires all the row sources get set. So, maybe I am just missing the right event/object pair - but, I am feeling like what I want to do is not possible. Still a good chance that I am missing something basic ...

Answer 4

Thanks for the help! I think my original question was not clear; but, please come back to me if the UNION suggestion does fit my need after the additional explanation below. UNIONs are typically used for common fields from multiple queries; so, this lead me to believe that my description of my need was not clear.

My hope is to create a report that will provide documentation about my database. Currently, I have code that gleans out field information for each table alongside supplementary documentation for each table. So, there are currently a couple of tables which are defined as follows (simplified for discussion):

1. tblMyTableDescriptions which contains

        TableName (PK)

        TableDescription

        etc.

2. tblMyTableFields which contains

         TableName  (FK to table above)

         FieldName

         FieldType

         FieldComment

         etc.

I am able to generate a nice report with the main report being represented by the tblMyTableDescriptions and then a sub-report which provides the field data for each table in tblMyTableDescriptions. This makes for nice database design documentation. My thought is that it would be nice to extend the sub-report so that "example" data is provided by taking existing records and showing those in the sub-report.

Is there a way to dynamically set the row source for a listbox within a sub-report? That is, for each sub-report produced I want to set a different row source. Let's say I have Table1, Table2, etc. defined in my database and described as rows in tblMyTableDescriptions above. Is there a way for each sub-report to contain a listbox whose row source changes for each sub-report? So, the first subreport list would have a row source of "SELECT TOP 5 * FROM Table1", the second report row source of "SELECT TOP 5 * FROM Table2", etc. where the table name is provided from the master table (ie. tblMyTableDescriptions).

I presume that UNION suggestion does not fit my problem given that UNIONs typically combine like fields while each of my Tables will be have entirely different fields. I think the UNION suggestion is a result of my original question not being clear. Hope this clarifies. Thanks again!

Answer 5

1 & 2) Yes a subreport is not necessary. What is the Recordsource of the report and the Subreport? 

I would use a Recordsource like:

SELECT tblMyTableDescriptions.*, tblMyTableFields.Fieldname, tblMyTableFields.FieldType, 

tblMyTableFields.Comments 

FROM tblMyTableDescriptions, tblMyTableFields

INNER JOIN ON tblMyTableDescriptions.Tablename = tblMyTableFields.Tablename;

Then I would create a Group on Tablename. I would put the info about the table in the Group header and the info about the fields in the Detail Band. 

3. By eliminating the unnecessary subreport and setting up a Group, you will then have a Group footer to place your sample data subreport. Again, I've never tried this so you may have to play. You should use the Group Footer's On Format AND On Print events to run the code that sets up the subform.