Access Programming Reports

Report Optional Details Section

A contacts report, showing multiple contact, has a user option to include list of feature articles related to contact. The features subreport column headings should not be displayed when there are no features for that contact.

Add subreport ContactArticlesSub in the details section under contact fields (name, company, email etc). Set "can shink" = true for subreport and detail.

In the Detail.On Format event add following VBA code to set the visible property for subreport to true only if there are features for the contact and the user has selected to show features.

Me.ContactFeatureSub.visible = False          
If forms("CallingForm").ShowFeatures = True then ' check form loaded
    If DCount("FeatureID", "FeatureContact", "ContactID = " & Me.ContactID) > 0 Then  
        me.ContactFeaturesSub.Visible = true          
    End If
End If 

Reports: How to make a Grid

In page header have horizontal line, at very bottom just above the detail

In the detail section place vertical lines as required (between fields) and a horizontal line at the bottom of the section.

(Doing it the other way round doesn't work well: You can't use the Page Footer for the last line of the page, because there will be usually some gap, before the footer.

Calculating Report Totals by Code

In simple case use =Sum(NetSales) in footer field controlsource.

Alternatively a calculated result in the reports query is a good solution.

When using VBA code to calculate totals, check for events being called more than once.

Dim TTotal

Private Sub Report_Open(Cancel As Integer) 
    AddTotals = False          
End Sub 

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer) 

    If Not AddTotals Then
        Reports!ProjectSessions.TTotal = Reports!ProjectSessions.Ttotal + Nz(TTotal, 0) 
        AddTotals = True ' Only do once per report         
    End If       

End Sub