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

Don,

As I read it, the OP already has his tables, I believe they include more info then available from TableDefs. But the Fill function may make it easier for future databases. I don't see much of a difference in putting it all into one table or having 2 tables related on Tablename. or using a query to join them. In either case, the use of a sub report for the fields is unnecessary.

The idea of a dynamic report ala that described by Mr Mccarthy is basically what I was talking about.

Answer 3

In looking at your post (alongside Scott's questions on what I was attempting to do), I realized that I needed to add a column to capture a value for each of the fields alongside Type, Name, and description for each of the rows that I want to store example data for.  It makes alot more sense to store this information in a row deep sense anyway given that you don't know how wide the rows of an arbitrary table are and the result would be hard to format.

So, for me I have a couple of tables to normalize out the verbose table description info that I create from the multiple field entries for each table. But, that really does not matter - the main point is that the code you provided can capture the value for the top row of actual data. (I could add 5 columns to capture the top 5 if desired; but, I have decided one row is sufficient for my needs).

So, here are the extensions that I have applied to what you described (this captures the essence of my code which does test out to provide what I need):

1. added a "TOP 1" to your MySql statement
2. added a column called Value1 to the table containing field information
3. code similar to the following just before your .Update on rstAdd (most of the lines are just string conversion / guard coding - main line of code bolded/underlined):

'Additional Code just after

!DataDescription = MyFieldDesc

        ' Get field values from 1st row of table

        If Not rst.BOF Then

            sampleval = rst.Fields(fld.Name)  ' pull the value from the TOP1 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)

'End of Additional Code just before

.Update

So, the bottom line is that my quest of adding another table containing values was not a good idea to begin with and by adding a column to capture the values in the first row of each table makes for a nicer report alongside cleaner code.

Thanks to both you and Scott for your help.

Answer 4

FWIW, my read on this is that you have several different tables --- all containing different field names and data types.

With that in mind, I can't see how you could possibly have ONE subreport that would display the "TOP 5" records from ANY table (because the number and names of fields are variable.)

It may be possible to have multiple overlapping subreports that report on each table individually, and then use a Select Case to make the pertinent subreport Visible?

Perhaps you could post a screenshot of your relationships window so that everyone can get a better grasp on the concept and intent?

Another (fuzzy) idea would be to get the table and field names in VBA ... but I'm not entirely sure how you could get this all compiled into one recordset, and then to display it in a subform???

http://www.techrepublic.com/article/how-to-create-a-list-of-tables-or-queries-from-access/

How to display Hidden / System Tables in Access 2010

(Be sure to backup your ACCDB file before messing around in here!)

This function courtesy Allen Browne:

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

Function CycleFields()

Dim rs As DAO.Recordset

Dim fld As DAO.Field

Dim i As Integer

Set rs = DBEngine(0)(0).OpenRecordset("SELECT * FROM MyTable;")

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

Set fld = rs.Fields(i)

Debug.Print fld.Name, fld.Type

Next

Set fld = Nothing

rs.Close

Set rs = Nothing

End Function

Answer 5

FWIW, my read on this is that you have several different tables --- all containing different field names and data types.

With that in mind, I can't see how you could possibly have ONE subreport that would display the "TOP 5" records from ANY table (because the number and names of fields are variable.)

It is certainly possible to create a dynamic report. In a properly designed database the tables are tall and thin. so there may not be too many fields. In that case, one can produce a tabular report with generic control and label names. Where the controlsources and label captions can be set at runtime. We do this for Crosstab queries.

It may require a lot of coding, it may not work for subreports. But it might.