Microsoft KB Archive/287478

= ACC2002: Combo Box Value Is Blank After You Set Recordset Property =

Article ID: 287478

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q287478



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).



SYMPTOMS
In Microsoft Access, after you set the Recordset property, the text box portion of a combo box may unexpectedly remain blank when you select items from that combo box.



CAUSE
This behavior can occur when the ActiveConnection property of the Recordset uses an ActiveX Data Objects (ADO) connection that was opened by means of the Jet OLEDB provider.

This behavior can also occur when you open a new ADO connection that uses the Jet OLEDB provider, and you assign the ActiveConnection property to the connection by using the following method: Dim cn As ADODB.Connection Set cn = New ADODB.Connection With cn  .Provider = &quot;Microsoft.Jet.OLEDB.4.0&quot; .Properties(&quot;Data Source&quot;).Value = &quot;C:\Northwind.mdb&quot; .Open End With



RESOLUTION
To correctly display your selection in the text box portion of the combo box, set the ActiveConnection parameter of the Open method of a Recordset object to the following:

CurrentProject.AccessConnection

In a situation where you have to open your own ADO connection, base your Microsoft Visual Basic code on the following sample code: Dim cn As ADODB.Connection Set cn = New ADODB.Connection With cn  .Provider = &quot;Microsoft.Access.OLEDB.10.0&quot; .Properties(&quot;Data Provider&quot;).Value = &quot;Microsoft.Jet.OLEDB.4.0&quot; .Properties(&quot;Data Source&quot;).Value = &quot;C:\Northwind.mdb&quot; .Open End With



STATUS
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 Problem
 Open the sample database Northwind.mdb. Create a new form based on the Order Details table.  Add a combo box to the form with the following properties:   Name: ProductID ControlSource: ProductID RowSource: SELECT [ProductID], [ProductName] FROM Products ORDER BY [ProductName]; ColumnCount: 2 ColumnWidths: 0&quot;; 1&quot; BoundColumn: 1  Open the form in Datasheet view.

Note that combo box shows data properly, both in the text box and in the list box.</li>  Open the form in Design view and add the following code to the OnLoad event: Private Sub Form_Load Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset rs.Open Me.ProductID.RowSource, CurrentProject.Connection, adOpenKeyset Set Me.ProductID.Recordset = rs End Sub </li> While still in the Visual Basic Editor, click References on the Tools menu, and then click Microsoft ActiveX Data Objects 2.6 Library.</li> Open the form in Datasheet view. Notice that the list is filled correctly, but the text box portion of the combo box is blank even though it is a bound control.

Also note that if you inspect the control's value property from the Immediate window, it is set correctly.</li></ol>

<div class="references_section">