Report run by selecting from combo box

[Zonie32]

I have a form which has a combo box labeled "Status". The choices are RED, YELLOW, or GREEN.

I created a report and I want the user to be able to select from the combo box which color status they want to print a report on.

So if I create a form with command buttons to open the report (rptByStatus), I want a combo box to appear with the choices. Once the user clicks on RED for example, only the RED status will appear. Is there a way to make this happen? thanks.

 

[GingerR]

Hi there. If you check out the SEARCH feature of this web site, you'll find lots of posts dealing with this topic. Here's one: thread703-902856

Hope this helps--g

 

[Zonie32]

Thank you for the tip. I did see this post and tried it out, but it only prompts for a parameter. I want to see the actual combo box where the user can choose their option. This is where I cannot get it to work.

 

[GingerR]

What have you tried so far?
In your report, are you using the combo box as criteria, or are you passing the criteria with a WHERE statement in the button's OnClick event?

Please paste your button's OnClick event code here.
Also please paste your Report's Recordsource.
Also the name of your form, the combo box, and the report.

Thanks.

 

[Zonie32]

The OnClick is below. The Report RecSource is qryProjectsByStatus. The Form Name is frmProjectMaster. The ComboBox name is Status. The Report Name is rptByStatus. Thank you for your time by the way!

Private Sub cmdRptByStatus_Click()
On Error GoTo Err_cmdRptByStatus_Click

Dim stDocName As String

stDocName = "rptByStatus"
DoCmd.OpenReport stDocName, acPreview

Exit_cmdRptByStatus_Click:
Exit Sub

Err_cmdRptByStatus_Click:
MsgBox Err.Description
Resume Exit_cmdRptByStatus_Click

End Sub

 

[GingerR]

please open qryProjectsByStatus, view the SQL view, and paste that SQL here. Thanks.

 

[Zonie32]

SELECT tblStatus.Status, tblProjectMaster.ProjMgr, tblProjectMaster.Sponsor, tblProjectMaster.ProjNo, tblProjectMaster.ProdDesc, tblProjectMaster.LOB, tblProjectMaster.State, tblProjectMaster.Carrier, tblProjectMaster.Type, tblProjectMaster.Comments, tblProjectMaster.GeneralStatus, tblProjectMaster.ProjComplete, tblProjectMaster.MarketDtDue, tblProjectMaster.MarketDtActual, tblProjectMaster.WrittenDtDue, tblProjectMaster.WrittenDtActual, tblProjectMaster.EffDtDue, tblProjectMaster.EffDtActual, tblProjectMaster.PrelimProdSpecRecdDue, tblProjectMaster.PrelimProdSpecRecdActual, tblProjectMaster.FinalPlanDesignRecdDue, tblProjectMaster.FinalPlanDesignRecdActual, tblProjectMaster.FinalRatesRecdDue, tblProjectMaster.FinalRatesRecdActual, tblProjectMaster.FullSpecsDelivDue, tblProjectMaster.FullSpecsDelivActual, tblProjectMaster.SoftwareToUATDue, tblProjectMaster.SoftwareToUATActual, tblProjectMaster.SoftwareFinalDue, tblProjectMaster.SoftwareFinalActual, tblProjectMaster.LobUATDue, tblProjectMaster.LobUATActual, tblProjectMaster.RateEntryDue, tblProjectMaster.RateEntryActual, tblProjectMaster.CertProgDue, tblProjectMaster.CertProgActual, tblProjectMaster.CaseIssueDue, tblProjectMaster.CaseIssueActual
FROM tblProjectMaster LEFT JOIN tblStatus ON tblProjectMaster.Status = tblStatus.Status
ORDER BY tblStatus.Status, tblProjectMaster.Sponsor, tblProjectMaster.ProjNo, tblProjectMaster.ProdDesc, tblProjectMaster.LOB;

 

[GingerR]

you don't have any place where you are telling the report to open only the record that you have selected on the form.
You could do that in the query that the report is based on (put criteria directly in the query that references your form) or you can put it in the OnClick event of the button.

What is your unique ID? ProjNo? Is it a number or text?

In the OnClick event, change the last line to be

DoCmd.OpenReport stDocName, acPreview,,"ProjNo = " & ProjNo

 

[Larsson]

My suggestion is that you place the combobox on the form that has the button that open the report.

Then in the query that is the recordsource for the report you add a condition that Status = Forms!FormName!comboBoxName

You will get an error if the user doesn't select a value in the combobox, so you need to check taht the user has selected a value before you open the report.

Here is a snippet for the SQL:

FROM tblProjectMaster LEFT JOIN tblStatus ON tblProjectMaster.Status = tblStatus.Status
WHERE tblStatus = [Forms]![FormName]![comboBoxName]
ORDER BY tblStatus.Status, tblProjectMaster.Sponsor, tblProjectMaster.ProjNo, tblProjectMaster.ProdDesc, tblProjectMaster.LOB;

Markus