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

How about this:

Private Sub Form_Load()

Dim cmd As ADODB.Command
Dim cn As ADODB.Connection
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
Dim sSQL 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 cn = New ADODB.Connection
With cn
    .CursorLocation = adUseClient
End With
cn.Open strcnn


sSQL = "Select VisitID,DateOfVisit,Method,Reason FROM tblCrVisit"
Set rsVisit = New ADODB.Recordset
rsVisit.Open sSQL, cn, adOpenDynamic, adLockOptimistic

This will pull in the data from the access database in My Docs. Now, what do you want to do with it?

I normally just pull the data to a local table and then open the form on the local table.

Did any of that help?




mmcdonal

Thanks for your help. I've assigned the rsVisit to the form's recordset.

The form controls then needed renaming to the name of the fields in the recordset, for the form to be populated.

I have one question regarding your advice, what is the purpose of cn.CursorLocation, the options being either client or server.

My intention is for this to be the initial display when you first load the form.

The form is actually a subform. The main form has combo boxes which I will then use as query parameters (e.g Date from, Date To). After the combobox has been updated I then need to requery the subform with the new parameters.

I have this working successfully on a local mdb where the form, query and table are all contained in the same database, I now need to pull the query result from the local mdb --> adp form.

Basically, while a little more costly than the default adUseServer, it allows more actions with the recordset.

In your case, I would open a second local recordset, and then append all the data from the remote table to a local table. Then build the subform off the local table, or a query on the local table.

Then if you want to push this data to the SQL Server, just package all the records you want and open a local and remote recordset and push them to the server.

Using a local table is a little more kludgy. I use it to keep traffic down on the wire, but since this is all local data, then that is not an issue.

Just call the data again from the My Docs database with your added parameters.

Did that help?


mmcdonal

Thanks for getting back to me,

Where I'm up to now....I created a query event on the subform which would run the select query with the search criteria. 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? To be honest you are on different level to me, and you've left me a little confused about how to proceed.

Put the code in a module, and then call the module from the sub form event and from the main form event you are using.

Depending on the amount of data, I would transfer this to a local table and run from there just to keep the transactions off the wire... I keep forgetting this is a local database.



mmcdonal