You are currently offline, waiting for your internet to reconnect

How to programmatically create a new column in an Access report

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

SUMMARY
This article describes how to programmatically create a column in an Access report. You can dynamically add the column to the report by using either Method 1 or Method 2 that are described in the "More Information" section.
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. Start Access.
  2. On the Help menu, click Sample Databases, and then click Northwind Sample Database. Close the Main Switchboard form when it appears.

    Note In Access 2007, click Sample in the Template Categories pane, click Northwind 2007, and then click Download.
  3. In the left pane, click Reports.

    Note In Access 2007, skip this step.
  4. In the right pane, double-click Create report in Design View.

    Note In Access 2007, click Report Design in the Reports group on the Create tab.
  5. On the File menu, click Save.

    Note In Access 2007, click Microsoft Office Button, and then click Save.
  6. In the Save As dialog box, type AccessColumnBuilder and then click OK.
  7. Close the report.
  8. In the left pane, select Forms.

    Note In Access 2007, skip this step.
  9. In the right pane, double-click Create form in Design View.

    Note In Access 2007, click Form Design in the Forms group on the Create tab.
  10. Add a command button to the form.

    Note In Access 2007, click Button to add a button to the form in the Controls group on the Design tab.
  11. Right-click the command button, click Build Event, click Code Builder in the Choose Builder dialog box, and then click OK.
  12. Add the code that follows to the OnClick event of the command button:
    Dim txtNew As Access.TextBoxDim labNew As Access.LabelDim lngTop      As LongDim lngLeft     As LongDim lblCol    As  LongDim rpt As ReportDim reportQuery As StringDim rs As DAO.RecordsetDim i As IntegerDim prevColwidth As longlngLeft = 0lngTop = 0 ' Open the report to design.' To make changes in the number of columns that appear at run time.DoCmd.OpenReport "AccessColumnBuilder", acViewDesignSet 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 = 0lblCol = 0' Print the page header for the report.For i = 0 To rs.Fields.Count - 1Set labNew = CreateReportControl(rpt.Name, acLabel, acPageHeader, _      , rs.Fields(i).Name, lblcol, , , lngTop)      labNew.SizeToFit      lblCol = lblCol + 600 + labNew.WidthNext' 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.widthNext'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.
  13. Save and then run the form.
  14. To preview the report, click the command button that you added in step 10.

    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. Start Access.
  2. On the Help menu, click Sample Databases, and then click Northwind Sample Database. Close the Main Switchboard form when it appears.

    Note In Access 2007, click Sample in the Template Categories pane, click Northwind 2007, and then click Download.
  3. 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.

      Note In Access 2007, click Report Wizard in the Reports group on the Create tab.
    2. In the New Report dialog box, click Design View, selectProducts, 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
  4. Add a text box control with the following properties 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
  5. On the Toolbox, click Page Break.

    Note In Access 2007, click Add or Remove Page Break in the Controls group on the Design tab.
  6. 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.
  7. 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
  8. 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.
  9. In the Page Footer section, set the OnPrint property to the event procedure that follows:

    When PageBreak occurs, the TotalPrice column appears.
  10. 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.
REFERENCES
For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
231806 ACC2000: Page Header That Spans Full Width of Multi-Column Report
209006 ACC2000: How to Control the Number of Records Printed per Page
210336 ACC2000: Sample Function to Determine Current Page of a Form
208979 ACC2000: How to Print a Group Footer at a Specific Location
231851 ACC2000: Microsoft Access 2000 Sample Reports Available in Download Center
For more information about how to programmatically create an Access report, visit the following Microsoft Developer Network (MSDN) Web site:
http://msdn2.microsoft.com/en-us/library/aa188513(office.10).aspx
ACC2007
Properties

Article ID: 812719 - Last Review: 09/17/2011 09:32:00 - Revision: 11.0

Microsoft Office Access 2007, Microsoft Office Access 2003, Microsoft Access 2002 Standard Edition

  • kbprogramming kbvba kbinfo kbhowto KB812719
Feedback
rue';document.getElementsByTagName('head')[0].appendChild(m);" onload="var m=document.createElement('meta');m.name='ms.dqp0';m.content='false';document.getElementsByTagName('head')[0].appendChild(m);" src="http://c1.microsoft.com/c.gif?"> ?DI=4050&did=1&t=">ng" id="language-es-es">España - Español
Paraguay - Español
Venezuela - Español
microsoft.com/c.gif?DI=4050&did=1&t="> var varCustomerTracking = 1; var Route = "76500"; var Ctrl = ""; document.write(" dChild(m);" src="http://c1.microsoft.com/c.gif?">