Copying refences to a control's recordset

[Dirk]

Hi,

I am tryign to get a reference to a listbox that I assigned a query to:

It's initial rowsource is a query that returns 0 rows.
The type of the rowsource is table/query.

I have the following recordset declared:

Dim rsRules as ADODB.Recordset

When I try to assign my listbox's recordset to this variable using:

Set rsRules = lstRules.Recordset

I get an error 13 (type mismatch). My listbox's recordset is of type
Object/Recordset so I don't understand why I am getting this error. Should I
declare my variable as some other type of recordset possibly?

 

[Brendan Reynolds]

In an MDB, the Recordset property will return a DAO.Recordset, unless you
have previously assigned an ADODB.Recordset to the property. Add a reference
to the Microsoft DAO 3.6 Object Library if you don't already have one, and
change your reference to Dim rsRules As DAO.Recordset

 

[Dirk]

Ok, declaring the variable as an DAO.Recordset worked to get a reference to
listbox's recordset. Thanks you for the info.

However, since I prefer to use ADO, I tried to assign an ADO recordset to
the listbox control in the form's Open eventhandler:

dbConn.Open ("myconnection;")
Dim strSQL As String
strSQL = "myquery;"
Set rsRules = dbConn.Execute(strSQL)
Set lstRules.Recordset = rsRules

This causes the following error on the last line:

Error 7965: The assigned object is not a valid Recordset property.

Is there some property I must set on the control for it to accept ADO
recordsets?

 

[Brendan Reynolds]

You need to set the CursorLocation property of the recordset to adUseClient
instead of the default adUseServer. I'm not sure whether using the Execute
method of the Connection object will work, but this works ... at least, it
works in tests I have done in response to this and similar newsgroup
questions. It is not something I have ever used in a 'real world' app, as I
would use DAO instead. With that caveat, here's the test code ...

Option Compare Database
Option Explicit

Private mrstEmployees As ADODB.Recordset

Private Sub Form_Open(Cancel As Integer)

Set mrstEmployees = New ADODB.Recordset
With mrstEmployees
Set .ActiveConnection = CurrentProject.Connection
.CursorLocation = adUseClient
.Source = "SELECT LastName FROM Employees ORDER BY LastName"
.Open
End With

Set Me.Combo0.Recordset = mrstEmployees

End Sub

Private Sub Form_Unload(Cancel As Integer)

If Not mrstEmployees Is Nothing Then
If mrstEmployees.State <> adStateClosed Then
mrstEmployees.Close
End If
Set mrstEmployees = Nothing
End If

End Sub