Displaying a recordset on a form in Access 2010 us

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?

Answer 1

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.

Answer 2

I don't use ADO but simply VBA, in which case above approach is wrong: one doesn't need any means for displaying an existing recordset in a form, but should on the contrary define an adequate recordset within the form !

Instead of creating MyDataBase.OpenRecordset ("SELECT … [SQL query] ;"), just set the form's RecordSource to the very same query definition:

Forms![MyDisplayForm].RecordSource = "SELECT … [SQL query] ;" Forms![MyDisplayForm].Requery

For me this works perfectly (Windows 7 pro ×64 / MS Office pro ×64)

Answer 3

Here's what I think you'll have to do to get this kind of functionality.

You'll first need to create enough of the correct controls on a form to handle every possible scenario. You'll then need to set the form to be a datasheet form so that it appears as a grid.

Now set the controlsource on your controls to correspond with one of the fields in the recordset. On every control that is not in use needs to have it's ColumnHidden property set to true. You'll also have to change the caption of the associated label to show the appropriate column name for each control that will be visible.

Now, bind that form to your ADO recordset object.

Me.Recordset = rst
'or
Me.Subform1.Form.Recordset = rst

Is this a perfect solution? Most certainly not. Access doesn't have anything that compares to the DataGridView in .Net or even to the Grid controls that are used in VB6. In my opinion, you're really pushing the limits of Access to try to get this sort of functionality. It's like swimming upstream. You'll find that everything you do is going to be fairly difficult and some things just aren't going to be possible.

Answer 4

for an ADP Project where you can't have local MS Access query definitions, you can create a datasheet form with multiple textboxs named txt1, txt2,.... txt30 and labels name lbl1 ... lb30 and this code will set the form.recordsource and set the textbox.controlsource and the label.caption to the appropriate fields from an ADO recordset object. This form will allow you to view your ADO Recordset similar to the Docmd.OpenQuery method.

You must pass the ADO recordset's SQL statement to the form using the OpenArgs property of the form. the code below shows the VBA code to call\open the form (which shows the ADO Recordset like a query) and pass your sql string. The vba code on the form's load event will set all the control properties, resize the columns that have data and hide the columns that do not have a corresponding field from the ado recordset:

'stevekirchner 09/29/2012  Replace Access parameterized query with SQL Server in-line function 
'DoCmd.OpenQuery "qry_SearchMaster_CaseTitles", , acReadOnly

 strsql = "Select * from dbo.UDF__qry_SearchMaster_CaseTitles ('%" & Me.tbxSearchTerm.Value & "%') "

Call Display_ADO_Recordset_from_Datasheet_Form(strsql, "frm_Display_ADO_Recordset_Result1")

'create a non-form module and put the code for the sub Display_ADO_Recordset_from_Datasheet_Form 
'and function fIsLoaded in it (this will allow you make several forms to view ADO recordset and 
'call the code from one place\module):

Sub Display_ADO_Recordset_from_Datasheet_Form(sSQL As String, sFormName As String)

On Error GoTo Error_Handler

    If fIsLoaded(sFormName) Then

        DoCmd.Close acForm, sFormName

    End If

    DoCmd.OpenForm sFormName, acFormDS, , , acFormReadOnly, , OpenArgs:=sSQL

Exit_Sub:

    Exit Sub

Error_Handler:

    MsgBox Err.Description & " Error No: " & CStr(Err.Number)

    Resume Exit_Sub

End Sub

Function fIsLoaded(ByVal strFormname As String) As Boolean

On Error GoTo Error_Handler

    'Returns False if form is not open or True if Open
    If SysCmd(acSysCmdGetObjectState, acForm, strFormname) <> 0 Then

        If Forms(strFormname).CurrentView <> 0 Then
            fIsLoaded = True
        End If

    End If

Exit_Function:

    Exit Function

Error_Handler:

    MsgBox Err.Description & " Error No: " & CStr(Err.Number)

    fIsLoaded = False

    Resume Exit_Function

End Function

'Create a datasheet view form (named frm_Display_ADO_Recordset_Result1) with 30 textboxes and 30
'30 labels named txt1 - txt30 and lbl1 - lbl30 and put this code in the form's module:

Option Compare Database

Private Sub Form_Load()

On Error GoTo Error_Handler

    Dim conn        As ADODB.Connection
    Dim rs          As ADODB.Recordset
    Dim rsClone     As ADODB.Recordset
    Dim strsql      As String

    Set conn = CurrentProject.Connection
    Set rs = New ADODB.Recordset

    strsql = Me.OpenArgs   
    rs.Open strsql, conn, adOpenStatic, adLockOptimistic

    Set rsClone = rs.Clone

    Call Update_Form_Controls("your text goes here", strsql, rsClone)

Exit_Sub:

    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing

    Exit Sub

Error_Handler:

    MsgBox Err.Description & "; Error Number : " & Err.Number, vbOKOnly

    Resume Exit_Sub
End Sub

Sub Update_Form_Controls(Header_Label As String, SQL As String, CloneRS As Recordset)

    Dim rsCount As Integer
    Dim i As Integer

On Error GoTo Error_Handler

    Me.Form.Caption = Replace(SQL, "Select * From ", "Display: ")

    rsCount = CloneRS.RecordCount

    If rsCount <= 0 Then

        MsgBox "The Query did not return any data to view", vbOKOnly

        DoCmd.Close
    Else

        Me.Form.SetFocus

        Me.RecordSource = SQL

        i = 1

        Do Until i = 31

            Me("lbl" & i).Caption = ""
            Me("txt" & i).ControlSource = ""
            Me("txt" & i).ColumnHidden = True

            i = i + 1

        Loop

        i = 1

        With CloneRS

        For Each Field In .Fields
        On Error Resume Next

            Me("lbl" & i).Caption = .Fields(i - 1).Name
            Me("txt" & i).ControlSource = .Fields(i - 1).Name
            Me("lbl" & i).Visible = True
            Me("txt" & i).ColumnHidden = False
            Me("txt" & i).SizeToFit

            i = i + 1

            'Debug.Print Field.Name

            On Error GoTo 0
        Next Field
        End With

    End If

Exit_Sub:

    Me.Requery

    Exit Sub


Error_Handler:

    MsgBox Err.Description & "; Error Number : " & Err.Number, vbOKOnly

    Resume Exit_Sub


End Sub