Can You Do This With Access

[vince99]

I have created several queries and I would like to know if there is a way to put them in a drop down box so a user can select which query to run.....

Also, is there a way to do this:

Lets say you have a list of products ( a - z)

I want to create a drop down box in which I can select a product and it will return the results of a query corresponding to what I choose in the drop down box.

Thanks

 

[JerryDennison]

You can use the hidden system objects to populate a combobox with the names of the queries and then attach code to either the afterupdate event procedure for the combobox or to a button to then run the query.

You can use any valid control as criteria for any valid field in a query. For example, in the query field's criteria property put the path to the control you want to limit the query's result to:

Forms!FormName!ComboboxName

or

Like Forms!FormName!ComboboxName & "*"
(this will return all records if you leave the combobox blank)

 

[ghubbell]

Hi Vince,
Elaborating on Jerry's post here's a query you can paste in to the SQL view of a new query and see the results:

SELECT DISTINCTROW MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "MSys*" And (MSysObjects.Name) Not Like "~*&quot AND ((MSysObjects.Type)=5))
ORDER BY MSysObjects.Name;

Bingo! Gord
ghubbell@total.net

 

[CPUburn]

Phoea,

why make it so hard on yourself? why not just create and execute the query e.g. in the afterupdate of the combobox, then you just have to parse the parameters into your sql statement?

the kid

 

[scking]

How is the data displayed? In the subform?
Can the queries be parameterized?

Mostly just assigning the SQL query string the the RecordSource property of a form from a combobox is a good design if they display the same recordset data. For instance:

ComboBox1 (Allows users to select different organizations)
ComboBox1_AfterUpdate

Select Case ComboBox1
Case "Region1"
'Assign the sql for outputting only region1 to subform
Case "Region2"
'Assign the sql for outputting only region1
Case Else
' Whatever
End Select


Steve King Professional growth follows a healthy professional curiosity

 

[Cagliostro]

In design mode select your box.
In the properties of your box, look up the property source and just put a source query for this box. John Fill