Microsoft Knowledge Base Wiki

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.

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.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.
    

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:

    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:

   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.

10. Preview the report. The records that follow appear on the
    first page:

    Product Name	Unit Price	Unit in Stock

    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

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

For additional information, click the following article
numbers to view the articles in the Microsoft Knowledge Base:

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
(http://msdn2.microsoft.com/en-us/library/aa188513(office.10).aspx)

---

APPLIES TO

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

kbprogramming kbvba kbinfo kbhowto KB812719

When you edit a record in a form that is based on a multi-table view, and you are working in a multi-user environment, you may be unsuccessful when you try to save the changes to the record. You may also receive the following error message in the Write Conflict dialog box:

If you click either Copy to Clipboard or Drop Changes in the Write Conflict dialog box, you may notice that the changes that you made to the record are undone, and that the changes to the record that were made by the other user are now visible. However, if you edit the same record again, and you try to save the record, you may receive the error message that is mentioned in the “Symptoms” section again.

You may also notice the error message that is mentioned in the “Symptoms” section intermittently when you click Save Record.

Note You may not see this behavior when you use Microsoft Access 2000.

To work around this problem, use one of the following methods:

• Update the form that is based on the multi-table view

  On the first occurrence of the error message that is mentioned in the “Symptoms” section, you must click either Copy to Clipboard or Drop Changes in the Write Conflict dialog box. To avoid the repeated occurrence of the error message that is mentioned in the “Symptoms” section, you must update the recordset in the form before you edit the same record again.

  Notes

  • To update the form in Access 2003 or in Access 2002, click Refresh on the Records menu.
  • To update the form in Access 2007, click Refresh All in the Records group on the Home tab.
• Use a main form with a linked subform

  To avoid the repeated occurrence of the error message that is mentioned in the “Symptoms” section, you can use a main form with a linked subform to enter data in the related tables. You can enter records in both tables from one location without using a form that is based on the multi-table view.

  To create a main form with a linked subform, follow these steps:

  1. Create a new form that is based on the related (child) table that is
     used in the multi-table view. Include the required fields on the form.
  2. Save the form, and then close the form.
  3. Create a new form that is based on the primary table that is used in
     the multi-table view. Include the required fields on the form.
  4. In the Database window, add the form that you saved in step 2 to
     the main form.

     This creates a subform.

  5. Set the Link Child Fields property and
     the Link Master Fields property of the subform to the name of the field or fields that are
     used to link the tables.

Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the “Applies to” section.

In a multi-user environment, Microsoft Access project (.adp) uses a technique that is named optimistic record locking to handle record contention. Therefore, when more than one user is working with the same record at the same time, one of the users may receive the error message that is mentioned in the “Symptoms” section. However, the error message that is mentioned in the “Symptoms” section may also intermittently appear when all the following conditions are true:

• The form is based on a multi-table view.
• The multi-table view is based on the tables that are involved in a parent-child relationship.
• The record that is being edited has been changed and committed by another user since you began editing the record.

You may notice that the same problem occurs when you use the multi-table view directly in a multi-user environment.

Steps to reproduce the problem

1. Start Access.
2. Open the NorthwindCS.adp sample database project.
3. In the Database window, click Queries in the Objects section.

   Note In Access 2007, click Query Wizard in the Other group on the Create tab.

4. In the right pane, double-click Create view in designer.

   Note In Access 2007, in the New Query dialog box, click Design View, and then click OK.

5. In the Add Table dialog box, double-click both Orders and Order Details on the Tables tab, and then click Close.
6. Type or paste the following query in the SQL pane:

   SELECT
   	dbo.Orders.OrderID,
   	dbo.Order Details.ProductID,
   	dbo.Order Details.Quantity,
   	dbo.Orders.ShipName
   FROM
   	dbo.Orders
   	INNER JOIN
   	dbo.Order Details
   	ON
   		dbo.Orders.OrderID = dbo.Order Details.OrderID

   Notes

   • In Access 2003 or in Access 2002, if the SQL pane is not visible, point to Show Panes on the View menu, and then click SQL.
   • In Access 2007, on the Design tab, click SQL in the Tools group to open the SQL pane.
7. Save the view asOrderView.
8. On the View menu, click Datasheet View.

   Note In Access 2007, on the Design tab, click the arrow under View, and then click Datasheet View.

9. Edit a record in the OrderView view.

   Note Make sure that the record has not been saved.

10. Open another instance of the NorthwindCS.adp sample database project.
11. Edit the same record that you edited in step 9.
12. On the Records menu, click Save Record.

    Note In Access 2007, click Microsoft Office Button, and then click Save.

    You may receive the error message that is mentioned in the “Symptoms” section.

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

---

APPLIES TO

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

kbtshoot kbexpertiseinter kbuser kbadp kberrmsg kbprb KB837937

For a Microsoft Access 2002 version of this article,
see
286345 

(http://support.microsoft.com/kb/286345/
)
.

For a Microsoft Access 2000 version of this article,
see
202312 

(http://support.microsoft.com/kb/202312/
)
.

Explains a problem that may occur when you convert an Access
2003 database to an Access 97 database. If you try to open a form or a report
in the Access 97 database, you receive an error message. The problem occurs if
the form or the report have an ActiveX control.

When you convert
a Microsoft Office Access 2003 database to a Microsoft Access 97 database and
then you open a form or a report in the new Access 97 database, you may receive
the following error message:

This bug occurs if the form or the report contains at least
one Microsoft ActiveX control. Access 2003 uses the IPersistStream interface to
insert and to store ActiveX controls. However, Access 97 uses the
IPersistStorage interface that contains the persistence information to insert
and to store ActiveX controls. The mismatch between the interface that Access
2003 uses and the interface that Access 97 uses causes the error that is
mentioned in the “Symptoms” section of this article.

To resolve this bug, follow these steps.

Note The following steps modify your Access 97 database. Microsoft
recommends that you back up your database before you continue.

1. Start Access 97.
2. Open the Access 97 database that you converted from an
   Access 2003 database.

   You receive the following message:

   Microsoft Access has converted the code in
   ‘Drive:PathFile
   Name.mdb’ to your current version of Visual Basic.

   To
   improve the performance of the Access 97 database, follow these steps:

   1. Open an available module in the Access 97 database in
      Design view.
   2. On the Debug menu, click
      Compile and Save All Modules.
3. Click OK.
4. Open a form that contains at least one ActiveX control.

   You may receive the following error message:

   There is no object in this control.

5. Click OK.

   When you click
   OK, the form opens in Form view. The form contains an empty
   white control for the ActiveX control.

6. On the View menu, click Design
   View.

   You may receive the error message that is mentioned in
   step 4 again.

7. Click OK.

   The form opens in
   Design view.

8. Right-click the ActiveX control, and then click
   Properties.
9. Notice the values that are assigned to the following
   properties:
   • Name
   • Enabled
   • Left
   • Top
   • Width
   • Height
   • All Event properties
10. Delete the ActiveX control.
11. Re-insert the ActiveX control that is included with Access
    97 or with Microsoft Office 97 Developer Edition.

    For example, if the
    form or the report contains Microsoft Calendar Control 10.0 or a later version
    for Access 2003, delete the control and then re-insert Microsoft Calendar
    Control 8.0 (the version that is included with Access 97).

    To insert
    an ActiveX control, follow these steps:

    1. On the Insert menu, click
       ActiveX Control.
    2. In the Insert ActiveX Control dialog
       box, click the appropriate control on the list, and then click
       OK.
12. View the properties of the new ActiveX control, and then
    assign the respective values to these properties based on your information from
    step 9.

Microsoft has confirmed that this is a bug in the Microsoft products that are
listed at the beginning of this article.

Steps to Reproduce the Behavior

1. Start Access 2003.
2. Create a new Access 2003 database.
3. In the Database window, click Forms under
   Objects.
4. In the right pane, double-click Create Form in
   Design view.
5. On the Insert menu, click ActiveX
   Control.
6. Click to select Calendar Control 10.0 or a
   later version, and then click OK.
7. Close the form. Save the form as Form1.
8. In the Database window, point to Database
   Utilities, point to Convert Database, and then click
   To Access 97 File Format on the Tools
   menu.

   Note You may receive the following message:

   Saving the database to the previous version of Access will
   create a new database that contains none of the security information that you
   have set. Are you sure you want to proceed?

   Click
   Yes.

9. In the Convert Database Into dialog box,
   type the appropriate file name for the new Access 97 database, and then click
   Save.

   Note After this conversion, you receive the following message:

   After you have converted this file to Access 97 File Format, any
   functionality specific to Access 2000 or Access 2002 or later will be
   lost.

   For more information about conversion, click Help.

   Click OK.

10. Start Access 97.
11. Open the Access 97 database that you created.
12. Open Form1.

    When you try to open the form, you
    receive the error message that is mentioned in the “Symptoms” section of this
    article.

---

APPLIES TO

• Microsoft Office Access 2003
• Microsoft Access 97 Standard Edition

kbdatabase kbconversion kberrmsg KB824270