Listing Table Query field Values

The third type of combo box/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 you want to create a drop-down list that shows an alphabetized list of all unique Company names from a table. By unique, I mean that if a given company name appears more than once in the table, it still appears only once in the drop-down menu (or list box). To create such a query in Query Design, you'd need to 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.

To see the SQL view of a query on your own screen, right-click the query's title bar and choose SQL View. For more information, see the first few pages of Chapter 7.

Figure 10-8:

Sample unique values query for a text field.

j^l UMqueCampdniesLookupQry : ... J

CustID

FirstName LastName Company Addressl

Field Table Sorb Show Criteria

Ascending

Query Properties

General |_

Description

Default View ¡Datasheet

Output All Fields [No

Top Values Ajl

Unique Values .

Unique Records Run Permissions Source Database , . . Source Connect Str . .

Record Locks

Recordset Type

ODBC Timeout

Filter

Order By

Max Records

Orientation

Subdatasheet Name .

Linkf Link m

■ Unique Values property

^ UniqueCompaniesLQokupQiy :...

SELECT DISTINCT Customers.Company FROM Customers

WHERE (((Customers.Company) Is Not Null))

ORDER BV Customers.Company; _

SQL view

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 Properties sheet. In the Query Properties sheet, set the Unique Values property to True, as in the example shown in Figure 10-8.

I 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 is to do when opened. In this case, those words are

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, tblName is the name of a table in the current database, and fldName is the name of any field within that table:

SELECT DISTINCT [fldName] FROM [tblName] WHERE [fldName] Is Not Null ORDER BY [fldName]

For example, Figure 10-9 shows a ComboBox control named SearchVal with its drop-down menu already visible. That drop-down menu 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 SQL statement below (shown as one lengthy line within the property):

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

Figure 10-9:

SearchVal is a

ComboBox control.

SearchVal control

¿1 EzQueryFrm : Form

B1

EZ Query

' 'f Sçlfl ëQiripgFÎasfffi- Vate

¡Company * *

hi

ABC Ridduettohs Arbor Classics (frffltfe Designs Gadgets.lnc, Visionary Systems, [nc

Wiley Widgets

Figure 10-9:

SearchVal is a

ComboBox control.

J* Combo Box: SearchVal

Format || Data Event || Other | All

Name

SearchVal

0

Control Source

Format

Decimal Places

Auto

Input Mask

Row Source Type

Table/Query

Row Source

SELECT

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

Column Count

1

Column Heads

No

Column Widths

Bound Column

1

List Rows

8

List Width

Auto

Status Bar Text

Limit To List

No

Auto Expand

Yes

Default Value

Row Source property

Now, suppose 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 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"

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

'statement as the Row Source for the SearchVal control.

Me!SearchVal.RowSource = MySQL

'Make the first menu option the selected item in list.

Me!SearchVal.Value = Me.SearchVal.ItemData(O)

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 Me!Search.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 will be 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 me 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.

..new Row Source property setting
0 0

Post a comment