Select a list of queries from combo box?

  • Thread starter Thread starter Randy
  • Start date Start date
R

Randy

I had an idea that it would be nice to select a particular query or reports
from a combobox. I would like all my queries or reports, lets say 10,
listed in a combobox, select the query/report and have it fire. Is this
possible? How would I do this..Thanks..Randy
 
I had an idea that it would be nice to select a particular query or reports
from a combobox. I would like all my queries or reports, lets say 10,
listed in a combobox, select the query/report and have it fire. Is this
possible? How would I do this..Thanks..Randy
Click to expand...

To open a report:
As Rowsource for a combo box (or List Box):
SELECT MSysObjects.Name FROM MSysObjects WHERE
(((Left([Name],1))<>"~") AND ((MSysObjects.Type)=-32764)) ORDER BY
MSysObjects.Name;

code the AfterUpdate event:
DoCmd.OpenReport Me!comboName, acViewPreview


To open a query:
As Combo Box rowsource:

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

Then to have the user run the query after selecting it, place code in
the Combo/List Box AfterUpdate event (or from a Command Button):

DoCmd.OpenQuery Me!ComboName

The problem with this method is that the combo box displays the actual
name of the report/Query rather than a user friendly name.
It might be worth while creating a table to hold the actual and user
friendly names. Use the table as the combo Rowsource, hiding the
actual name.
As long as the actual name is the bound column, when the user selects
the friendly name, it will still open the correct report or query.
You'll need to maintain the table if the user can add new
reports/queries.
 
Randy said:
I had an idea that it would be nice to select a particular query or reports
from a combobox. I would like all my queries or reports, lets say 10,
listed in a combobox, select the query/report and have it fire. Is this
possible? How would I do this..Thanks..Randy
Click to expand...

Hi, Randy

There are many ways to do what you want; this is the method we use.

All of the reports that are available all begin with "+R: *" (without the
quotes).
So the report name would be like: +R: StatementByMth and if it wasn't ready
to be used yet, the name would be: StatementByMth

Create an unbound form. Add any unbound control for any parameters you need.
Add an unbound listbox; we named it "lstReports". We use a listbox because
it is easier to see the reports - one less click.

The list box row source is a query; the SQL looks like this:

SELECT msysobjects.Name
FROM msysobjects
WHERE (((msysobjects.Name) Like "+R: *"))
ORDER BY msysobjects.Name;

Below the list box is a button with the caption OPEN. The click event is:

Private Sub cmdOpen_Click()
On Error Resume Next
DoCmd.OpenReport lstForm, acViewPreview
On Error GoTo errHandler
errHandler:
End Sub

There are also two events for the list box OnClick and OnDoubleClick. The
OnClick enables the button (default is Enabled=False). Double clicking a
report in the list box runs the report, just like clicking once on a report,
then clicking the OPEN button.
The code for the click events is:

Private Sub lstReport_Click()
cmdOpen.Enabled = True
End Sub

Private Sub lstReport_DblClick(Cancel As Integer)
cmdOpen_Click
End Sub

That is all it takes....

HTH,

Steve
 
Back
Top