Displaying a recordset on a form in Access 2010 using VBA

Question

I'm developing a data retrieval application in Access 2010 in which the user chooses which table, columns, and rows to view by selecting listbox entries. The VBA code generates a SQL statement from these choices and then creates an ADBDB.Recordset object from this.

How can I display the recordset records in Access? None of the grid controls work in Access 2010 and the subform just isn't designed for this purpose. Can someone recommend another strategy?

Solution

You could save the SELECT statement as a named query, then open the query as a datasheet. It's not really a form, but somewhat form-like.

Call DatasheetFromSql(strSql)

Public Sub DatasheetFromSql(ByVal pSql As String)
    Const cstrQuery As String = "qryDiscardMe"
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strMsg As String

On Error GoTo ErrorHandler

    Set db = CurrentDb
    db.QueryDefs.Delete cstrQuery
    Set qdf = db.CreateQueryDef(cstrQuery, pSql)
    DoCmd.OpenQuery cstrQuery, , acReadOnly

ExitHere:
    On Error GoTo 0
    Set qdf = Nothing
    Set db = Nothing
    Exit Sub

ErrorHandler:
    Select Case Err.Number
    Case 3265 ' Item not found in this collection. '
        Resume Next
    Case Else
        strMsg = "Error " & Err.Number & " (" & Err.description _
            & ") in procedure DatasheetFromSql"
        MsgBox strMsg
        GoTo ExitHere
    End Select
End Sub

I opened the query read-only. If you want to allow users to edit the data returned by their custom queries, I would not recommend this approach. Instead I would invest the effort in the approach HK1 offered because it can support better control of the user data changes.

With the query opened as a datasheet, you can use Screen.ActiveDatasheet to inspect its properties. At least some of the methods are also available to you. For example you could resize/reposition the datasheet like this:

Screen.ActiveDatasheet.Move Left:=0, Top:=0, Width:=(4 * 1440), Height:=(3 * 1440)

The units are twips (1440 twips / inch), so that would make the width 4 in., height 3 in., and move it to the upper left corner of the Access window.