Report filter not working

[davecar]

I am trying to print a report based on the current (or selected) record on a form, actually a subform within a main form. I tried using a query as the basis for the report with the where condition equaling the form control, i.e., WHERE (((tblCorrectiveAction.CAID)=[Forms]![frmCorrectiveActionDetail]![CAID])) but it resulting report shows all records not the current or selected record on the subform.

I also tried the same with a filter within the report as tbl.CorrectiveAction = [Forms]![frmCorrectiveActionDetail]![CAID] and filter set on , and it always prompts me for the CAID.

I think the filters may not be able to uniquely identify the single current / selected record because if I run a query without opening the report it will return all the records not just the current / selected one.

Any suggestions ?

 

[KenReay]

Hi

THe easisest way I can think of to do this, is to put a control on the MAIN form (say txtDummy) with visible set = false

make your where clause

WHERE (((tblCorrectiveAction.CAID)=[Forms]![frmCorrectiveAction]![txtDummy]))

in the subform (frmCorrectiveActionDetail) on current event put Parent.txtDummy = CAID
this is assuming you main form is called frmCorrectiveAction, and your subform is called frmCorrectiveActionDetail

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[dhookom]

The syntax for referencing a control on a subform is:
Forms!frmMainForm!sfrmControlName.Form.txtYourControl
You can use this as a criteria in your report's record source.

Duane
MS Access MVP