How to programmatically create a new column in an Access report (812719)

MORE INFORMATION

Note The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

Method 1 Add Columns to the Report Programmatically

The example that follows shows you how to programmatically create an Access report. The code generates a report that is based on record source query. The generated report displays the Firstname column and the Lastname column of the Employees table of the Northwind.mdb sample database.

1. Run Access. Open the sample database Northwind.mdb.
2. In the left pane, click Reports.
3. In the right pane, double-click Create report in Design View.
4. On the File menu, click Save.
5. In the Save As dialog box, type AccessColumnBuilder and then click OK.
6. On the File menu, click Close.
7. In the left pane, select Forms.
8. In the right pane, double-click Create form in Design View.
9. Add a command button to the form.
10. Add the code that follows to the OnClick event of the command button:

    Dim txtNew As Access.TextBox
    Dim labNew As Access.Label
    Dim lngTop      As Long
    Dim lngLeft     As Long
    Dim lblCol    As  Long
    Dim rpt As Report
    Dim reportQuery As String
    Dim rs As DAO.Recordset
    Dim i As Integer
    Dim prevColwidth As long
    
    lngLeft = 0
    lngTop = 0
     
    ' Open the report to design.
    ' To make changes in the number of columns that appear at run time.
    
    DoCmd.OpenReport "AccessColumnBuilder", acViewDesign
    
    Set rpt = Reports![AccessColumnBuilder]
    
    ' Change the number of columns required as per your requirement.
    reportQuery = "SELECT FirstName, LastName FROM Employees"
    
    ' Open the recordset.
    Set rs = CodeDb().OpenRecordset(reportQuery)
    ' Assign the query as a record source to report control.
    rpt.RecordSource = reportQuery
    
    ' Set the value to zero so that the left margin is initialized.
    prevColwidth = 0
    lblCol = 0
    ' Print the page header for the report.
    For i = 0 To rs.Fields.Count - 1
    Set labNew = CreateReportControl(rpt.Name, acLabel, acPageHeader, _
          , rs.Fields(i).Name, lblcol, , , lngTop)
          labNew.SizeToFit
          lblCol = lblCol + 600 + labNew.Width
    Next
    
    ' Create the column depending on the number of fields selected in reportQuery.
    ' Assign the column value to new created column.
    For i = 0 To rs.Fields.Count - 1
          ' Create new text box control and size to fit data.
          Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
             acDetail, , , lngLeft + 15 + prevColwidth, lngTop)
          txtNew.SizeToFit
          txtNew.ControlSource = rs(i).Name
          ' Modify the left margin depending on the number of columns
          ' and the size of each column.
          prevColwidth = prevColwidth + txtNew.width
    Next
    'To save the modification to the report,  uncomment the following line of code:
    	'DoCmd.Save
    ' View the generated report.
    DoCmd.OpenReport "AccessColumnBuilder", acViewPreview
    
    ' This opens the report in preview.
    

11. Save and then run the form.
12. To preview the report, click the command button that you added in step 9.
    
    The records that follow appear on the first page:
    
    

    First Name	Last Name

    
    

    Nancy		Davolio

    
    

    Margaret		Peacock

    
    

    ........	.......

    
    
    The report contains the Firstname column and the Lastname column of the Employees table. You can either save the report or make the required changes to the query and then run the report.

Method 2 Add Columns to the Report at Run Time by Setting the Visible Property of the Existing Column

This example shows you how to display a new column in a report by manipulating the Visible property of the control.

The report contains four columns. The Visible property of the first three columns is set to yes. The Visible property of the fourth column is set to no. On a page break, the Visible property of the fourth column is set to yes when the column appears.

1. Run Microsoft Access. Open the sample database Northwind.mdb.
2. To create a report that is named Report1 and is based on the Products table, follow these steps:
   1. In the Database window, click Reports and then click New.
   2. Select the Products table and then click OK.
   3. Add the following text boxes to the Detail section of the report. Align the text boxes.
   4. Put the corresponding labels in the Page Header section of the report. Align the labels.

      Report: Report1
         --------------------------
         Caption: TestReport
         ControlSource: Products
      
         Label:
            Name: ProductName_label
            
         Text Box:
            Name: ProductName
            ControlSource: ProductName
          
      
         Label:
            Name: UnitPrice_label
         
         Text Box:
            Name: UnitPrice
            ControlSource: UnitPrice
      
         
         Label:
            Name: UnitsInStock_label
         
         Text Box:
            Name: UnitsInStock
            ControlSource: UnitsInStock
         
      
         Label:
             Name: TotalPrice_label
             Visible: No
      
         Text Box:
            Name:  TotalPrice
            ControlSource:  =[UnitPrice] * [UnitsInStock]
            Visible: No
      

3. Add a text box control with the properties that follow. Add this to the Detail section. Put the text box directly above the ProductName control.
   
   This control acts as a counter for the number of records in the report.

   Text Box:
      -----------------------
      Name: Counter
      ControlSource: =1
      Visible: No
      RunningSum: Over All
   

4. Open the toolbox and then click Page Break. Add a page break control to the lower-left corner of the Detail section. Put the page break control directly below the ProductName control. Set the Name property to PageBreak.
5. In the Detail section, set the OnFormat property to the following event procedure:

       If Me![Counter] Mod 2 = 0 Then Me![PageBreak].Visible = True _
       Else Me![PageBreak].Visible = False
   

6. To reduce the blank space in the report, put your pointer between the bottom of the Detail section and the Page Footer and then drag up.
7. In the Page Footer section, set the OnPrint property to the event procedure that follows:

   
   If Me![PageBreak].Visible = True Then
       Me![TotalPrice].Visible = True
       Me![TotalPrice_label].Visible = True
   Else
       Me![TotalPrice].Visible = False
       Me![TotalPrice_label].Visible = False
   End If
   

   When PageBreak occurs, the TotalPrice column appears.
8. Preview the report. The records that follow appear on the first page:
   

   Product Name	Unit Price	Unit in Stock

   
   

   Cahi		$18.00		39

   
   

   Chang		$19.00		17

   
   Note The first page of the report contains three columns.
   
   The records that follow appear on the second page:
   
   

   Product Name	Unit Price	Unit in Stock	Total Amount

   
   

   Aniseed Syrup 	$10.00		13    		130

   
   

   Chef A...    	$22.00 		53    		1166

   Note The page break occurs after the first page. Therefore, the second page of the report contains four columns.