Dropdown list of Reports starting with SWP

[sdjames1979]

Hi there

I have a form in which I want to put a dropdown list of reports available, however, I only want the dropdown to show reports starting with 'SWP'.

I have created various SWP reports:

SWP-January
SWP-February
SWP-March
SWP-Invoicing

Thanks

S

 

[Minty]

The following will populate a list box or combo box

SELECT MsysObjects.Name, Mid([Name],5) AS ReportName FROM MsysObjects WHERE (((Left([Name],4))="SWP_")) ORDER BY MsysObjects.Name;

Put this in the Row Source for the control.

 

[Minty]

Actually - this would be much better if you had reports and your combo boxes selected the dates you wanted them to run over.

 

[Minty]

I think you really want something like this;

 

[sdjames1979]

That would be great, but unfortunately i'm a bit of a beginner.

 

[Minty]

That would be great, but unfortunately i'm a bit of a beginner.

It is actually a very straight forward thing to create.
At it's most basic it's just a form with the list box in it, and the names of your reports (I actually use queries) using the code I listed above.

The date boxes are simply unbound controls that default to the current month's start and end dates.
The queries can have the dates in the bottom of the form used as criteria.

The extra bits to export to excel etc are just nice user friendly add ons.

 

[jeremy.lankenau]

Minty,

Your pointers to sdjames were really helpful to me. I'm a beginner myself.

I've created a form (ReportViewer) that has a listbox of the reports, populated using the code you provided above.

Intended Filter1: I've also created a date range that will act as a filter with the default from date set to DateSerial(Year(Date), Month(Date),1) and to date set to DateSerial(Year(Date), Month(Date)+1,0). I have a date field from my main table that is in all my reports that I will want to be between the from and to dates on my form (ReportViewer)

Intended Filter2: I've created a unbound combo box with the values of Ongoing or Complete which refers to a field in my main table that reports are generated from (The action is either ongoing or complete and will be the most common filter next to the date range)

I'd now like to create two buttons that generates the report selected in the list box, applying the date and ongoing/complete filter. One button will be for opening in access, the other for exporting to excel. I've tried playing with macros for this, but references the listbox instead of a specific report name throws me errors. What would be the best way to go about having these buttons work as intended? I've reached a bit of a wall on this one. It's starting to get more complex then the very simple database I have, but my bosses want more flexibility in filtering their reports.

Thanks for all your help!
Jeremy