Unable to assign Ado recordset to form

[feets]

I think a bit of background would be useful first, before discussing the coding issue.

I have an Adp connecting to a Sql 2000 Server. The purpose of the front end is to record visits to premises.

My users have laptops so therefore will not always have a connection to the Sql backend. Therefore when a visit record is inserted, it is also inserted in a table on a local mdb file held in their my Documents folder. The purpose is to view previous visits whilst offline.

My problem is that I can program and open a connection to the local mdb. I can run a select query which is assigned to a recordset object. What I can't do is assign a form's recordset property to the recordset I have created.

I get Run time error '91' Object variable or with block not set. Something I don't really understand, so I would appreciate any attempt to help me comprehend.

Below is the code I'm using when the form loads

Private Sub Form_Load()

Dim cmd As ADODB.Command
Dim rsVisit As ADODB.Recordset
Dim frm As Form_sbFrm_ViewCR
Dim objVisit As Object

Dim strcnn As String
Dim strMdbPath As String
Dim strDbName As String

' Calls Function to Find Path of MyDocumenrs

strMdbPath = SpecFolder(CSIDL_Personal)
strDbName = "\wsrsVisits.mdb"

strMdbPath = strMdbPath + strDbName

strcnn = "Provider=Microsoft.jet.oledb.4.0;"
strcnn = strcnn & "Data Source= " & strMdbPath & ";"
strcnn = strcnn & "Persist Security Info=False"

Set cmd = New ADODB.Command
cmd.ActiveConnection = strcnn
cmd.CommandType = adCmdText
cmd.CommandText = "Select VisitID,DateOfVisit,Method,Reason From tblCrVisit"


Set rsVisit = cmd.Execute

Me.Recordset = rsVisit

[boblarson]

You have this:
Dim rsVisit As ADODB.Recordset

But you never instantiate it (and ADO doesn't like that):

Set rsVisit = New ADODB.Recordset

you need to do that first before the

Set rsVisit = cmd.Execute (and I'm not sure about this one anyway as you need to "open" the recordset not set it to the cmd.Excecute. rsVisit.Open ...etc.

[feets]

Originally Posted by boblarson

You have this:
Dim rsVisit As ADODB.Recordset

But you never instantiate it (and ADO doesn't like that):

Set rsVisit = New ADODB.Recordset

you need to do that first before the

Set rsVisit = cmd.Execute (and I'm not sure about this one anyway as you need to "open" the recordset not set it to the cmd.Excecute. rsVisit.Open ...etc.

Thanks for your advice so far. As suggested by you and others I created a new instance and then opened the recordset assigning it to the form's recordset.

This gives me all the records from the local table, what I want to do now is from the main form select criteria, which after updating refreshes the recordset showing the results.

I had tried to create a query event on the subform which would run the select query. However when I update the combo box I could not get it run the event. form_sbfrmCR_Visit.query???

What would be the best way to achieve this?

[boblarson]

If you create a SQL string and assign it to, say strSQL and then use

rst.Open strSQL, strcnn, adOpenDynamic, adLockOptimistic

and then you can set the subform's recordsource to rst