Applying VBA in the Real World

A list box doesn't have a List Rows or List Width property because there's no drop-down list in a list box. The width and height of the ListBox control, as a whole, determine the width and length of the list. There's no Limit To List property for a list box because there's no optional text box in which the user could type a value. With a list box, the user is always required to choose an option in the list.

Combo boxes and list boxes are both examples of list controls (in that they show some sort of list to the user). After the preceding quick peek at some commonly used properties of those controls, read on to take a look at how you work those puppies.

In the form's Design view, you can easily change a text box to a combo box or to a list box or whatever. Just right-click the control that you want to change and then choose Change ToOxx (the type of control you want).

Listing field names

If you want a list box or combo box to list the names of fields in a table or query, set the control's Row Source Type property to Field List and set its Row Source property to the name of the table or query that contains the fields whose names you want to list.

For example, Figure 10-3 shows a ComboBox control named FldNamesCombo on a form. As you can see in the property sheet, its Row Source Type is set to Field List, and its Row Source is set to Customers. The names in the control's drop-down list (CustID, FirstName, LastName, and so forth) are field names from a table named Customers.

Figure 10-3:

Row Source properties for a Field List combo box.

Figure 10-3:

Row Source properties for a Field List combo box.

From a VBA standpoint, if you want the FldNamesCombo control to show field names from a different table or query in response to some event, change the control's .RowSource property to the name of the table or query from which you want the control to get field names. For example, this statement sets the Row Source property of the control named FldNamesCombo to a table named Products (so the control shows field names from the Products table):

MelFldNamesCombo.RowSource = "Products"

The Me! in these examples refers to the form to which the control is attached and works only from a class module. From a standard module, Me! would have to be replaced with the full identifier for the open form — for example:

Forms![EzQueryFrm]![FldNamesCombo].RowSource = "Products"

if the control is on an open form named EZQueryFrm.

In your code, you can take extra steps to make sure that the control's Row Source Type is set correctly to Field List before putting in the new table name. After the field receives its new list, you can use the statement

Me!FldNamesCombo.Value

Me!FldNamesCombo.ItemData(0)

to set the selected option in a combo box to the first item in the drop-down list. Here's all the code together to change the drop-down list:

'Make sure the control's Row Source Type is Field List. Me!FldNamesCombo.RowSourceType = "Field List"

'Change the Row Source table to Products table. Me!FldNamesCombo.RowSource = "Products"

'Set combo box value to first item in drop-down list. Me!FldNamesCombo.Value = Me!FldNamesCombo.ItemData(0)

Using the keyword Me! in the preceding examples assumes that the code is in the class module for whatever form the FldNamesCombo control is on. To change the FldNamesCombo properties from a standard module or another form's class module, include the complete identifier for the open form. For example, if the FldNamesCombo control is on a form named EZQueryFrm, the complete identifier for the form is Forms![EzQueryFrm]! rather than Me!. The complete identifier to the FldNamesCombo control is Forms![EzQueryFrm]![FldNamesCombo].

In code, you can spell out the complete identifier in each line of code, like this:

'Make sure the control's Row Source Type is Field List. Forms![EzQueryFrm]![FldNamesCombo].RowSourceType = "Field List"

'Change the Row Source table to Products table. Forms![EzQueryFrm]![FldNamesCombo].RowSource = "Products"

'Set combo box value to first item in drop-down list. Forms![EzQueryFrm]![FldNamesCombo].Value _

= Forms![EzQueryFrm]![FldNamesCombo].ItemData(0]

To avoid typing Forms![EzQueryFrm]![FldNamesCombo] repeatedly in your code, define a Control object variable that refers to the control through a shorter name:

'Make short name MyControl refer to 'Forms![EZQueryFrm]![FldNamesCombo] Dim MyControl As Control

Set MyControl = Forms![EZQueryFrm]![FldNamesCombo]

'Make sure the control's Row Source Type is Field List. MyControl.RowSourceType = "Field List"

'Change the Row Source table to Products table. MyControl.RowSource = " Products"

'Set combo box value to first item in drop-down list. MyControl.Value = MyControl.ItemData(O)

For example, the first line of the preceding code (Dim MyControl As Control) defines a new, empty Control object variable named MyControl. The second line

Set MyControl = Forms![EzQueryFrm]![FldNamesCombo]

makes the short name MyControl refer specifically to the control named FldNamesCombo on the form named EZQueryFrm. The last two lines are the same as the last two lines in this example except that they use the shorter name MyControl to refer to Forms![EzQueryFrm]![FldNamesCombo] (which makes the code a little easier to read).

The main point here though is that if you have a combo box or list box on a form, you can programmatically change the contents of the list (or drop-down list) to show the field names from any table or query in the database. Now turn your attention to the second type of list — one that gets its values from a Value List.

Listing text options

A combo box or list box can get its values from a simple string called a Value List. The string just contains each item in the list separated by semicolons. If the items in the list are all text, you should enclose each item in quotation marks.

For example, Figure 10-4 shows a combo box (named OpsCombo) added to a form. You can see the items in the open combo box: =, Like, <, >, and so forth. You can also see the properties for the control. Notice that the Row Source Type is Value List, and the Row Source is a bunch of little chunks of text enclosed in quotation marks and separated by semicolons. On the form, each little chunk of text is shown as an option on the control's drop-down list.

Figure 10-4:

Row Source properties for a Value List.

Figure 10-4:

Row Source properties for a Value List.

ü EzQueryFrm

x!

EZ Query

1

Property Sheet

-

Search which Field?

Comparison

Selection type: Combo Box

H

|v|

1 OpsCombo

M

=

Format fata Event Other All

Like

Control Source

i

<>

^Row Source

1 = "Like ^ "

Row Source Type

Value Lut v

Bound Column

1

<

Limit To List

ïeî

> =

Allow Value List Edits

No

List Items Edit Form

¡Inherit Value List No

Show Only Row Source Va lui

1 Record: H lofl H

I ft No Filter ||

a

Default Value Validation Rule

1

The Row Source for the OpsCombo control is

" = "

;"Like";

:"<>";

: ">";

: "<";

; "<="

which is why the drop-down list displays the various comparison operators. You can programmatically change the contents of a Value List combo or list by using the RowSource property. The new Row Source value must follow the rules of syntax, though, with each item separated by a semicolon and each string enclosed in quotation marks.

In code, you can represent a quotation mark as Chr(34) (the 34th ASCII character). That's generally easier than trying to add quotation marks by enclosing them in single quotation marks, like ' " ', which doesn't always work and is difficult to read. For example, Listing 10-1 declares a string variable named NewValList and then adds some text, quotation marks, and semicolons to that string.

Listing 10-1: Filling a Combo Box Value List Property

'Create a string variable named NewValList

Dim NewValList As String

'Build NewValList string in chunks.

NewValList = Chr(34) & "First Item" & Chr(34) & '

; "

NewValList = NewValList & Chr(34) & "Second Item"

' & Chr(34) & ";"

NewValList = NewValList & Chr(34) & "Third Item"

& Chr(34) & ";"

NewValList = NewValList & Chr(34) & "Fourth Item"

' & Chr(34)

'At this point, NewValList contains...

'"First Item",-"Second Item",-"Third Item",-"Fourth

Item"

'Make new string the Row Source for value list named OpsCombo

MelOpsCombo.RowSourceType = "Value List"

MelOpsCombo.RowSource = NewValList

'Set selection to first item in drop-down list.

Me.OpsCombo.Value = Me.OpsCombo.ItemData(Q)

When you create a list box or combo box with its Row Source Type set to Value List, you can leave the Row Source property empty. When the form first opens, the list is also empty, which means that the user can't select anything. However, you can write some code that fills the list and then attach it to the form's On Load event. This allows you to create dynamic, flexible lists that adapt themselves to the current database. We look at some examples in the sections that follow.

Making a list of table and query names

Sometimes you might want a combo box or list box to display a list of all the tables, or all the queries, or both. There isn't a simple property setting that lets you do that. You need to programmatically fill the list with names as soon as the form opens. Whenever you want code to execute as soon as a form opens, attach that code to the form's On Load event.

For example, Figure 10-5 shows an empty control named TblQryCombo. Its Row Source Type is set to Value List, but its Row Source property is empty. So without any code, when the form opens, TblQryCombo displays nothing.

Suppose now that when the form opens, you want it to display a list of all table names in the current database. You can write some code that loops through the AllTables collection and adds the name of each table to a string. Then use that string as the Row Source for the control.

Note this catch, though. The AllTables collection includes hidden system tables that Access uses behind the scenes. Because the names of those system tables normally don't appear in the Navigation pane, you should exclude them from the drop-down list as well.

Choose a Table or Query

¡Validation Rule a

Figure 10-5:

Sample empty combo box named

TblQry Combo.

¡Validation Rule id

Luckily, all the system tables have names that start with the letters MSys. To eliminate those table names from the drop-down list, you can use an If...Else...End If block to skip over any name that starts with MSys. The complete code to fill TblQryCombo with a list of table names as soon as the form opens is shown in Listing 10-2. Each comment refers to the line or lines that follow the comment.

Listing 10-2: Creating a Combo Box of Table Names

Private Sub Form_Load()

'Declare an empty string to store a value list. Dim TblNames As String TblNames = ""

'Loop through AllTables connection, add each table's name to TblNames 'variable, each enclosed in quotation marks and followed by a semicolon. Dim tbl As AccessObject For Each tbl In CurrentData.AllTables

'Exclude system tables, whose names all start with Msys. If Not Left(tbl.Name, 4) = "Msys" Then

TblNames = TblNames & Chr(34) & tbl.Name & Chr(34) & ";" End If Next tbl

'TblNames string now has all table names (except system tables). 'Make it the Row Source for the TblQryCombo control. MelTblQryCombo.RowSourceType = "Value List" MelTblQryCombo.RowSource = TblNames

'Show first item as selected item in control. MelTblQryCombo.Value = MelTblQryCombo.ItemData(O) 'Make sure user can only select a valid name. MelTblQryCombo.LimitToList = True

End Sub

If you want the combo box to show a list of all queries rather than all tables, you basically just have to change the word AllTables to AllQueries so that the loop gathers up names of queries rather than tables. Also, there are no system queries, so you wouldn't need the lf...Then...End If block to exclude names that begin with MSys.

Taking it a step further, suppose that you want the list to display the names of all tables and queries in the current database, with the word Table: in front of table names and the word Query: in front of query names. You need two loops in the form's On Load procedure: one to add the table names and one to add the query names. The entire procedure is shown in Listing 10-3.

Listing 10-3: Creating a Combo Box of Table and Query Names

Private Sub Form_Load()

'Declare an empty string to store a value list.

Dim TblNames As String

TblNames = ""

'To keep lines below short, we'll store the quotation mark

'as a variable named QM, and just refer to it by

'name (QM) in code that follows.

Dim QM As String

QM = Chr(34)

'Loop through AllTables connection, add each table's

'name to TblNames variable, each enclosed in quotation

'marks and followed by a semicolon.

Dim tbl As AccessObject

For Each tbl In CurrentData.AllTables

'Exclude MSys table names from list.

If Not Left(tbl.Name, 4) = "MSys" Then

TblNames = TblNames & QM & "Table: " & tbl.Name &

QM & ";"

End If

Next tbl

'Next we loop through the AllQueries collection and add

their names.

Dim qry As AccessObject

For Each qry In CurrentData.AllQueries

TblNames = TblNames & QM & "Query: " & qry.Name & QM

& ";"

Next qry

'TblNames string now has all table and query names.

'Make it the Row Source for the TblQryCombo control.

MelTblQryCombo.RowSourceType = "Value List"

MelTblQryCombo.RowSource = TblNames

'Show first item as selected item in control.

MelTblQryCombo.Value = MelTblQryCombo.ItemData(O)

'Make sure user can only select a valid name.

MelTblQryCombo.LimitToList = True

End Sub

Referring to the empty TblQryCombo control shown at the start of this section — and assuming that the code in Listing 10-3 is tied to that form's On Load event — by the time the form is visible to the user, the control will contain the names of all tables and queries in the current database, as in the example shown in Figure 10-6.

Figure 10-6:

TblQry Combo control after Form_ Load() procedure executes.

í-g] Combo Boxbf Tábles'antí"í3ífenÍ5 Choose a Table or Query

Table:.Sales Reports^^^^^^^^^S

V

Table.

Sates Report?

Tabte

Sbipp.&T

Table.

State

Table

Strings

Table.

Suppliers

Query: SI

Query

Custesners Extended

Query

DeleteOneResord

Query

Employees Extended

Query

: tSSBpg

Query

Inventory Needing Restocking

Query

Inventory On Hp|d

Making a list of form or report names

You can use a similar technique to Listing 10-2 to make a drop-down list display the names of all forms or all reports in the current database. For example, Figure 10-7 shows an empty ComboBox control named ObjCombo (for lack of a better name).

Figure 10-7:

Sample combo box named

ObjCombo.

Figure 10-7:

Sample combo box named

ObjCombo.

To fill the ObjCombo with a list of all form names in the current database, tie the form's On Load event to a procedure that creates a value list of form names, as we do in Listing 10-4.

Listing 10-4: Filling a Combo Box with Form Names

Private Sub Form_Load()

'Define string variable to store new Value List. Dim NewValList As String NewValList = ""

'Loop through collection and add each object name 'with quotation marks and semicolons to NewValList. Dim obj As AccessObject For Each obj In CurrentProject.AllForms

NewValList = NewValList & Chr(34) & obj.Name & Chr(34) & ";" Next obj

'Now NewValList contains all object names in proper format. 'Make that string the Row Source for objCombo control. MelObjCombo.RowSourceType = "Value List" MelObjCombo.RowSource = NewValList

'Set option to first item in list. MelObjCombo.Value = MelObjCombo.ItemData(O) End Sub

If you want that combo box to list all reports rather than all forms in the current database, change the code to loop through the AllReports collection rather than the AllForms collection. That just involves changing the collection name in the For Each...Next loop code block, as shown in boldface:

'Loop through collection and add each object name 'with quotation marks and semicolons to NewValList. Dim obj As AccessObject

For Each obj In CurrentProject.AllReports

NewValList = NewValList & Chr(34) & obj.Name & Chr(34) & ";" Next obj

The basic idea is still the same in all these examples. When the form opens, the form's On Load event occurs, which then triggers the code in the Form_Load() procedure, which in turn creates a valid, up-to-date list of object names to show in the list box or combo box.

Listing Table/Query field Values

The third type of combo box or list box that you can create gets its values from a field (or fields) in a table or query. The Row Source Type for such a list is Table/Query, and the Row Source is generally a SQL statement that specifies which fields and values to show in the list. Back up a moment and take a look at the bigger picture.

Suppose that you want to create a drop-down list that shows an alphabetized list of all unique company names from a table. By unique, we mean that if a given company name appears multiple times in the table, it still appears only once in the drop-down list (or list box). To create such a query in the query's Design view, you add the field name to the Query By Example (QBE) grid and also choose Ascending as the sort order. To prevent empty records from showing up in the query results, set a criterion to Is Not Null, as in the example shown in Figure 10-8.

Figure 10-8:

Sample unique values query for a text field.

Figure 10-8:

Sample unique values query for a text field.

To see the SQL view of a query on your own screen, as shown on the bottom of Figure 10-8, right-click the query's title bar and choose SQL View. For more information, see Chapter 7.

To ensure that only unique addresses appear, you then need to double-click the gray area at the top of the query to open the Query property sheet. In the Query property sheet, set the Unique Values property to Yes, as in the example shown in Figure 10-8.

We also managed to sneak the SQL view of the same query into Figure 10-8. Like any SQL statement, it describes in words what the query will do when it's opened. In this case, those words are tí

SELECT DISTINCT Customers.Company FROM Customers

WHERE (((Customers.Company) Is Not Null)) ORDER BY Customers.Company;

The SQL statement says the same thing that the items in the QBE grid say, which is "Select unique Company names from the Customers table, excluding blanks (nulls), and put them in alphabetical order."

The Unique Values property eliminates duplicate values within a single field. If a query contains multiple fields and you want only records with identical values in every field to be considered a duplicate, set the Unique Records property to Yes (or True). The SQL keyword for Unique Values is DISTINCT, and the SQL keyword for Unique Records is DISTINCTROW.

The SQL statement would work as the Row Source property for a ListBox or ComboBox control. In VBA, however, you'd probably prefer to use the following slightly different syntax, partly because you can omit all the parentheses and partly because the table name in front of the field name (for example, Customer.Company) is required only when the query involves two or more tables with identical field names. In the following syntax, tableName is the name of a table in the current database, and fieldName is the name of any field within that table:

SELECT DISTINCT [fieldName] FROM [tableName] WHERE [fieldName] Is Not Null ORDER BY [fieldName]

For example, Figure 10-9 shows a ComboBox control named SearchVal with its drop-down list already visible. That drop-down list contains an alphabetized list of company names from a table named Customers because the control's Row Source Type is set to Table/Query, and its Row Source property is set to the following SQL statement (shown as one lengthy line within the property):

SELECT DISTINCT [Company] FROM Customers WHERE [Company] Is Not Null ORDER BY [Company];

Now, suppose that you want to programmatically change the SearchVal combo box so that it shows all unique zip codes from the Customers table. This example assumes that the Customers table stores zip codes in a field named ZipCode. But the idea is to create a new SQL statement that refers to the ZipCode field rather than to the Company field, as follows. Then use that new SQL statement as the Row Source property for the SearchVal control:

'Create a string named MySql, and put a SQL statement in it.

Dim MySQL As String

MySQL = "SELECT DISTINCT [ZipCode] FROM [Customers]" MySQL = MySQL & " WHERE [ZipCode] Is Not Null" MySQL = MySQL & " ORDER BY [ZipCode]"

'Now MySQL contains a valid SQL statement. Use that SQL

'statement as the Row Source for the SearchVal control. MelSearchVal.RowSource = MySQL

'Make the first menu option the selected item in list. MeiSearchVal.Value = Me.SearchVal.ItemData(Q)

Figure 10-9:

Search Val is a ComboBox control.

Figure 10-9:

Search Val is a ComboBox control.

Even though the SQL statement is built in chunks in the code (just to make the lines short enough to fit inside these margins), the SQL statement that's created and stored in the MySQL variable is one long line of text composed of all the chunks. By the time the last MySQL = MySQL & ... statement has executed, the MySQL variable contains

SELECT DISTINCT [ZipCode] FROM [Customers] WHERE [ZipCode] Is Not Null ORDER BY [ZipCode]

In the procedure, the statement MelSearchVal.RowSource = MySQL puts the complete SQL statement into the Row Source property of the control. When the user clicks the drop-down button, the control shows all unique zip codes from the Customers table, as in Figure 10-10.

The bottom line here is that programmatically, you can do anything you want with a ListBox or ComboBox control. Like with anything you do through VBA, controlling when a combo box gets changed is a matter of choosing an appropriate event. Often the triggering event is a change to some other control on the form or even a different form. In this way, you can control what appears in a combo or list box based on the contents of some other control, which brings us to linking lists.

Figure 10-10:

Result of changing a combo box's Row Source property.

Figure 10-10:

Result of changing a combo box's Row Source property.

Was this article helpful?

0 0

Responses

  • rosa russo
    How to set combobox properties in vba?
    8 years ago

Post a comment