ACC: DDE Statements Invalid in List Box or Combo Box Control

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SYMPTOMS

Advanced: Requires expert coding, interoperability, and multiuser skills.

If you use a dynamic data exchange (DDE) function as the ControlSource property of a list box or combo box, the box will be empty when you view it in Form view (you will receive a "#Error" message in version 1.x). However, if you view the form in Datasheet view, the first object in the DDE data will appear.

Using a DDE function in the RowSource property causes an error message in both Form and Datasheet views of the form.

RESOLUTION

Using code, you can create a list function that returns to a list box or combo box. You can use any code routine to obtain the information used in the list, including DDE commands.

STATUS

This behavior is by design.

MORE INFORMATION

The ControlSource property of a list box or combo box may be blank if the control is unbound, or may contain the name of the field that the control is bound to; it may not contain a function.

The RowSource property of a list box or combo box may contain a list of values, the name of a table or query, or an SQL string. This property provides the list of values the user may select from.

Steps to Reproduce Behavior

1. Open a Microsoft Excel spreadsheet and enter a text string in cell A1. Save the spreadsheet as Test.xls.

2. Create a new form in Microsoft Access and add a list box with the following property to the form:

         Object: List Box
         ----------------------------------------------
         ControlSource: =DDE("Excel","Test.xls","R1C1")
   

3. Make sure the Test.xls spreadsheet in Microsoft Excel is open.

4. View the form you created in step 2 in Form view. Note that in Microsoft Access 2.0, 7.0, and 97 the list box is empty. In Microsoft Access version 1.x, the list box contains "#Error."

5. View the form in Datasheet view. Note that the data from Microsoft Excel appears in the list.

REFERENCES

For more information about using the ControlSource property of a form or report object, search the Help Index for "ControlSource," and view the available topics, or ask the Microsoft Access 97 Office Assistant.