sql - MS Access displaying vba select query in datasheet

Question

Learning to use SQL inside ms access vba. I've gotten how select queries work but they don't immediately output to a datasheet like one built through the access forms. I needed complicated if else chains to get what I wanted so I had to use the vba route. I've tried several methods that have been suggested in other questions but they haven't worked like I want.

I'm creating the code for a button which displays a datasheet with particular elements based on check boxes. the code that follows will just be about displaying the elements of the Select query.

I tried the .QueryDef method

Dim qd As QueryDef
Set qd = CurrentDb.CreateQueryDef("")

With qd
    .ReturnsRecords = True
    .sql = "SELECT * FROM EXPORT_CERTIFICATION WHERE EXPORT_CERTIFICATION.CertificationStatus = 'Certified'"
End With

This worked but only once when I put in a query name and not when empty. it created a new query which does what I want but then the button doesn't do anything after that.

should I create a table to accept the output and try to set that table to the recordset created by the sql statement? i'd like to avoid having another table since this is just used for viewing.

Here is the full code with my current try at a simpler solution

Private Sub NDC_CERT_VIEW_Click()
Dim StrSQLclause As String
Dim db1 As DAO.Database, qry1 As DAO.QueryDef

Set db1 = CurrentDb()

Set qry1 = db1.QueryDefs("NDC_EXPORT_VIEW")

MsgBox ("Here")
If (Certified_Check And Not Revised_Check And Not Doc_Exceptions_Check And Not Pending_Check) Then
    StrSQLclause = "Select * From EXPORT_NDC_CERTIFICATION Where EXPORT_NDC_CERTIFICATION.CertificationStatus = 'Certified' "
ElseIf (Not Certified_Check And Revised_Check And Not Doc_Exceptions_Check And Not Pending_Check) Then
    StrSQLclause = "Select * From EXPORT_NDC_CERTIFICATION Where EXPORT_NDC_CERTIFICATION.CertificationStatus = 'Revised' "
ElseIf (Not Certified_Check And Not Revised_Check And Doc_Exceptions_Check And Not Pending_Check) Then
    StrSQLclause = "Select * From EXPORT_NDC_CERTIFICATION Where EXPORT_NDC_CERTIFICATION.DocumentException Not Is Null "
ElseIf (Not Certified_Check And Not Revised_Check And Not Doc_Exceptions_Check And Pending_Check) Then
    StrSQLclause = "Select * From EXPORT_NDC_CERTIFICATION Where EXPORT_NDC_CERTIFICATION.CertificationStatus = '' "
ElseIf (Certified_Check And Revised_Check And Not Doc_Exceptions_Check And Not Pending_Check) Then
    StrSQLclause = "Select * From EXPORT_NDC_CERTIFICATION Where EXPORT_NDC_CERTIFICATION.CertificationStatus = 'Certified' Or EXPORT_NDC_CERTIFICATION.CertificationStatus = 'Revised' "
ElseIf (Not Certified_Check And Revised_Check And Doc_Exceptions_Check And Not Pending_Check) Then
    StrSQLclause = "Select * From EXPORT_NDC_CERTIFICATION Where EXPORT_NDC_CERTIFICATION.DocumentException Not Is Null Or EXPORT_NDC_CERTIFICATION.CertificationStatus = 'Revised' "
ElseIf (Not Certified_Check And Not Revised_Check And Doc_Exceptions_Check And Pending_Check) Then
    StrSQLclause = "Select * From EXPORT_NDC_CERTIFICATION Where EXPORT_NDC_CERTIFICATION.DocumentException Not Is Null Or EXPORT_NDC_CERTIFICATION.CertificationStatus = '' "
ElseIf (Certified_Check And Not Revised_Check And Not Doc_Exceptions_Check And Pending_Check) Then
    StrSQLclause = "Select * From EXPORT_NDC_CERTIFICATION Where EXPORT_NDC_CERTIFICATION.CertificationStatus = 'Certified' Or EXPORT_NDC_CERTIFICATION.CertificationStatus = '' "
ElseIf (Not Certified_Check And Revised_Check And Doc_Exceptions_Check And Pending_Check) Then
    StrSQLclause = "Select * From EXPORT_NDC_CERTIFICATION Where EXPORT_NDC_CERTIFICATION.CertificationStatus = 'Revised' Or EXPORT_NDC_CERTIFICATION.CertificationStatus = '' Or EXPORT_NDC_CERTIFICATION.DocumentException Not Is Null "
ElseIf (Certified_Check And Not Revised_Check And Doc_Exceptions_Check And Pending_Check) Then
    StrSQLclause = "Select * From EXPORT_NDC_CERTIFICATION Where EXPORT_NDC_CERTIFICATION.CertificationStatus = 'Certified' Or EXPORT_NDC_CERTIFICATION.CertificationStatus = '' Or EXPORT_NDC_CERTIFICATION.DocumentException Not Is Null "
ElseIf (Certified_Check And Revised_Check And Not Doc_Exceptions_Check And Pending_Check) Then
    StrSQLclause = "Select * From EXPORT_NDC_CERTIFICATION Where EXPORT_NDC_CERTIFICATION.CertificationStatus = 'Certified' Or EXPORT_NDC_CERTIFICATION.CertificationStatus = '' Or EXPORT_NDC_CERTIFICATION.CertificationStatus = 'Revised' "
ElseIf (Certified_Check And Revised_Check And Doc_Exceptions_Check And Not Pending_Check) Then
    StrSQLclause = "Select * From EXPORT_NDC_CERTIFICATION Where EXPORT_NDC_CERTIFICATION.CertificationStatus = 'Certified' Or EXPORT_NDC_CERTIFICATION.DocumentException Not Is Null Or EXPORT_NDC_CERTIFICATION.CertificationStatus = 'Revised' "
ElseIf (Certified_Check And Revised_Check And Doc_Exceptions_Check And Pending_Check) Then
    StrSQLclause = "Select * From EXPORT_NDC_CERTIFICATION Where EXPORT_NDC_CERTIFICATION.CertificationStatus = 'Certified' Or EXPORT_NDC_CERTIFICATION.CertificationStatus = 'Revised' Or EXPORT_NDC_CERTIFICATION.DocumentException Not Is Null Or EXPORT_NDC_CERTIFICATION.CertificationStatus = '' "
Else
    MsgBox ("No Status Selected")
    Exit Sub
End If
MsgBox (StrSQLclause)
MsgBox ("Here3")

qry1.sql = StrSQLclause
MsgBox ("Here4")
DoCmd.OpenQuery "NDC_EXPORT_VIEW"

MsgBox ("Here6")

Answer 1

Personally, I use the following code to display recordsets.

Like Darren's answer, I have created a form, which I've named frmDynDS, with the default view set to datasheet view, and I've added 255 controls to it using the following code (run while the form is in design view):

Dim i As Long
Dim myCtl As Control
For i = 0 To 254
    Set myCtl = Application.CreateControl("frmDynDS", acTextBox, acDetail)
    myCtl.Name = "Text" & i
Next i

Then, I've added the following code to the form's module:

Public Myself As Object

Public Sub LoadRS(myRS As Object)
    'Supports both ADODB and DAO recordsets
    Dim i As Long
    Dim myTextbox As textbox
    Dim fld As Object
    i = 0
    With myRS
        For Each fld In myRS.Fields
            Set myTextbox = Me.Controls("Text" & i)
            myTextbox.Properties("DatasheetCaption").Value = fld.Name
            myTextbox.ControlSource = fld.Name
            myTextbox.ColumnHidden = False
            myTextbox.columnWidth = -2
            i = i + 1
        Next fld
    End With
    For i = i To 254
        Set myTextbox = Me.Controls("Text" & i)
        myTextbox.ColumnHidden = True
    Next i
    Set Me.Recordset = myRS
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Set Myself = Nothing 'Prevent memory leak
End Sub

Then, I've got the following code in a public module:"

Public Sub DisplayRS(rs As Object)
    Dim f As New Form_frmDynDS
    f.LoadRS rs
    f.Visible = True
    Set f.Myself = f
End Sub

After you have all this set up, displaying recordsets is very simple. Just do the following:

DisplayRS CurrentDb.OpenRecordset("SELECT * FROM EXPORT_CERTIFICATION WHERE EXPORT_CERTIFICATION.CertificationStatus = 'Certified'")

This will open up the form, make the appropriate amount of controls visible, set the caption, adjust cell width to accommodate the caption, and then bind the controls to the recordset. The form will persist until closed, and you can open up multiple recordsets simultaneously with this code.

Do note that you can't use parameters in the recordset when running this code, as it will crash on filtering/sorting.