Creating Custom Combo Boxes

A combo box in Access is a control that acts as both a text box and a dropdown list 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.

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

/J AL^tij Objects

Tables

For in p Reports

'J Ayety.94i2 Liï&Ë.ti' J Ayety;94¿3 lijéis H Ayeiy.'NgjJíTag Labels '.J QJîtomer Dile*. j'y I ïn'ygréBi arij Re<elpt33pf LabelsTempReport Sale; TaX'Pue Rpt U Yt'j.lJ'-'íí^'.L",1 Modules

One way to do this is to add a ComboBox control to your SkipLabels form and simply type into the control's Value List the names of reports on which the user can print labels. For example, you might already have a TextBox control named ReportName on a form, and you want to change the text box 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 property 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 list. 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 we converted the TextBox control named ReportName to a ComboBox control. On the Data tab of that control's property sheet, we 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-right section of Figure 9-7 shows the same combo box open in Form view. The drop-down list shows the report names in the Value List property of the control.

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

Selected control in Design view

Figure 9-7:

The Report Name control as a combo box.

Drop-down list properties Drop-down list in

Form view

An easier approach is 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 always works, even if you add, change, or delete reports or drop the whole chunk of code into an entirely separate database.

Figure 9-7:

The Report Name control as a combo box.

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 property sheet. Choose Form from the property sheet's drop-down list so that you're setting properties for the form as a whole. Then click the Event tab, click the 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(), like this:

Private Sub Form_Load()

End Sub

Any code that you place inside that procedure executes 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-down list. 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

variable.

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

& Chr(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 this example works. The For Each...Next loop loops through each report in the database's Reports collection. For each report, the code 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 3 4. A single quotation mark is character number 3 9. Using Chr(34) in code tells VBA to insert a double quotation mark in place of Chr(34).

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 change the ReportName control's RowSource property to the value of the ValueList variable:

ReportName.RowSource = ValueList ReportName.Requery

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 list 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 these reports print mailing labels, so not all the report names are 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 the 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 property for the ReportName control contains all report names except LabelsTempReport, which got skipped over by the statement

If Not AccessObject.Name = "LabelsTempReport" Then

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. Suppose that we make this rule: Any report in this database that prints labels must have the word labels in its name. If we make that rule and stick to it, we can rewrite the preceding code so that only reports with the word labels 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 "labels". 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 labels 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 list 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 labels in their name, excluding the report named LabelsTempTable.

Listing 9-1: Building a List of Report Names

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 'Don't add LabelsTempReport to the ValueList. If Not AccessObject.Name = "LabelsTempReport" Then

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

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

'Now make ValueList the Value List for the ReportName combo box. ReportName.RowSource = ValueList ReportName.Requery End Sub

The main point to glean from this example, though, is that the drop-down list for a combo box need not be set in concrete. With VBA, you can customize the drop-down list as needed by changing the control's .RowSource property. In this example, the code to build the ReportName drop-down list is executed each time Fancy SkipLabels Form opens. Hence, if any reports have been added, renamed, or deleted since the last time the form opened, the drop-down list still accurately reflects the names of all reports in the current database that contain the word labels.

If you import Fancy SkipLabels Form into an existing database, the dropdown list automatically displays all report names that contain the word labels (excluding LabelsTempReport) in that database. Of course, if that other database didn't follow the rule of including the word labels in all label reports, the procedure as it stands wouldn't work. you would need to either rename reports in that database to follow the rule (which could be disastrous for any existing macro or code that refers to existing report names) or make copies of all existing label reports and rename the copies to include the word labels.

If you already have some other means of uniquely identifying label reports in your database, you can change the rule in the code accordingly. For example, if all the label reports contain the word Avery, you can change the inner If...End If block to exclude report names that don't contain the word Avery, as shown here:

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

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

The Form_Load() procedure executes as soon as you open the form. To fully test the form after creating or changing the Form_OnLoad() event procedure, close and save the form first. Then open it in Form view from the Navigation pane.

Was this article helpful?

0 0

Post a comment