How to show recordset data in Ms access subform

Question

How can i show the result of one SQL query in an Access Sub form control? I tried the below code

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Dim qry As String

qry = "SELECT FirstName,Email FROM Customer" '
Set rs = db.OpenRecordset(qry)

Customer_enquiry_subform.Form.Recordset = rs

Where Customer_enquiry_subform is the name of my SubForm. I have only 2 columns in my Subform to show, FirstName,Email

It is throwing me an error

Run-time error 3251 : Operation Not supported for this type of object.

Can some one tell me what i am doing wrong ?

Answer 1

I know this question is 2 years old, but for those of us who stumble on it and do want to use a SQL query for an Access Subform control (Many SQL queries/stored procedures are just too complicated to put in the forms record source)...I have found ADO works well. See http://support.microsoft.com/kb/281998/EN-US/ for a good explanation. To get this to work on a subform I put the code on the subform's "on open" event - not the form's.

One note is that the subform will not be bound to the main form (parent/child is lost) so you would have to rely on other code to add new records I believe. My particular subform is read only so it doesn't need to be parent/child - I use the record ID from the main form as a parameter when I call the stored procedure to recreate the parent/child effect (see below code for my example).

Also, this code will not work if you do not specify LockType adLockPessimistic or adLockOptimisic: adLockReadOnly doesn't work (see In Memory, Stand-Alone, Disconnected ADO Recordset). You will get the error "The object is not a valid Recordset property" which can cause a little headache. :)

My example code (I use Juan Soto's universal ADO connection example from https://accessexperts.com/blog/2011/01/21/easy-adodb-recordsets-and-commands-in-access/):

   Private Sub Form_Open(Cancel As Integer)
   On Error GoTo ErrHandler
   Dim rs As ADODB.Recordset
   Dim strSQL As String
   Dim strPoint As String

   'I use my Point_ID from my main form as a parameter to fill the subform to recreate the "parent/child" effect
   strPoint = Forms!FRM_Vegetation_Strata!Point_ID
   strSQL = "sp_Report_VegWorksheet '" & strPoint & "'"

   'this method of calling the sub "OpenMyRecordset" is from Soto's example listed above
   OpenMyRecordset rs, strSQL, rrOpenStatic, rrLockOptimistic, True
   With rs
   If .RecordCount = 0 Then
   MsgBox "No records returned"
   End If
   End With

   Set Me.Recordset = rs


ExitProcedure:
    On Error Resume Next
    Exit Sub

ErrHandler:
    MsgBox Err.Description & " (" & Err.Number & ") encountered", vbOKOnly + vbCritical, "InitConnect"
    Resume ExitProcedure

    Resume
End Sub

To refresh the subform when the data on the main form changes:

Private Sub cmdRefresh_Click()
Me.Form.Recordset.Requery
Set Me.Form.Recordset = Me.Form.Recordset
End Sub

And when the form closes (on the subform "on close" event)

Private Sub Form_Unload(Cancel As Integer)
   'Close the ADO connection we opened
   On Error Resume Next
   Dim cn As ADODB.Connection
   Dim rs As ADODB.Recordset

   Set cn = Me.Recordset.ActiveConnection
   If cn.State = adStateOpen Then
    cn.Close
   End If

   Set cn = Nothing
   Set rs = Nothing
   Set Me.Recordset = Nothing

End Sub