Access subform isn't displaying ado recordset

Question

I'm trying to display an ADO recordset in an access07 subform in datasheet view. Just a snapshot, read-only. The connection is successful, the recordset contains data, however, when I set the subform recordset no fields are displayed. The subform shows a vertical scrollbar, and when scrolling to the bottom it indicates the correct number of records, but it shows nothing, no fields, no columns.

This has to be something very simple that I'm missing. Any ideas?

EDIT: Here's a summary of the code that establishes the recordset.

Set cn = New ADODB.Connection
Set rsADO = New ADODB.Recordset

dbBackend = "C:\Users\Me\Desktop\TEST_Db_Backend.accdb"
sqlStr = "SELECT * FROM tblMaster;"
    strConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbBackend & ";User Id=;Password=;"

    cn.CursorLocation = adUseClient
    cn.ConnectionString = strConnect
    cn.Open
    If cn.State = adStateOpen Then
        rsADO.Open sqlStr, cn, adOpenStatic, adLockReadOnly
End If

    Set Me.PreviewPane.Form.Recordset = rsADO

rsADO.Close
cn.Close
Set rsADO = Nothing
Set cn = Nothing

I updated my original post with the recordset code. So your initial thought is the recordset as the cause? — whistler
Is this code in the Private Sub Form_Load() event procedure? If not, try moving it there and changing Set Me.PreviewPane.Form.Recordset = rsADO to Set Me.Recordset = rsADO. I used the answer here: stackoverflow.com/questions/5641760/… — bf2020
Thank you for the suggestion. No, with the recordset being created in the form load event I'm still receiving the same result. — whistler
UGH! This just reminded me of years ago we had a similar problem, and although we never found the cause, we did find a workaround. Unfortunately the fog of time masks the exact solution, but I think we did one of the following: (a) ...subform.Refresh or Repaint or Requery; (b) subform.recordset MoveLast then MoveFirst; Wish I could remember.... :( — Wayne G. Dunn
Thanks Wayne. I'm still fiddling, will post whatever solution I can scrounge up. — whistler

whistler whistler · Accepted Answer · 2014-02-11T22:01:08

After toiling with this for hours, I have an acceptable solution although not what I wanted originally. Here's my answer, the best I can tell...

An ADO recordset cannot be bound directly to a form (or subform) in datasheet view without the form having controls to bind the fields in the recordset to. I've seen resources with contrary opinions, but with what I'm doing I just can't make it work.

To resolve this, I added textboxes (with labels) to my subform's source object form in design view. The number of textboxes added is the maximum number of recordset fields that I'm willing to display in the subform, since my recordset will have a variable number of fields and I need to assign these textboxes to them dynamically.

I created the recordset using the code in the original post, then looped through the form's controls to assign them to the recordset. Here's the loop:

Const prevMax As Long = 25
Dim r As Long, rMax As Long
Dim ctrl As Object

    rMax = rsADO.Fields.Count - 1
    If rMax > prevMax Then rMax = prevMax
    r = 0
    For Each ctrl In Me.PreviewPane.Form.Controls
        If ctrl.ControlType = acTextBox Then
            If r <= rMax Then
                ctrl.ControlSource = rsADO.Fields(r).Name
                ctrl.Controls(0).Caption = rsADO.Fields(r).Name
                r = r + 1
            Else
                Me.PreviewPane.Form.Controls(ctrl.Name).ColumnHidden = True
            End If
        End If
    Next ctrl

It changes the ControlSource of each textbox to a corresponding recordset field, changes the label caption to match the recordset fieldname (which adds the column names I want in the subform), and hides the remaining textbox columns that would appear in datasheet view. As long as the default view of the source object form is set to datasheet, it should display the recordset (within the maximum) in the subform.

Access subform isn't displaying ado recordset

1 Answers