Ms Access Displaying Vba Select Query in Datasheet

MS Access displaying vba select query in datasheet

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.

Displaying VBA SQL String Query as a datasheet on a form in MSAccess

A few main issues are present with your current setup:

1. The reason you do not see any errors is On Error Resume Next which tells compiler to continue to next line on any runtime error and does not inform user of any issue. You should be raising errors in an error handling process with MsgBox. See this note.

2. You cannot use an SQL SELECT string in DoCmd.OpenQuery as you are attempting with:

   DoCmd.OpenQuery qdf.SQL, acViewNormal, acEdit
   

   You must use a saved query and reference its name in the command:

   DoCmd.OpenQuery "mySavedQuery", acViewNormal, acEdit
   

3. Continue to use QueryDef's .SQL property for dynamic SQL but be sure to release querydef to save changes prior to opening the datasheet view:

   sSQL = "..."
   
   Set db = CurrentDb
   Set qdf = db.CreateQueryDef("QrySchedComp")
   
   Application.RefreshDatabaseWindow
   qdf.SQL = sSQL
   
   ' RELEASE QUERYDEF TO SAVE CHANGES
   Set qdf = Nothing
   Set db = Nothing
   
   DoCmd.OpenQuery "QrySchedComp", acViewNormal, acEdit
   

---

With that said, consider parameterized queries using QueryDef parameters that avoids quote/pound sign enclosures and string concatenation, and abstracts data from code, not to mention it safely avoids SQL injection, a potential issue of user input.

And since you are iterating across a multiple select listbox, consider using a temp table that replicates structure of current query. This might even yield better performance for your users as you avoid the JOIN on a UNION query in datasheet for any filtering and searching needs.

SQL

Make-table action query to create temp table to be run only once to replicate structure:

SELECT TOP 1 * INTO tmpSchedComp FROM QrySchedComp

Save action query only once as a stored, parameterized query (no data):

PARAMETERS SiteParam Text(255), ScheduleParam Text(255), 
           DateStartParam Datetime, DateEndParam Datetime;
INSERT INTO tmpSchedComp ([Task No], [Task Detail], [USVF Compliance], 
                          [DIO Contract Compliance],  [Other Compliance])
SELECT BFMA_TaskList.Task AS [Task No], [Task Group] & '' & [Task Details] AS [Task Detail], 
       '' AS [USVF Compliance], '' AS [DIO Contract Compliance], '' AS [Other Compliance]
FROM [BFMA_TaskList] 
INNER JOIN [Qry_Union] ON [BFMA_TaskList].[Task] = [Qry_Union].[Task] 
WHERE [Qry_Union].[Site] = [SiteParam] AND [BFMA_TaskList].[Schedule] = [ScheduleParam]
  AND [Qry_Union].[Planned Date] BETWEEN [DateStartParam] AND [DateEndParam]

VBA (run loop without OR concatenation to append records per site, then open temp table)

...
Set db = CurrentDb
' CLEAN OUT TEMP TABLE
db.Execute "DELETE FROM tmpSchedComp", dbFailOnError

' INITIALIZE QUERYDEF (ACTION QUERY)
Set qdf = db.CreateQueryDef("AppnSchedComp")

' POPULATE TEMP TABLE
For Each varItem In Me.lstsite.ItemsSelected      
   ' BIND VALUES TO PARAMETERS
    qdf!SiteParam = Me.lstsite.ItemData(varItem)
    qdf!ScheduleParam = Me![TxtSchLetter]
    qdf!DateStartParam = Me![txtschfrom]
    qdf!DateEndParam = Me![txtschto]

    ' EXECUTE ACTION
    qdf.Execute dbFailOnError
Next varItem

Set qdf = Nothing
Set db = Nothing

DoCmd.OpenTable "tmpSchedComp", acViewNormal

MS Access VBA - display dynamically built SQL results in datasheet subform

If the "object that is closed or doesn't exist" error occurs on the Me.dataDisplaySubform.Form.RecordSource line, chances are your subform control is not named dataDisplaySubform.

You can examine the names of all your form's subform controls with this temporary change to your code ...

'Me.dataDisplaySubform.Form.RecordSource = pSQL
Dim ctl As Control
For Each ctl In Me.Controls
    If TypeName(ctl) = "SubForm" Then
        Debug.Print ctl.Name, TypeName(ctl)
    End If
Next
Stop

The Stop statement will trigger debug (break) mode and take you to the Immediate window where you can view the names of your form's subform control(s).

The screenshot you added to the question confirms you're using the correct name for the subform control. However, that subform has nothing in its Source Object property. Since there is no form there, the second part of the error message, "doesn't exist", applies. There is no form to be referenced by Me.dataDisplaySubform.Form

how to show query results in a datasheet in the same form?

You can use Allen Browne's Search criteria form example as the basis for your search form. He provides a sample database in Access 2000 format and also a page which displays the code behind the form.

Edit: You wanted your query results presented as a datasheet in the same form. AFAIK, there is no way to display your search control on a form in datasheet view. If a continuous form is acceptable, Mr. Browne's example should be easier for you to adapt. But if you must have the query results in datasheet view, you can add a subform (with its Default View property set to Datasheet) to the search form. However, that approach will require you to apply the filter to the subform rather than the main form.

Dynamically create Datasheet from SQL query with VBA in Ms Office Access

This doesn't seems efficient, but it is working and a bit satisfactory:

DoCmd.RunSQL "IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS " & _
     "WHERE TABLE_NAME = 'tv') DROP VIEW tv"
DoCmd.RunSQL "create view tv as " & txtQry
DoCmd.OpenView "tv"

Here I am creating a temporary VIEW (tv) in a button's click event. Before creating the view, I am checking that if a view with the same name exist or not; and if it exist then delete it so that a new view can be created with the same name with different query.

Displaying results in subform only works for first query

I never use dynamic parameterized query. I would use VBA code to set Filter and FilterOn properties, like:

Me.Filter = "[FName] LIKE '*" & Me.cbxFNames & "*'"
Me.FilterOn = True

If you prefer dynamic parameterized query the VBA would be: Me.Requery

If code is behind the main form then need to reference subform through container control. Recommend giving the container control a name different from the object it holds, like ctrEmps

Me.ctrEmps.Requery

The real trick is figuring out what event to put code into.

---

Related Topics