Modifying the row source of a list box on a data access page in Access

Applies to
Microsoft Office Access 2003
Microsoft Access 2002

When you add a drop-down list box or list box to a data access page by using a wizard, Access sets the ListRowSource property of the drop-down list box or list box to the table or query you selected as the control's data source. A ListRowSource is the name of a recordset in the data source that supplies the values and text for the control. When the data access page is created and saved, the SQL statement behind the ListRowSource is saved with the page (in an .htm file) as static text. While you can point the control to another table or query the data source by using the control's property sheet in Access, there is no user interface to modify the saved SQL statement.

For example, if you update the connection information for the Employees or Review Orders data access pages in the Northwind Traders sample database (.mdb) to point to an Access project (.adp) instead, the row source for the ReportsTo drop-down list box on the Employees page and the Employee drop-down list box on the Review Orders page will be truncated and generate an error. This occurs because the row source uses a query which contains ampersands (&) to concatenate sections of the expression. Ampersands are acceptable concatenation operators in .mdb files. However, .adp files use T-SQL which requires plus signs (+) for concatenation. No user interface exists to make this change directly.

That means if you need to change or modify the row source of the drop-down list box or list box, you must either remove the existing control and add a new control with a revised row source to the page (the recommended method), or edit the SQL statement directly in the .htm file, either in a text processor like Notepad or by using the Microsoft Script Editor.

Of the two ways of changing or modifying the row source, the recommended method is to remove the existing control and add a new control with a revised row source to the page. The other method involves navigating through the raw text string of the Microsoft Office Data Source Control (MSODSC), which contains the drop-down list box or list box, and replacing the SQL text. Because the manual editing of the SQL statement is prone to errors, it is not supported and won't be covered in this article.

Modifying the row source by replacing the control

To illustrate this method, we'll modify the row source of the ReportsTo drop-down list box on the Employees data access page in the Northwind Traders sample database, and replace the ANSI SQL-89 compliant ampersand (&) with the ANSI SQL-92 compliant plus sign (+).

  1. Open the Northwind Traders sample database.
  2. In the Database window, click Pages under Objects, and open the Employees page in Design view.
  3. Select the ReportsTo drop-down list box control and press DELETE.
  4. Minimize the Employees page.
  5. Click Tables under Objects, and open the Employees table in Design view.
  6. Scroll down to and select the ReportsTo field.
  7. Click the Lookup tab under Field Properties and click the Build button next to the RowSource property to open the Query Builder.
  8. In the Query Builder, click SQL View on the View menu.
  9. In the SQL window, replace the ampersands (&) with plus (+) signs. Close and save the changes to the query. Close and save the changes to the table.
  10. Restore (maximize) the Employees page. If the field list isn't displayed, click Field List on the View menu.
  11. From the field list, drag the ReportsTo field to the page. Close and save the page.

The row source of the ReportsTo drop-down list box on the Employees page now contains the revised SQL statement. You can verify this by clicking the drop-down list box and noticing that it contains data.

ShowExample of the SQL statement

The row source SQL statement before it was modified:


SELECT Employees.EmployeeID, [LastName]&", "&[FirstName] AS Name
FROM Employees
ORDER BY Employees.LastName, Employees.FirstName;

The row source SQL statement after it was modified:

SELECT Employees.EmployeeID, [LastName]+", "+[FirstName] AS Name
FROM Employees
ORDER BY Employees.LastName, Employees.FirstName;

 
 
Applies to:
Access 2003