Creating Custom Combo Boxes

A combo box in Access is a control that acts as both a text box and a dropdown menu of options. As you probably know, you can create two types of combo boxes in Access: those that get their values from a table or query, and those that get their values from a simple value list that you type manually.

For example, suppose you have a database that contains a number of reports, as in the example shown in Figure 9-6. Ideally, you'd like to create a Skip Labels dialog box that provides a drop-down list of report names that the user can print labels on.

Figure 9-6:

Sample reports in an Access database.

Figure 9-6:

Sample reports in an Access database.

One way to do this would be to add a ComboBox control to your SkipLabels form and simply type the names of reports on which the user can print labels into the control's Value List. For example, suppose you already have a TextBox control named ReportName on a form, and you want to change that to a combo box. Open the form in Design view, right-click the ReportName control, and choose Change ToOCombo Box. The ReportName text box becomes a combo box (still named ReportName).

On the Data tab of the control's Properties sheet, set the Row Source Type to Value List and then set the Row Source property to the names of reports that you want to see in the drop-down menu. Note: You need to spell each report name exactly as it's spelled in the database. Enclose each report name in quotation marks and also separate names with semicolons. To ensure that the user can choose only a report name from the list, set the Limit to List property to Yes.

Figure 9-7 shows an example where I converted the TextBox control named ReportName to a ComboBox control. On the Data tab of that control's Properties sheet, I set the Row Source Type to Value List and the Row Source to the list of report names as shown here:

"Avery 5197 Labels";"Avery 8462 Labels";"Avery 8463 Labels"; "Avery Name Tag Labels"

The lower half of Figure 9-7 shows that same combo box open in Form view. The drop-down menu lists the report names that show in the Value List property of the control.

Selected control (Design view)

' Fancy SkipLaliels Dialog Box : Form

«eportt-Jame iLabelSTc

Cancel fr Print

Figure 9-7:

The Report Name control as a combo box.

Combo Box: Re|iorlName

ReportName

Format Data Event Other All

Control Source ¡ReportName I

_Input Mask

Row Source Type VajueJJst v

Row Source "Avery 5197 Labels"; "Avery 8462 Labels"; "ftvery 8463 Labels"; "Avery Name Tag Labels"

Bound Column [1_

_Umit To List [Yes

Auto Expand Yes

Default Value

Validation Rule

Validation Text j_

Enabled |Yes_

Locked ¡No

Smart Tags

Menu properties

:-l Fancy SkipLabels Form

P'rintwhich-report? ■Skip how; many labels?;

>very-84&2' Labels

Avery 8463 Labels

I Avery 5197 Labels ¡Avery Name Tag Labels

Drop-down menu in Form view

The drop-down menu in the example shown above is static: It never changes. If you add, delete, or rename reports, those changes won't automatically be reflected in the drop-down menu. To get the drop-down list to work correctly, you need to open the form in Design view and manually change the Value List property from the drop-down menu to reflect current report names.

An easier approach would be to make the drop-down list dynamic so that each time the form opens, VBA can build an accurate, up-to-date list of valid report names for the combo box. That way, the drop-down list will always work even if you add, change, or delete reports, or even drop the whole chunk of code into an entirely separate database.

The CurrentProject.AllReports collection in VBA contains the names of all reports in the current database. If you want the drop-down list to show the names of all reports each time the form opens, you need some sort of code that builds the combo box's Value List from those report names. You also need to attach that code to the form's On Load event, which is triggered each time the form opens and displays any data from its underlying table or query.

In this example, assume that the form is named and the control for which you want to build the Value List is named ReportName. The first step is to open Fancy SkipLabels Form in Design view and get to its Properties sheet. Choose Form from the Properties sheet's drop-down menu so you're setting properties for the form as a whole. Then click the Event tab, click On Load Event, click the Build button, click Code Builder, and then click OK. The VBA editor opens with the cursor inside an event procedure named Form_Load(), as follows:

Private Sub Form_Load()

End Sub

Any code that you place inside that procedure will execute each time the form opens. In this case, you want that code to loop through the AllReports collection, building a string of report names separated by semicolons that you can use as the ValueList for the ReportName drop-menu. The following code creates that semicolon-delimited list of report names from all reports in the current database:

Private Sub Form_Load()

'ValueList variable will store a string that can

'be used as the Value List property for a combo box.

Dim ValueList As String

ValueList = ""

'Loop through all report names.

For Each AccessObject In CurrentProject.AllReports

'Add current report name and semicolon to ValueList

vari

able.

ValueList = ValueList + Chr(34) + AccessObject.Name

+ Ch

r(34) + ";"

Next

'Now make ValueList the Value List for the ReportName

combo

box.

Debug.Print ValueList

ReportName.RowSource = ValueList

ReportName.Requery

End Sub

Take a moment to see how that works. The For Each...Next loop loops through each report in the database's Reports collection. For each report, it adds a quotation mark (specified as Chr(34) in the code), the report name, another quotation mark, and a semicolon.

Every character on your keyboard has an ASCII number assigned to it. For example, a double-quotation mark is character number 34. A single quotation mark is character number 39. Using Chr(34) in code tells VBA to insert a double-quotation mark in place of Chr(34).

So with each pass through the loop, the variable named ValueList gets another report name enclosed in quotation marks, followed by a semicolon. As written, the loop just adds every report name to the ValueList variable. So, referring back to the report names shown in Figure 9-6, by the time the loop has looked at every report name in the database, the ValueList variable contains this:

"Avery 8463 Labels";"Avery 8462 Labels";"Avery 5197

Labels";"Customer Directory";"Avery Name Tag Labels";"Vendor Directory";"Invoices and Receipts Rpt";"Sales Tax Due Rpt";"LabelsTempReport";

The next lines

ReportName.RowSource = ValueList ReportName.Requery change the ReportName control's RowSource property to that new ValueList variable. The ReportName.Requery statement just makes sure that the form is aware of the change so that the combo box always shows the correct names. By the time the procedure has run, in this example, the ReportName combo box drop-down menu would contain these options:

i Avery 8463 Labels i Avery 8462 Labels i Avery 5197 Labels i Customer Directory i Avery Name Tag Labels i Vendor Directory i Invoices and Receipts Rpt i Sales Tax Due Rpt i LabelsTempReport

There are a couple of little problems here. For one, not all of these reports print mailing labels, so not all of the report names are really appropriate for the SkipLabels procedure. Also, LabelsTempReport isn't really a valid report name: It's just a temporary report name created by the SkipLabels procedure.

If you want to exclude LabelsTempReport from the drop-down list, you need to modify the code so that name isn't added to the ValueList variable. The necessary lines to be added are shown here in boldface:

Dim ValueList As String

ValueList = ""

'Loop through all report names.

For Each AccessObject In CurrentProject.AllReports

'Don't add LabelsTempReport to drop-down menu.

If Not AccessObject.Name = "LabelsTempReport" Then

'Add current report name and semicolon to ValueList variable.

ValueList = ValueList + Chr(34) + AccessObject.Name +

Chr(34) + ";"

End If

Next

'Now make ValueList the Value List for the ReportName combo

box.

ReportName.RowSource = ValueList

ReportName.Requery

By the time all the preceding code is executed, the ValueList for the ReportName control contains all report names except LabelsTempReport, which got skipped over by the statement

If Not AccessObject.Name = "LabelsTempReport"...

You can narrow the list of report names to just those reports that can print labels, but you need some means of being able to tell those reports apart from other ones. For example, suppose I make the rule Any report in this database that prints labels must have the word label in its name. If I make that rule and stick to it, I can rewrite the preceding code so that only reports with the word label in the name are added to ValueList, as shown in boldface here:

'ValueList variable will store a string that can 'be used as the Value List property for a combo box. Dim ValueList As String ValueList = ""

'Loop through all report names. For Each AccessObject In CurrentProject.AllReports 'Don't add LabelsTempReport to the Value List. If Not AccessObject.Name = "LabelsTempReport" Then

'Only add report names that contain the word "label". If InStr(AccessObject.Name, "Labels") > 1 Then

'Add current report name and semicolon to ValueList variable. ValueList = ValueList + Chr(34) + _ AccessObject.Name + Chr(34) + ";" End If End If Next

'Now make ValueList the Value List for the ReportName combo box.

ReportName.RowSource = ValueList

ReportName.Requery

Excluding LabelsTempReport and any other reports that don't have the word label in their names creates the following string in the ValueList variable and ultimately in the ValueList property of the ReportName combo box. Hence, the ValueList string ends up containing

"Avery 8463 Labels";"Avery 8462 Labels";"Avery 5197 Labels"; "Avery Name Tag Labels";

which means that the drop-down menu for the ReportName combo box ends up containing these options:

i Avery 8463 Labels i Avery 8462 Labels i Avery 5197 Labels i Avery Name Tag Labels

Listing 9-1 shows the complete procedure with the ability to build the list of report names from only those reports that have the word label in their name, excluding the report named LabelsTempTable.

0 0

Post a comment