Article ID: 282358 - View products that this article applies to.
This article was previously published under Q282358
This article has been archived. It is offered "as is" and will no longer be updated.
Advanced: Requires expert coding, interoperability, and multiuser skills.
This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).
In Microsoft Access, after you set the Recordset property of a list box or combo box, the list is empty after you change between form views (such as Datasheet view, PivotTable view, or PivotChart view).
This behavior occurs because Access resets the control's Recordset property when you switch between different views of the form.
To work around this issue, use one of the following methods.
Method 1: Set the RowSource Property of the ControlInstead of setting the control's Recordset property, set its RowSource property in Design view of the form instead. The RowSource property allows you to use a table name, query name, or SQL statement to fill the list portion of the control. You can set the RowSource property of the control in Design view without having to write Microsoft Visual Basic for Applications (VBA) code.
Method 2: Use VBA Code to Fill the ListMicrosoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. It is possible to use the AddItem method to programmatically add items to the list portion of a combo box or list box control in an Access form. This technique allows you to open a recordset and enumerate through it to add values to the combo box or list box. To use the AddItem method to fill the control, follow these steps:
CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.
Steps To Reproduce the ProblemCAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.
REFERENCESFor additional information, click the article number below to view the article in the Microsoft Knowledge Base:
(https://support.microsoft.com/kb/287520/EN-US/ )ACC2002: Problems Binding List Box Recordset in Datasheet View