Access Cookbook

Fill the Drop-Down Lists When Using ServerFilterByForm in an ADP

Problem

You have turned on the ServerFilterByForm property. However, when users open the form and select from the combo boxes, the only choices are “Is Null” and “Is Not Null”. How do you get the combo boxes to show a list of valid values for that field?

Solution

If you turn on the ServerFilterByForm property, your form will open in a special view that turns text boxes into combo boxes. This allows users to define their own server filters at runtime, which are then processed by SQL Server before the record source data is returned to the form. However, you’ll often see only the values shown in Figure 14-8 when you expand one of the combo boxes.

Figure 14-8. Combo boxes with only Is Null and Is Not Null options

Each text box on the form has a FilterLookup property that has three settings:

Never

A combo box list will contain only two items: Is Null and Is Not Null.

Always

A combo box with a full list of values will be created for that text box.

Database Default

Access will populate the list either with all the values or with only Is Null/Is Not Null, depending on the settings in the Edit/Find tab of the Tools → Options dialog shown in Figure 14-9.

Figure 14-9. Database options that affect ServerFilterByForm

Follow these steps ...