Basing combo boxes, visibility of fields on report field values

[SM]

Hi,

I'm new to access reports and have been tearing my hair
out over some issues with a report I need to write.

The report is based on a query which retrieves various
fields from several tables.
I've been trying to get a list (or combo, I'm not fussy!)
box to use a SQL select statement to get stuff from the
database based on the value of one of the fields in the
query the report is based on, but I get no value
displayed - the SQL for the List box Record source is

SELECT [TstFailDtls].[TestFailInformation] FROM
TstFailDtls WHERE [TstFailDtls].[ResultFailID]=[Reports]!
[TestReport]![TstFailDetailID];

It works fine if I hard code [Reports]![TestReport]!
[TstFailDetailID] to be , say 150, but fails if I ask it
to get the data from one of the report fields.

Is this perhaps because the report data has not been
retrieved when the data for the combo box is retrieved ??
How do I get around this ??
(I'd rather not use Left joins etc in the original report
query or I'll have about a million of them!).

Problem 2:
I've also been trying to make certain Subreports/controls
visible based on values of the report fields but this
doesnt work either. I tried setting the value using a
macro in the reports Footer sections OnFormat (This is
where the subreports I want to restrict are) but I get a
type mismatch error even though the same query works fine
as just a query.

Is this the right way to go about this??
Is there an easier way??

Any help on either of these appreciated!

Thanks,

Sara

 

[Jim Kennedy]

I think you'll be better off creating a form and
referencing the form controls in your query instead of the
report controls.

For problem 2 you can use the on_format event and then
write an 'if-else' condition. Something like:

Private Sub Header_Format(Cancel As Integer, FormatCount
As Integer)
if me.yourfield < 10 then
Me.Header.Visible = false
else
me.Header.Visible = true
end if
End Sub