Report based on Form's Combo box selection

[mba_110]

Hi everyone,

I am facing some difficulties in producing reports for my database, I have table called payments_table in which I have about 100+ entries for payments to employees for their privilege utilization one employee can have many and other can have one its not predictable it all upon the utilization they have made.

What I need now is I have form called AnnualLeave_Form whenever anyone request new leave we need their history how much they have utilized before to produce this history I need reports I want a command button on form when I click that button this should open report for that employee based on combo box selection on this form which is [Emp_IDCombo] and do not include any other employee’s data in that report.

I have already made the query and necessary tables for these fields I just want the method how can I do this and I would prefer some modification on this but that is not an issue now mainly I need this method.

Here some important reference & relationships for my project database.

Report Name: BenefitUtilization_Report based on UtilizationReport_Query which is having fields [Pmt_ID],[Contract_ID],[Emp_ID],[EmployeeName], and all the necessary Category of payments I have used all this three tables field to create the UtilizationReport_Query in design view.

Relationships: Employees_table connected to Contracts_table via [Emp_ID] and Payments_table connected to Contracts_table via [Contract_ID] all are one-many relationships.

 

[smozgur]

Standard method I would follow to achieve this is filtering the report by using Report properties.

1- Open Report Properties dialog.
2- Select Data tab.
3- Record Source must be already UtilizationReport_Query
4- Set Filter On property to be "Yes"
5- Enter following filter text in the Filter property. Make sure you changed the form name before entering this. It is simply WHERE clause to filter data on your report.

[COLOR=#333333]Emp_ID=[/COLOR][COLOR=#333333]Forms!FORMNAMEHERE![/COLOR][COLOR=#333333]Emp_IDCombo[/COLOR]

Warning: Form must be open to get this work. It means it is going to work when you click on a button to open the report on the form but it is not going to work if you double click on the report object without Form is opened. Basically, Forms!FORMNAMEHERE!Emp_IDCombo looking for the data in the specified form in the forms collection.

You can even do the same in the base query, or even via VBA.

Hope this helps.