Hello,
I have a problem that's really bugging me.
I'm trying to populate a form in an Access Project using an ADO recordset.
The form is set to Continuous forms.
The following code is run after a user selects an item from a combo box. There can be as may as 30 odd records being brought back.
The problem is when populating the form all the records in the form are populated with the last recordsets data. How do I get each recordset to populate each record on the form?
(The solution to set the recocrdsource in access itself will not suffice,as I need to be able to type in data without using a recordset as well)
The code I'm using is as follows;

Private Sub cboSchedule_AfterUpdate()

'This procedure transfers the reocrdset to the form based on the
'project/schedule chosen by the user

'declare connections and recordsets
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

Dim strSQL As String

'SQL string
strSQL = _
"SELECT * FROM luminaire_schedule_items WHERE doc_number ='" & Me.cboSchedule & "'"

On Error GoTo HandleErr

If ConnectTolager(cnn) Then

      rst.Open _
      Source:=strSQL, _
      ActiveConnection:=cnn, _
      CursorType:=adOpenKeyset, _
      LockType:=adLockBatchOptimistic, _
      options:=adCmdText

Set rst.ActiveConnection = Nothing
CloseAndReleaseConnection cnn

'If no records then quit
If rst.EOF Then
MsgBox "No records found"
GoTo ExitHere
End If

'transfer recordset to form
Set Me.Form.Recordset = rst
While Not rst.EOF
With rst
Me.txtItemType = !ITEM_TYPE
Me.txtBigNotes = !BIG_NOTES
Me.txtLocationUse = !LOCATION_USE
rst.MoveNext
End With
Wend

ExitHere:
'close connection
  CloseAndReleaseConnection cnn
  Set rst = Nothing
  Exit Sub

HandleErr:
  MsgBox Err & ": " & Err.DESCRIPTION, , _
    "Error"
  Resume ExitHere
  Resume

End If
End Sub

Unfortunately you can't. There isn't any way to assign unbound control values from a Recordset object if the form is displayed in continuous or datasheet view.

If you were using a .mdb file, you could cache the recordset in a temp table, then bind to the temp table. Since you're using a .adp file, though, you don't have the local storage option. What I have seen done ("Microsoft Access Developer's Guide to SQl Server") is the use of a local XML file to cache local data, with code like the following:

'Create a client-side recordset.
  Set rst = New ADODB.Recordset
  rst.CursorLocation = adUseClient
  rst.CursorType = adOpenStatic
  rst.LockType = adLockReadOnly
  'Find or create the XML file,
  ' and open the recordset.
  If Len(Dir(strFile)) > 0 Then
      'File was found. Open recordset from the file.
      rst.Open Source:=strFile, Options:=adCmdFile
  Else
      'File wasn't found.
      'Open recordset using the Row Source,
      ' and create the XML file.
      rst.ActiveConnection = _
        Forms!frmLogin.OLEDBConnect
      rst.Open Source:=ctl.RowSource
      rst.Save strFile, adPersistXML
      Set rst.ActiveConnection = Nothing
  End If

The source code for the book I cited above is available from the Sam's Publishing web site if you want to look into this technique a little further. It works well, but isn't exactly trivial to implement.

HTH,

Bob

If you decide to download the code, you're interested in the chapter 11 folder. It contains two unbound Access front-ends to the SQL Server version of Northwind. One app is a .mdb which executes stored procedures using pass-through queries and caches the data in local Jet tables; the other is an .adp which caches local data in XML files on your file system in the directory where the app is sotred. There is also a script with the download that you need to run to create the stored procedures. There are quite a few. Both apps are reasonably complete: login screens, search functionality, ability to fetch a limited number of records, all your standard DML functions, input validation, etc...

I have been working the same issue and here is the solution I came up with. My code is almost the same as yours with the following exceptions:

Remove the following loop code:
While Not rst.EOF
With rst
Me.txtItemType = !ITEM_TYPE
Me.txtBigNotes = !BIG_NOTES
Me.txtLocationUse = !LOCATION_USE
rst.MoveNext
End With
Wend

Then on your form bind the field text box controls (not the form) by putting each table field name in the field text box control source property.
Leave the form recordsource unbound.

This populated my continuous form correctly. Of course since it is an ADO recordset assigned to a form it is not updateable in the form. In my form the only field that needs to updated is a supervisor's approval checkbox. I put a command button that executes a parameterized update query based on the primary key field in the record that has the focus in the continous form.
Let me know if this works for you.
Von Kettler

This reply is somewhat belated, but I have only just read this series of posts after being confronted with the same problem that Bobby has had.

My question is directed to Von Kettler. Could you provide more detail concerning how you managed to get an ADO recordset to populate a continuous form? I am particularly interested in the form and control work/syntax that you used to solve your problem.

Cheers,

sfx

Hi sfx,

Since this appears to be Von’s only post to this forum ever (gem that it is), mind if I share my two cents? I was under the impression that this couldn’t be done, so I found it quite interesting. Of course, it turns out to be quite simple.

I created a table (in a .mdb) with two fields, DataID and DataItem, and added a few records. Then created a form and placed two text box controls (Text1 and Text2) on it. I set the form’s Default View property to Continuous Form, and left all other form properties alone. Then I typed DataID in Textbox1’s Control Source property, and DataItem in Textbox2’s Control Source property, leaving all other textbox properties alone. Then I placed the following in the form’s Load Event:

Private Sub Form_Load()
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset

    Set cnn = CurrentProject.Connection
    Set rst = New ADODB.Recordset

    Dim strSQL As String
    strSQL = "SELECT * FROM tblData"

    rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText

    Set Me.Form.Recordset = rst

End Sub

The continuos form populates as advertised.

The only observation I might add is that this only works with a Keyset or Static cursor, which suggests that the recordset needs to be able to report exactly how many records it contains. Dynamic or ForwardOnly cursors return a run-time error.

Thanks for revisiting this.

Bob

Hi Bob,

No, thank you for revisiting this. :)

Your information has proven most helpful. It has been some time since you last broached this subject, and no doubt your skills have greatly improved since then. I am but a nascent Access developer, so thank you for responding so swiftly to my post and sharing your insight with me.

Cheers,

sfx

The code provide by Bob Bedell in his 12/18/2005 posting is very interesting. But what do I do if besides the actual field values I also need to include some action buttons per each record in the recordset?
Also, suppose the user clicks on the button that corresponds to a particular record shown. How do I refer to that record in the VBA behind the button, in a continuous form?

Thank you,
Vlad

Hi Vlad

The answer is simple - just treat the continuous form as if it were a normal form. For example, if the user clicks a command button on a particular record in the continous form, then Access will treat all code as if the form displayed just that one record. So references to controls such as Me!txtValue will give you the current value for that record.

As for the per record actions, just add the command buttons to the continuous form - as I said above, each command button will run in the contect of the record it is displayed against.

Hope that helps!

Richard