open a report based on a combo box

[hellohello1]

I have a table with 2 fields that looks like this:

ReportID CriteriaCode
1 ...........Cost-R3
1 ...........Cost-Y6
2 ...........Sched-Y7
2 ...........Sched-R3
2 ...........Risk-R5

etc.

I have a report based on a query. The query contains a field called CriteriaCode.

I have a combo box on a Form that shows all the ReportIDs. If the user selects a ReportID and clicks on a command button, can I use the table above to build a Where statement to open the report?

So if a user chooses ReportID 1 on the Form, the report would open and the Where statement would be: Where CriteriaCode=Cost-R3 or Cost-Y6.

How can I do this?

Thanks,

 

[BofRossClan]

I just posted something similar in this thread:

http://www.tek-tips.com/viewthread.cfm?qid=1534136

http://www.accessbrenda.com

 

[TheAceMan1]

How are ya hellohello1 . . .

[blue] ... can I use the table above to build a Where statement to open the report?[/blue]

Yes you can!

Post the [blue]tablename[/blue], [blue]SQL of the combobox[/blue], [blue]SQL of the report[/blue], and the [blue]reportname[/blue].


See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[hellohello1]

Thanks to both of you for writing.

TheAceMan:

Table name is tblEscalationReports. It contains 2 fields: ReportID (Autonumber-primary key) and ReportName (Text).

Table name is tblEscalationReportsDetail. It contains ReportIDLink (Number) and Criteria Code (Text). Both are primary keys.

My ComboBox SQL is: SELECT tblEscalationReports.ReportID, tblEscalationReports.ReportName FROM tblEscalationReports ORDER BY [ReportName];

The Report is called RptKPIDetail.

The query behind the report is called qryKPIDetail. The query is pretty involved, but I simplified it below:

SELECT [tblProject Status Survey].[Project ID], [tblProject Status Survey].[As-Of Date], [tblProject Status Survey].ProjectName, [tblKPI Rating Rationale].[Criteria Code]
FROM [tblProject Status Survey] INNER JOIN [tblKPI Rating Rationale] ON ([tblProject Status Survey].[As-Of Date] = [tblKPI Rating Rationale].[As-of-Date]) AND ([tblProject Status Survey].[Project ID] = [tblKPI Rating Rationale].[Project ID])
WHERE ((([tblProject Status Survey].[As-Of Date])=#1/31/2009#));

Detail of the 2 tables involved in the query above:
The tblProject Status Survey has Project ID (Number) and As-Of Date (Date) as both primary keys. It contains other data such as monthly cost and risk fields. It gets data imported from an external source monthly.

The tblKPI Rating Rationale has Project ID (Number), As-of-Date (Date) and Criteria Code (Text) as all primary keys.

What I need is:
The user selects a ReportId from the Combo Box. The RptKPIDetail opens. That report is based on qryKPIDetail. The Where statement for that query would be created based on the tblEscalationReportsDetail which lists Criteria Codes for each report.

Thanks

 

[hellohello1]

Hello,

Just following up to see if anyone can help me.

Thanks,

 

[hellohello1]

I had help from someone who told me:

"create a query joining the two tables. For example:

SELECT Table1.Field1, Table1.Field2
FROM Table1
INNER JOIN Table2
ON Table1.CriteriaCode=Table2.CriteriaCode
WHERE Table2.ReportID = Forms!NameOfForm.NameOfCombobox
"

It works. My problem is resolved.

thanks