query with multiple comboboxes to create report

[Duane8889]

Hello
I found this query bit that does a nice job of retrieving all the tables in my db.

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

So I have a combobox that uses this query to pull up all the table names. What I would like to do with this is have two more comboboxes the first with a start date the 2nd with an end date. Those dates would be tied to the selected table in the combobox. Then, use the parameters to pull a report based on those parms. I know I don't want much.

Please help I'm trying to impress some dictators at work and need some ammunition.

VinD

 

[Remou]

Hi
If you had several reports with the same name as the tables, something like this might work:

Private Sub cmdPreviewReport_Click()
Dim strSQL

strSQL = "Select * from " & Me!cboTable & "Where Date >=#" & Me!txtStartDate _
& "# And Date <=#" & Me!txtEndDate & "#"

DoCmd.OpenReport Me!cboTable, acViewDesign
Reports(Me!cboTable).RecordSource = strSQL
DoCmd.Save acReport, (Me!cboTable)
DoCmd.OpenReport Me!cboTable, acPreview
End Sub

If you had the same report but wished to use it with different tables and dates, you could do something similar, except:
DoCmd.OpenReport "StandardReport", acViewDesign

Perhaps you would prefer to set the start and end dates as filters?

Just a few ideas ... hmm, hmm