code for reports

[SPK]

Hi all,

Can we write code in the report. My challenge is I have to hide some fields
in the reports based on the value of the other field. Can we do this. I am
working with access 2000 version.

 

[John Spencer]

In the Format event of the report you can use code like

IF Me.SomeControl = 22 THEN
Me.SomeOtherControl.Visible = False
ELSE
Me.SomeOtherControl.Visible = True
END IF

If you just want to hide the value, you can use conditional formatting to turn
the color of the print to white.

Or you can use and expression in the report's underlying query to display the
value or set the value to null depending on the value of the other field

Field: ShowHideFieldB: IIF(FieldA = 22, Null, FieldB)

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[SPK]

Hi John
there I have tried the following code but not of any use. can you point out
where exactly I'm going wrong
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Result_Incub = "Positive" Then
Me.Sample_Analysis_Dt.Visible = False
Me.Result.Visible = False
Else
Me.Sample_Analysis_Dt.Visible = True
Me.Result.Visible = True
End If
End Sub

 

[John Spencer]

Do you get any error message?
Does nothing happen at all?

Things to check.
1) Is Result_Incub in your query? (Try typing Me.result_incub and see if it
gets captialized when you leave the line)
2) Is Result_Incubincluded on the report (even if it is hidden)?
3) Is Result_Incub a text field that contains "Positive" as a value or is it
another type of field?

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[SPK]

Nothing happen at all.
The answers for your questions are as follows

1. Yes the text changes to title case
2. It is included in the report
3. It is a list box text field with positive/negative as allowed values in
the field

 

[John Spencer]

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Result_Incub = "Positive" Then
Me.Sample_Analysis_Dt.Visible = False
Me.Result.Visible = False
Else
Me.Sample_Analysis_Dt.Visible = True
Me.Result.Visible = True
End If

End Sub

Hmm. The code looks fine to me. Try a little trouble-shooting to see if the
code is executing at all.

Change the line
If Result_Incub = "Positive" Then
to
If TRUE Then

See what happens and then change the line to
IF FALSE Then
and see what happens.

If your results change then we know the code is running and we can look at the
If line. Try it as
IF ME.Result_Incub Then

Also, make sure the control bound to Result_Incub is in the detail section so
that it changes as as the detail changes.


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County