Adding Controls to the Report

The CreateReportControl method allows you to add new controls to a report. The CreateReport Control method has the following basic syntax.

CreateReportControl(ReportName, ControlType, Section, Parent, ColumnName, Left, Top, Width, Height)

Now, let's modify the prior code to add four fields to the report: CompanyName, ContactName, Title, and Phone. Each field needs a corresponding text box and label. The following modified procedure is one way to accomplish this.

Sub CreateNewReport()

Dim rptCustomers As Access.Report Dim strReportName As String Dim aoAccessObj As AccessObject

Dim txtTextBox As Access.TextBox Dim lblLabel As Access.Label Dim strSQL As String Dim intPosition As Integer

'set the name of the new report strReportName = "Customers"

strSQL = "SELECT * FROM Customers ORDER BY CompanyName"

'delete any existing report with that name

For Each aoAccessObj In CurrentProject.AllReports

If aoAccessObj.Name = strReportName Then

DoCmd.DeleteObject acReport, strReportName End If Next aoAccessObj

'create a new Customers Report Set rptCustomers = CreateReport

With rptCustomers

'set the report record source to the SQL Statement .RecordSource = strSQL

'set the height, caption, and other report options .Section("Detail").Height = 500 .Caption = "Client Contact List" End With

'add a Company Name label and text box to the report intPosition = 0

Set txtTextBox = CreateReportControl(rptCustomers.Name, acTextBox, acDetail, , "CompanyName", intPosition) txtTextBox.Name = "txtCompanyName" txtTextBox.Width = 1800

Set lblLabel = CreateReportControl(rptCustomers.Name, acLabel, _

acPageHeader, , , intPosition) lblLabel.Name = "lblCompanyName" lblLabel.Caption = "Company Name" lblLabel.Height = txtTextBox.Height lblLabel.Width = txtTextBox.Width lblLabel.FontBold = True

'add a Contact Name label and text box to the report intPosition = txtTextBox.Width + txtTextBox.Left + 350

Set txtTextBox = CreateReportControl(rptCustomers.Name, acTextBox, acDetail, , "ContactName", intPosition) txtTextBox.Name = "txtContactName" txtTextBox.Width = 1800

Set lblLabel = CreateReportControl(rptCustomers.Name, acLabel, _

acPageHeader, , , intPosition) lblLabel.Name = "lblContactName" lblLabel.Caption = "Contact Name" lblLabel.Height = txtTextBox.Height lblLabel.Width = txtTextBox.Width lblLabel.FontBold = True

'add a Contact Title label and text box to the report intPosition = txtTextBox.Width + txtTextBox.Left + 500

Set txtTextBox = CreateReportControl(rptCustomers.Name, acTextBox, acDetail, , "ContactTitle", intPosition) txtTextBox.Name = "txtTitle" txtTextBox.Width = 1800

Set lblLabel = CreateReportControl(rptCustomers.Name, acLabel, _

acPageHeader, , , intPosition) lblLabel.Name = "lblTitle" lblLabel.Caption = "Title"

lblLabel.Height = txtTextBox.Height lblLabel.Width = txtTextBox.Width lblLabel.FontBold = True

'add a Contact Phone label and text box to the report intPosition = txtTextBox.Width + txtTextBox.Left + 1000

Set txtTextBox = CreateReportControl(rptCustomers.Name, acTextBox, acDetail, , "Phone", intPosition) txtTextBox.Name = "txtPhone"

Set lblLabel = CreateReportControl(rptCustomers.Name, acLabel, _

acPageHeader, , , intPosition) lblLabel.Name = "lblPhone" lblLabel.Caption = "Phone" lblLabel.Height = txtTextBox.Height lblLabel.Width = txtTextBox.Width lblLabel.FontBold = True

'save and close the new report DoCmd.Save , strReportName DoCmd.Close

End Sub

In the code example shown again in the following section, notice how a text box and label control are declared and then used to create each of the fields on the report. For example, txtTextBox is assigned to the result of the CreateReportControl method, which specifies the report name, type of control, section to add the control, name of the control, and the position in which to place the control. Various other properties of the text box control are specified, such as the Name and Width properties. The corresponding label control is also created and various settings specified.

'add a Company Name label and text box to the report intPosition = 0

Set txtTextBox = CreateReportControl(rptCustomers.Name, acTextBox, _

acDetail, , "CompanyName", intPosition) txtTextBox.Name = "txtCompanyName" txtTextBox.Width = 1800

Set lblLabel = CreateReportControl(rptCustomers.Name, acLabel, _

acPageHeader, , , intPosition) lblLabel.Name = "lblCompanyName" lblLabel.Caption = "Company Name" lblLabel.Height = txtTextBox.Height lblLabel.Width = txtTextBox.Width lblLabel.FontBold = True

The same basic steps are then repeated to create each control for the report.

Try It Out Creating a Report Programmatically

Now it's your turn to create a report programmatically. This example creates a report based on a new table that you create and uses a SQL statement as the report's RecordSource property.

1. Create a new database by selecting File C> New C> Blank Database. Specify Ch8CodeExamples for the filename and click the Create button.

2. Add a new tblComplaints table to the database as shown in Figure 8.1.

-

tblComplaints : Table

a

Field Name

Data Typs

Description

v

m

Complamild

AutoNumber

Unique identifier (Eiing Inteoer. Pnrmarv Key)

ComplaintDate

Date iTirne

Date of Complaint

CustomerName

Text

Name of cusiomer :'Fie!d Size 50}

CustomerDavPhone

Test

Day Phone :'Fieid size 12)

CustomeiEveninaPhone

Text

Evenina Phone I'Field size 12:

IssueDescnotion

Menno

Descnption of Issue

Resolved

Yes/No

Resolved [Boolean - Yes or No'

ResolutionDescnption

Menno

Description of Resolution

Figure 8.1

3. Open the table and add some records, such as those shown in Figure 8.2.

_j tblComplaints

Table

ComplaintDate

CustomerName

CustomerDavPI

CustomerEvenir

IssLieDescription

Resolved

ResolutionDesc

A

4/23/2004

John Doe

501-723-1111

501-723-2222

Leaky faucet

4/23/2004

Jack Smith

987-E54-3211

937-654-3211

Storm door that will not shut

4/24/2004

Betty Doe

111-222-3333

111-222-3333

Neighbor in other apartment has loud parties

4/25/2004

Amber Smith

999-888-7777

999-888-4444

Stove in kitchen not working

Record: i H \

1 Ut*J H \m\ of 4

<

| III!

_I

>

Figure 8.2

4. Insert a new module into the database. To do so, choose Modules from the Database Window and click the New button.

5. Add the following CreateComplaintsReport procedure to the module.

Sub CreateComplaintsReport()

Dim rptComplaints As Access.Report Dim strReportName As String

Dim intPosition As Integer 'set the name of the new report strReportName = "Unresolved Customer Complaints"

strSQL = "SELECT * FROM tblComplaints WHERE Resolved=False"

'create a new Unresolved Customer Complaints Report Set rptComplaints = CreateReport

With rptComplaints

'set the report record source to the SQL Statement .RecordSource = strSQL

'set the height, caption, and other report options .Section("Detail").Height = 500 .Caption = "Unresolved Customer Complaints" End With

'add a Customer Name label and text box to the report intPosition = 0

Set txtTextBox = CreateReportControl(rptComplaints.Name, acTextBox, acDetail, , "CustomerName", intPosition) txtTextBox.Name = "txtCustomerName" txtTextBox.Width = 1800

Set lblLabel = CreateReportControl(rptComplaints.Name, acLabel, _

acPageHeader, , , intPosition) lblLabel.Name = "lblCustomerName" lblLabel.Caption = "Customer Name" lblLabel.Height = txtTextBox.Height lblLabel.Width = txtTextBox.Width lblLabel.FontBold = True

'add a Customer Day Phone label and text box to the report intPosition = txtTextBox.Width + txtTextBox.Left + 350

Set txtTextBox = CreateReportControl(rptComplaints.Name, acTextBox, acDetail, , "CustomerDayPhone", intPosition) txtTextBox.Name = "txtCustomerDayPhone" txtTextBox.Width = 1800

Set lblLabel = CreateReportControl(rptComplaints.Name, acLabel, _

acPageHeader, , , intPosition) lblLabel.Name = "lblCustomerDayPhone" lblLabel.Caption = "Day Phone" lblLabel.Height = txtTextBox.Height lblLabel.Width = txtTextBox.Width lblLabel.FontBold = True

'add a Customer Evening Phone label and text box to the report intPosition = txtTextBox.Width + txtTextBox.Left + 500

Set txtTextBox = CreateReportControl(rptComplaints.Name, acTextBox, acDetail, , "CustomerEveningPhone", intPosition) txtTextBox.Name = "txtCustomerEveningPhone" txtTextBox.Width = 1800

Set lblLabel = CreateReportControl(rptComplaints.Name, acLabel, _ acPageHeader, , , intPosition)

lblLabel.Name = "lblCustomerEveningPhone" lblLabel.Caption = "Evening Phone" lblLabel.Height = txtTextBox.Height lblLabel.Width = txtTextBox.Width lblLabel.FontBold = True

'add an Issue Description label and text box to the report intPosition = txtTextBox.Width + txtTextBox.Left + 1000

Set txtTextBox = CreateReportControl(rptComplaints.Name, acTextBox, _

acDetail, , "IssueDescription", intPosition) txtTextBox.Name = "txtIssueDescription" txtTextBox.Width = 2000 txtTextBox.Height = 750

Set lblLabel = CreateReportControl(rptComplaints.Name, acLabel, _

acPageHeader, , , intPosition) lblLabel.Name = "lblIssueDescription" lblLabel.Caption = "Issue Description" lblLabel.Height = txtTextBox.Height lblLabel.Width = txtTextBox.Width lblLabel.FontBold = True

'save and close the new report DoCmd.Save , strReportName DoCmd.Close

End Sub

6. Run the procedure from the Immediate Window by typing the procedure name CreateComplaintsReport and pressing Enter.

7. Return to the Database Window and select Reports. The newly created CreateComplaintsReport should appear in the Database Window, as shown in Figure 8.3.

J ChSCodeExamples : Database (Access 2002 -...

Preview / Design u New X Ht

Objects J Tables Queries :il Forms

^J Create report in Design view Create report by using wizard

□ iUnresolvec! Customer Complaints;

U Reports

w

8. Open Unresolved Customer Complaints to preview the report. A screen similar to the one shown in Figure 8.4 is displayed.

Figure 8.4
0 0

Responses

  • clare
    How to generate access report based on textbox?
    8 years ago
  • Obo
    How to access the controls on a report access 2003?
    8 years ago

Post a comment