Using a Dynamic Queary as a Subform RecordSource

[5imon]

The objective is for the user to select data fields and parameters, then see on screen the information they have requested.

The selection form generates the query and updates the 'dynamin query' SQL (from one, two or three tables with Where clauses as required) and works well - I can open the query and see the SQL, fields, and data, as expected.

After generating the query, the code should open a display form, with a subform displaying the data from the query in datasheet view. This is failing.

The code is opening the display form, but fails to show any data within the subform.

The subform RecordSource, (by default Select * from the dynamic query), is blank at runtime. Ideally, this should be replaced/refreshed by the code.

Currently, the code is suffering from syntax errors - I believe it's incorrectly referencing the subform RecordSource control (although I have tried many ways).

As the dynamic query name doesn't change, do I need to rewrite the RecordSource whenever the screen is opened (the number of fields and their names do change), or can I just get the subform to refresh it's fields?

The code for updating the RecordSource is below, can someone see where my form/subform referencing is going wrong??

    Dim strDisplayData As String
    Dim strDataGrid As String
    Dim strDataQry As String
    Dim frm As Form
    
    strDisplayData = "fReport-ViewDynamic"          'main display form
    strDataGrid = "fReport-ViewDynamic-sfData"      'subform containing data
    strDataQry = "SELECT qDynamicQuery.*, * FROM qDynamicQuery;"
    
    'Open form as Hidden to apply changes - currently unhidden for checking and correcting
    DoCmd.OpenForm strDisplayData, acNormal ', , , , acHidden
   
    Forms(strDisplayData).Controls(strDataGrid).Form.Controls.RecordSource = (strDataQry)
                                                  
    'Unhide the form
    Set frm = Forms![fReport-ViewDynamic]
    Forms![fReport-ViewDynamic].SetFocus
    ' Set frm.lblReportTitle = strReportTitle
    frm.Requery
    frm.Visible = True

 

[dhookom]

I would just set the subform control's Source Object like:
Source Object Query.YourSavedQueryNameHere

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[5imon]

Thank you Duane (dhookom)

I didn't know it was possible use a query as a subform source! Maybe not a solution to be used often, but works well in this case.

Thank you

 

[dhookom]

I'm not sure how I discovered this but I've been using it for years to display data in a form without any clue regarding the data and structure.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016