Display queries in access subform from a selected listbox

Question

Within Access 2010, I am trying to make a form display a query within a subform QueriesSubForm that when selected from listbox QueryListBox and then click on the button runbtn. It is supposed to display the query within the linked subform, and change when you do this whole steps again from a different choice selected on the listbox and click on the button.

At the moment I can only get it to work, if I want it to display the queries in a new task window, when programmed like so:

QueriesListBox Listbox - SQL View

SELECT MSysObjects.[Name]
FROM MSysObjects
WHERE (((MSysObjects.[Type])=5) AND ((Left([Name],1))<>"~"))
ORDER BY MSysObjects.[Name];

runbtn Button - VBA View

Private Sub runbtn_Click()
DoCmd.OpenQuery QueryListBox, acViewNormal
End Sub

It works and appears to just open one of the queries from the queries list and display it to me, which I do not want.

This might be an easy thing, but I can not see how it is done with either SQL, VBA or Macro, which I am guessing is need for this to work

How many different queries do you have? Do you think you'll need to be adding in more queries? I can think of one option that might work, but it wouldn't be very scalable. — DasPete

DasPete DasPete · Accepted Answer · 2013-04-04T21:22:24

Ok, here's one way to do it. First make sure your subform is set up with no source object. Then setup your button click like so:

Private Sub runbtn_Click()
    Me.QueriesSubForm.SourceObject = "Query." & QueryListBox.Value
End Sub

That should get you the result you're looking for. Alternatively, if you want your form to open with a certain query to displayed on open, you could set up your subform's source object to that query. Using the properties window find the source object drop down and find the query you want to show when the form opens.

Display queries in access subform from a selected listbox

1 Answers