MS Access displaying vba select query in datasheet

1
votes

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")
2
sqlvbams-accessselectdatasheet
Why not just save as a named query, then open that query in datasheet view? ... DoCmd.OpenQuery "YourQueryName" – HansUp
the SQL that needs to be executed is different for each combination of checkboxes and I have an if else chain to choose between the combinations. each if block saves the SQL statement to a string and then after the block i'm looking to execute the select statement and have it displayed in a datasheet. I've tried having an existing select query and then updating its SQl but that doesn't seem to work. – ChadT
Where does it fail, and what is the error message? – HansUp
What happens if you change Not Is Null to Is Not Null? – HansUp
Please realise you were essentially asking us how to display broken SQL in Datasheet View. That is not possible. – HansUp

2 Answers

4
votes

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.

3
votes

I'd suggest using a pre-built form and query for the purpose you're trying to achieve here.

But, saying that, you're learning to use SQL within VBA so here's how to do it with code (other ways are more than likely possible):

  • Create a basic form and add the correct number of controls per record.
  • Set the Has Module property for the form (in Other tab) to Yes.
  • Set the Default View to DataSheet.
  • Save the form as MyForm.

Add a VBA code module and add this code: 

Sub Test()

    Dim qd As DAO.QueryDef
    Dim rs As DAO.Recordset
    Dim frm As Form_MyForm

    Set qd = CurrentDb.CreateQueryDef("", "PARAMETERS Stats Text(255); " & _
                                          "SELECT * FROM EXPORT_CERTIFICATION " & _
                                          "WHERE CertificationStatus = Stats")

    qd.Parameters("Stats") = "Certified"

    Set rs = qd.OpenRecordset

    Set frm = New Form_MyForm

    'The record source for the form
    Set frm.Recordset = rs

    'The record source fields attached to each control.
    frm.Text0 = "FieldA"
    frm.Text2 = "Field"
    frm.Text3 = "CertificationStatus"

    frm.Visible = True

    Debug.Assert False 'Form will disappear when code ends, so pause here.

End Sub

A form will appear containing your recordset.