How To Create A MS Access Dynamic Report Based On A Dynamic Table

So in my other post, I demonstrated how to create a table dynamically with VBA.

Here’s the post
How To Create A Table In Access Using SQL And VBA

Now what if we need a report based on a table that is always changing, but is consistent in
its format?

Well we can send the data to Excel using VBA, or we can use an Access report and change the control source of the fields in the report to reflect the table’s new fields.

The report needed to stay in Access, but the field names would constantly be changing based on the months, and months don’t sort correctly (April, August … instead of January, February).

So the way I decided to handle it was to create a table in VBA, and then use vba to have ms access generate a dynamic report.

In the attached database I’m dynamically change my field name’s control source on the report.

Here is some helpful background… inspections are conducted on “SAA’s” (satellite accumulation units), and the data is logged in the database where a report can be generated based on data in the table (tblSAAInspections).

The goal is to produce the report called “rptSAAStatisticsTableQuestions”.

So here is a step by step:

1. We use this form to select the date for the report we want to create:

2. Here is the report based on the prior selection:

3. The table called “tblSAAQuestionStats_Report” serves as the record source for the report. Here’s what it looks like in it’s raw form:

Here is the code to get to this point of table creation (EVERYTHING BEFORE THE “If intCreateTable = True Then” LOGIC):

Option Compare Database

Private Sub btnCreate_Click()

    Dim dteEndDate As Date
    Dim qdf As QueryDef
    Dim prmEndDate As Parameter
    Dim intCreateTable As Integer
    
    dteEndDate = Forms![frmCreateSAATable]![cboSAAMonths]
    dteEndDate = DateAdd("m", 1, dteEndDate) 'add 1 month to the end date
    
    '1. make the table (add 1 month for the table creation)
    intCreateTable = CreateSAAQuestionsTable(dteEndDate)
    
    If intCreateTable = True Then

'***********************************************************************
'NOW IT'S TIME TO POPULATE THE TABLE WE JUST CREATED WITH REPORT DATA:
'***********************************************************************

        'reset the end date back to the form date
        dteEndDate = Forms![frmCreateSAATable]![cboSAAMonths]
        
        '2. now add data to the table you just made (we are using the querydef because the use of a parameter will throw an error)
        Set qdf = CurrentDb.QueryDefs("qrySAAInspectionsPerMonth_xtab2")
        
        'Set the value of the QueryDef's parameter
        qdf.Parameters("Forms![frmCreateSAATable]![cboSAAMonths]").Value = Forms![frmCreateSAATable]![cboSAAMonths]
    
        'Execute the query
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "qrySAAInspectionsPerMonth_xtab3"
        DoCmd.SetWarnings True
    
        'Clean up
        qdf.Close
        Set qdf = Nothing
        
        MsgBox "Query Finished.  Loading Report..."
        
        DoCmd.OpenReport "rptSAAStatisticsTableQuestions", acViewPreview
        
    Else
        MsgBox "Table not created, so don't proceed"
    End If
    
End Sub
Function CreateSAAQuestionsTable(EndDate) As Boolean
    'Produced by http://loeblcomservices.com
    
    Dim intCounter As Integer
    Dim strFields As String
    Dim blnTableCreated As Boolean
    Dim strMonths As String

    
    For intCounter = 12 To 1 Step -1
        strMonths = strMonths & "," & Format(DateAdd("m", -intCounter, EndDate), "mmm-yyyy")
        'strMonths = strMonths & "," & intCounter
    Next
        
    strFields = "ID,JEPRS Text" & strMonths
    
    blnTableCreated = CreateSAAQuestionsTable2(strFields, 14, "tblSAAQuestionStats_Report")
    CurrentDb.TableDefs.Refresh

    If blnTableCreated Then
        CreateSAAQuestionsTable = True
    Else
        CreateSAAQuestionsTable = False
    End If
       
    
End Function
Public Function CreateSAAQuestionsTable2(table_fields As String, num_fields As Integer, table_name As String) As Boolean
    Dim strCreateTable As String
 
    Dim intCount As Integer
 
    Dim strFields() As String
    Dim strValues() As String
 
    Dim strInsertSQL As String
 
    Dim intCounter As Integer
    Dim intData As Integer
 
    On Error GoTo errHandler
 
    'split the string on the comma delimiter
    strFields = Split(table_fields, ",")
 
    CurrentDb.TableDefs.Delete table_name
    Debug.Print "DELETE: " & table_name
    CurrentDb.TableDefs.Refresh
 
    'this creates the table structure...
    strCreateTable = "CREATE TABLE " & table_name & "("
 
    For intCounter = 0 To num_fields - 1
        If intCounter = 1 Then
            strCreateTable = strCreateTable & "[" & strFields(intCounter) & "] VARCHAR(150),"
        Else
            strCreateTable = strCreateTable & "[" & strFields(intCounter) & "] INT,"
        End If
    Next
 
    If Right(strCreateTable, 1) = "," Then
        strCreateTable = Left(strCreateTable, Len(strCreateTable) - 1)
        strCreateTable = strCreateTable & ")"
    End If
 
    CurrentDb.Execute strCreateTable
 
    intCounter = 0 'reset
    intData = 0 'reset
 
    If Err.Number = 0 Then
        CreateSAAQuestionsTable2 = True
    Else
        CreateSAAQuestionsTable2 = False
    End If
    
 
    Exit Function
errHandler:
        CreateSAAQuestionsTable2 = False
        MsgBox Err.Number & " " & Err.Description
 
End Function

****************************************************************************

The task of populating the new table structure with data is accomplished using queries. I know that there are different ways of doing this, but I decided to keep it simple:

1. The query called “qrySAAInspectionsPerMonth”, is used to add 2 user defined functions which are in “basSAA” called “SAAFail” and ‘SAAFail_val”. This identifies SAA inspections that failed, and what question in “tblSAAInspectionQuestions” was the cause of its failure.

2. “qrySAAInspectionsPerMonth_xtab” limits our selection to just show the 12 months from the date selected on the form which is “Mar-2019”

The “12 Months Selection” is generated by this syntax:

12 Month Selection: DateDiff("m",[Inspection Date],CDate([Forms]![frmCreateSAATable]![cboSAAMonths]))

So we just want the records that are failures and occurred in the past 12 months from the date selection.

3. “qrySAAInspectionsPerMonth_xtab2” – takes the query from step2, and puts it into a crosstab query format. I can not use this as the record source because the column headings can always change, and don’t sort correctly.

To get the “JEPRS Text” I use a domain function called “Dlookup”

JEPRS Text: DLookUp("[JEPRS Text]","tblSAAInspectionQuestions","[ID]=" & [Failed Question])

Basically I want to know the question the inspection failed on, so it says “look in the [JEPRS Text] field of the [tblSAAInspectionQuestions] table for the [JEPRS Text] value where the ID is the same as the failed question”

I do this also for the ID.

4. Then “qrySAAInspectionsPerMonth_xtab3” appends the crosstab query to the correct fields in the table we created above.

Then we can view the newly generated report!

Since the table fields can change all the time, we need the report fields to by dynamic.

So we use VBA to produce dynamic report fields. Here’s is how simple it is:

When the report opens, set the control source automatically!

Use the Report_Open event as shown below:

Private Sub Report_Open(Cancel As Integer)
    
    '4/25/19 - modify the control source of the month fields
    Dim strSource As String
    Dim dteEndDate As Date
    
    dteEndDate = Forms![frmCreateSAATable]![cboSAAMonths]
    
    strSource = Format(DateAdd("m", -11, dteEndDate), "mmm-yyyy")
    Me.lblF1.Caption = strSource
    Me.Failed1.ControlSource = "=NZ([" & strSource & "],0)"

    strSource = Format(DateAdd("m", -10, dteEndDate), "mmm-yyyy")
    Me.lblF2.Caption = strSource
    Me.Failed2.ControlSource = "=NZ([" & strSource & "],0)"
    
    strSource = Format(DateAdd("m", -9, dteEndDate), "mmm-yyyy")
    Me.lblF3.Caption = strSource
    Me.Failed3.ControlSource = "=NZ([" & strSource & "],0)"
    
    strSource = Format(DateAdd("m", -8, dteEndDate), "mmm-yyyy")
    Me.lblF4.Caption = strSource
    Me.Failed4.ControlSource = "=NZ([" & strSource & "],0)"
    
    strSource = Format(DateAdd("m", -7, dteEndDate), "mmm-yyyy")
    Me.lblF5.Caption = strSource
    Me.Failed5.ControlSource = "=NZ([" & strSource & "],0)"
    
    strSource = Format(DateAdd("m", -6, dteEndDate), "mmm-yyyy")
    Me.lblF6.Caption = strSource
    Me.Failed6.ControlSource = "=NZ([" & strSource & "],0)"
    
    strSource = Format(DateAdd("m", -5, dteEndDate), "mmm-yyyy")
    Me.lblF7.Caption = strSource
    Me.Failed7.ControlSource = "=NZ([" & strSource & "],0)"
    
    strSource = Format(DateAdd("m", -4, dteEndDate), "mmm-yyyy")
    Me.lblF8.Caption = strSource
    Me.Failed8.ControlSource = "=NZ([" & strSource & "],0)"
    
    strSource = Format(DateAdd("m", -3, dteEndDate), "mmm-yyyy")
    Me.lblF9.Caption = strSource
    Me.Failed9.ControlSource = "=NZ([" & strSource & "],0)"
        
    strSource = Format(DateAdd("m", -2, dteEndDate), "mmm-yyyy")
    Me.lblF10.Caption = strSource
    Me.Failed10.ControlSource = "=NZ([" & strSource & "],0)"
            
    strSource = Format(DateAdd("m", -1, dteEndDate), "mmm-yyyy")
    Me.lblF11.Caption = strSource
    Me.Failed11.ControlSource = "=NZ([" & strSource & "],0)"
                
    strSource = Format(DateAdd("m", 0, dteEndDate), "mmm-yyyy")
    Me.lblF12.Caption = strSource
    Me.Failed12.ControlSource = "=NZ([" & strSource & "],0)"


            
End Sub

That is let you control your report fields at run time.

You can download the database here:
MS Access Dynamic Report A Dynamic Table.accdb

Let me know if you have any questions.

———————–
dynamic table,MS Access Report ,create table dynamically with VBA,control source ,create table vba,dlookup
DoCmd.OpenReport.QueryDef, Parameters Collection,dynamic report fields,ms access dynamic reports

By the way, if you got or are getting value from the VBA information, please give me a tip, thanks!