Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Using a Dynamic Queary as a Subform RecordSource

Using a Dynamic Queary as a Subform RecordSource

Using a Dynamic Queary as a Subform RecordSource

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??

CODE -->

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]
    ' Set frm.lblReportTitle = strReportTitle
    frm.Visible = True 

RE: Using a Dynamic Queary as a Subform RecordSource

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

Hook'D on Access
MS Access MVP 2001-2016

RE: Using a Dynamic Queary as a Subform RecordSource

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

RE: Using a Dynamic Queary as a Subform RecordSource

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.

Hook'D on Access
MS Access MVP 2001-2016

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close