Linking Lists

One of the many reasons for programming ListBox and ComboBox controls is to create linked lists, where the options in one control depend on what's selected in another control. As an example, Figure 10-11 shows a form named

Fancy SkipLabels Dialog Box that contains three dynamic combo boxes named LabelRpt, FldToSearch, and ValueToFind. Because the fourth combo box (not pointed out) is static, its drop-down list never changes.

LabelRpt

Figure 10-11:

Fancy SkipLabels form dynamic controls.

3 Fartu/-1 SkipLabels Dialog Box

Fancy SkipLabel

Ft ht. ,;'/t .Lh i Hpur LP Labels tabsts-taäaji | 3 PnntWH^t? — O All records

Search Field . tair|:anv:in .koobFopValue iLastName v| = fy] |ccstKIIc

Close Preview. Print

FldToSearch

ValueToFind

The names of the dynamic controls and the relationships between the control are summarized here:

1 LabelRpt: This ComboBox lists names of all reports in the current database that contain the word label. It needs to be filled once — the moment when the form opens.

1 FldToSearch: This ComboBox lists the names of fields from the selected report's underlying table or query. The list needs to be updated each time the user chooses a report to print from the LabelRpt control.

1 ValueToFind: This displays a list of all unique values in the field selected in the FldToSearch combo box. Each time the user chooses a field to search on, this combo box needs to be changed to list values from the selected field.

You can envision the relationships between the combo boxes as dependencies, in the sense that the exact items in a combo box depend on what's selected and available at the moment. For example, what appears in the FldToSearch combo box depends on what report is selected at the moment in LabelRpt. Similarly, what appears in the ValueToFind combo box depends on what field name is selected in the FldToSearch control. As is always the case, just writing the code to make these controls always show the "right stuff" is only part of the problem. You also have to control exactly when that code runs. Look at some examples of that first, which we follow with some of the code.

To make life simpler, we encapsulated the code that updates each combo box as its own little procedure. The fancy programming term encapsulation translates to something along the lines of Save yourself from having to deal with this problem more than once. For example, if we create a procedure named UpdateFldToSeachCombo() and make its job to ensure that the FldToSearch control is up-to-date, we don't have to worry about when the code gets executed. We can just tie the statement UpdateFldToSeachCombo to any event on any control in the form when we want that event to update the FldToSearch control.

That's sort of a programming strategy. To encapsulate the code needed to update each of the three dynamic controls shown in Figure 10-11, we wrote three separate procedures and named each one so that it describes what it does. The names of those procedures are

1 Sub UpdateLabelRptCombo(): This procedure updates the list of reports in the LabelRpt combo box on the form to accurately reflect label reports in the current database.

1 Sub UpdateFldToSearchCombo(): This procedure ensures that the FldToSrch combo box accurately reflects the names of fields in the selected report's recordsource. It allows the user to choose a field name on which to create a filter.

1 Sub UpdateValueToFindCombo(): As its name suggests, this procedure ensures that the unique values displayed in the ValueToFind combo box accurately reflect the contents of the field specified in the

FldToSearch control.

The advantage of creating these procedures is that we could just concentrate on getting each one to work (at all) without worrying about when the procedure will do its thing. In our code, when we want to tie the procedure to a particular event, the triggered procedure need only call the appropriate Sub procedure to get its job done. Again, we look at each procedure in a moment. Just focus on the when for now.

Running code when a form opens

If you want a procedure to execute as soon as a form opens and any data from the form's underlying table or query has been loaded into the form, tie a procedure to the forms On Load event. The name of that procedure, in every form, is Form_Load(). The Form_Load() procedure for the sample form shown in Figure 10-11 looks something like this:

Private

Sub Form_Load()

Call

UpdateLabelRptCombo

Call

UpdateFldToSearchCombo

Call

UpdateValueToFindCombo

End Sub

In the form's Design view, make sure that the property sheet shows the word Form in the drop-down list. Double-clicking the gray area behind the form's Design grid or the gray box where the rulers meet instantly displays Form properties in the property sheet.

The basic logic of the Form_Load() procedure is straightforward: It simply updates each of the three ComboBox controls in the order that they need to be updated. When the form opens, each ComboBox control has actual, reasonable data in its drop-down list.

Suppose that the form is open and the user chooses a report name from the LabelRpt drop-down list. When that happens, the two controls beneath LabelRpt need to have their drop-down lists updated. First the FieldToSearch drop-down list needs to be updated to reflect field names from the selected report's recordsource (underlying table or query). Then, after that control gets a new value, the ValueToFind drop-down list needs to be updated to reflect legitimate values for the selected field name. To make that happen, a change to the LabelRpt control needs to run two of the update procedures. Here's the AfterUpdate event procedure for the LabelsRpt control:

Private

Sub LabelRpt_AfterUpdate()

Call

UpdateFldToSearchCombo

Call

UpdateValueToFindCombo

End Sub

The preceding procedure says, "After the user chooses a different report to print, update the FldToSearch and ValueToFind combo boxes on this form."

Running code when the user makes a choice

To make a procedure execute after the user chooses an option from a combo or list box, tie the procedure to the control's After Update event property. For example, when the user chooses a different field to search on from the Search Field option on the Fancy SkipLabels form (the FldToSearch control), the Look For Value drop-down list needs to be updated to show unique values from that field. To make sure that the ValueToList control gets updated whenever the user chooses a different field to search, we added the following procedure to the form's class module:

Private Sub FldToSearch_AfterUpdate()

Call UpdateValueToFindCombo End Sub

The preceding class procedure says, "After the user chooses a different field to search on, update the Value to Find combo box to list unique values from the specified field."

Getting fancy SkipLabels

You can download the Fancy SkipLabels Dialog Box form and all its code from www. dummies.com/go/access2007vbaprog. You won't find any standard modules in that database. All the code for Fancy SkipLabels Dialog Box are in the form's class module. If you look at that code, you see more than just what's shown in this chapter. (That's because much of the code there isn't relevant to this chapter's topic.)

To use Fancy SkipLabels Dialog Box in your own database, you first need to create at least one report for printing labels and also make sure to save that report with the word label in its name so that SkipLabels finds the report. Then you need to import Label SettingsTableand Fancy SkipLabels Dialog Box Form from the downloaded database into your own database. The Web site provides more information.

Getting back to the encapsulation strategy, you can see that it wouldn't be too tough to make other events on other controls update any dynamic list on the form. Just click the control, click its After Update event property, and add the code needed to call the appropriate procedures in the event procedure.

The various preceding called procedures all follow the examples presented earlier in this chapter. For example, the LabelRpt control, which displays a drop-down list of reports with the word label in their names, gets its information from the AllReports collection (see Listing 10-5):

Listing 10-5: Updating a Combo Box of Report Names

'** UpdateLabelRptCombo() updates the LabelRpt control. Private Sub UpdateLabelRptCombo()

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

'Get names of label reports from AllReports collection, 'and assemble into a valid Value List for a Combo Box. Dim rpt As AccessObject

For Each rpt In CurrentProject.AllReports

'Don't add LabelsTempReport to the ValListVar. If Not rpt.Name = "LabelsTempReport" Then

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

'Add label report names to ValListVar with Double Quotes. ValListVar = ValListVar & Chr(34) & rpt.Name & Chr(34) & ";" End If End If Next

'ValListVar now contains valid report names, so next 'lines make it the Row Source property for LabelRpt. MelLabelRpt.RowSourceType = "Value List" MelLabelRpt.RowSource = ValListVar MelLabelRpt.Requery End Sub

The UpdateFldToSearchCombo procedure updates the drop-down list in the FldToSearch control. The code gets the name of the label report to print from the LabelRpt control on the form (referred to as MelLabelRpt.Value in the code). It then (invisibly) opens that report in Design view and copies its Record Source property (which is the name of the report's underlying table or query) into its own variable named LabelRecSource. After that, the rest of the code sets the control's Row Source Type to Field List and the Row Source to the name that's stored in that LabelRecSource variable. Listing 10-6 shows the whole procedure with comments to help explain each step.

Listing 10-6: Updating a Combo Box with Field Names

'** UpdateFldToSearchCombo() updates the FldToSearch Combo Box. Private Sub UpdateFldToSearchCombo()

'Open specified report in Design view.

DoCmd.OpenReport MelLabelRpt.Value, acViewDesign, , , acHidden

'Copy its recordsource name to LabelRecSource variable. Dim LabelRecSource As String 'Placeholder for record source.

LabelRecSource = Reports(Reports.Count - 1).RecordSource

'Close the report (only needed to grab record source). DoCmd.Close acReport, MelLabelRpt.Value, acSaveNo

'Set FldToSearch Combo Box Row Source properties. MelFldToSearch.RowSourceType = "Field List" MelFldToSearch.RowSource = LabelRecSource MelFldToSearch.Requery

End Sub

The last dynamic control on the form, ValueToFind, gets updated by a Sub procedure named UpdateValueToFindCombo. This procedure updates the list of unique values in the control's drop-down list to accurately reflect unique values in whatever field the user specified in the FldToSearch control. The Row Source Type for the control needs to be Table/Query, and the Row Source has to be a valid SQL statement that specifies what to display. The code in Listing 10-7 builds a valid SELECT DISTINCT... query for whatever field's name is selected in the FldToSearch control (MelFldToSearch.Value in VBA). Listing 10-7 holds the whole procedure with comments.

Listing 10-7: Updating a Combo Box from a Table

Private Sub UpdateValueToFindCombo()

'Build a SQL statement to pull unique values 'from whatever field name is selected in form. '(If FldToSearch is empty, do nothing) If Not IsNull(MelFldToSearch.Value) Then Dim MySQL As String

MySQL = "SELECT DISTINCT [" & FldToSearch.Value & "]"

MySQL = MySQL & " FROM [" & LabelRecSource & "]"

MySQL = MySQL & " WHERE [" & FldToSearch.Value & "] Is Not Null"

MySQL = MySQL & " ORDER BY [" & FldToSearch.Value & "]"

'Now that we have the right SQL statement, make it the 'Row Source for the ValueToFind control. MelValueToFind.RowSourceType = "Table/Query" MelValueToFind.RowSource = MySQL MelValueToFind.Requery End If End Sub

In case you're wondering about the If Not IsNull(!FldToSearch.Value) Then...End If statements, we originally wrote the procedure without those. At first, the procedure seemed to work fine when tested. But then we discovered that if the FldToSearch control is null (empty) when UpdateValueTo FindCombo is called, the procedure crashes and yelps out an error message. To ward off that irritant, we make execution of the code dependent on the FldToSearch control's not being null. In other words, the procedure executes only if a field name is selected in the FldToSearch control. Otherwise, the procedure does nothing to prevent the error from occurring.

From a programming perspective, the main thing to remember is that every ListBox and ComboBox control that you create exposes many properties to VBA. Two of those properties, Row Source Type and Row Source, give you strong programmatic control over the choices presented by those controls.

+1 0

Post a comment