Input for reports from a form

[Martin Hopkins]

Hello,

I have several reports that are configured to run with the input parameter
set by the user in his selection via a form.

The user selects either a Dept, or a trade or an office. The report output
relates to the selection by means of an input criteria in the underlying
query on the report.

I have 3 lines of crieria:

1. Dept = forms!dept
or
2. trade = frorms!trade
or
3. office = forms!office

all runs well with only 1 criteria chosen,

But if i want ot run a report for say Manufacturer Dept and Welders, then I
get all the dept Manufacturer returned as well as the welders.

How do I select a dept and only Welders.? Can I use an Group Option? Or do I
cange the underlying query?

Any help greatly appreciated.

Martin Hopkins

 

[Guest]

I don't know if my previous post made it or not, but this is not that
difficult to do. It would be helpful to know the relationships between
Manufactures, Trades, and Offices. Like One Manufacturer has 1 to many
trades, etc.

Then using 3 cascading combos, you can make the selections and build the
criteria in VBA. Describe the relationships, and I can show you how to do it.

 

[Martin Hopkins]

Klatuu,

In Personnel there is:

Dept_id linked to Dept_id in Dept Table
Trade_id linked to trade_id in trade table
Office_id linked to Office_id in Office table

also in Office table there is a link to Trade_id in trade table.

Many trades can work in one office and many offices in one dept.

eg. Engineering Dept has plummer, builder, crane operator and has several
offices site office, yard office etc

And the Enginnering dept is part of the company branch in an area.

If you could help with te cascading combos I think I can pick up the rest.

martin Hopkins

 

[Guest]

based on your description, the 3 combos, in order, would be Deparment,
Office, and Trades. The trick to cascading combos in fairly simple. Each
combo's row souce needs to be filtered on the content of the preceding combo
and each combo must requery suceeding combos in the After Update event.
Row souce for the first combo does not need to be filttered:

SELECT DEPT FROM tblDepartments;

The the next combo filters on the previous:

SELECT OFFICE FROM tblOffices WHERE DEPT = Me.cboDepartments;

Same for the next combo:

SELECT TRADE FROM tblTrades WHERE OFFICE = Me.cboOffices.

Then in the After Update event of the department combo:

Me.cboOffices.Requery

And in the After Update event of the Office Combo:

Me.cboTrades.Requery