ACC2002: Combo Box Value Is Blank After You Set Recordset Property (287478)


The information in this article applies to:

  • Microsoft Access 2002
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 = "Microsoft.Jet.OLEDB.4.0"
   .Properties("Data Source").Value = "C:\Northwind.mdb"
   .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 = "Microsoft.Access.OLEDB.10.0"
   .Properties("Data Provider").Value = "Microsoft.Jet.OLEDB.4.0"
   .Properties("Data Source").Value = "C:\Northwind.mdb"
   .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.

MORE INFORMATION

Steps to Reproduce the Problem

  1. Open the sample database Northwind.mdb.
  2. Create a new form based on the Order Details table.
  3. 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"; 1"
   BoundColumn: 1
  4. Open the form in Datasheet view.

    Note that combo box shows data properly, both in the text box and in the list box.
  5. 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
  6. While still in the Visual Basic Editor, click References on the Tools menu, and then click Microsoft ActiveX Data Objects 2.6 Library.
  7. 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.

REFERENCES

For more information about the Open method, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type open method (ado recordset) in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MinorLast Reviewed:10/11/2006
Keywords:kbbug kbnofix KB287478
©2004 Microsoft Corporation. All rights reserved.