VBA in report question

[ucdcrush]

Hi - I have a report where I would like to modify the Header appearance
(like textbox.visible = false) based on values in the Detail.

The problem I'm having is that there are multiple values by the same
variable name in the detail, and if I write something like this in the
header code:
--------------
Private Sub GroupHeader1_Format(Cancel As Integer, FormatCount As
Integer)
If Trim(MedicnType & "") = "Other" Then
Me.OtherDescLabel.Visible = True
else
Me.OtherDescLabel.Visible = False
End if
--------------

It seems to only "look" at the first MedicnType it comes across, and if
the first one is "Other" then it (correctly) displays the
"OtherDescLabel" label in the header. But if there are 3 rows of data
returned for the report (3 diff doctors visits) that it's looking in,
and if MedicnType of the first row = "Blood pressure", even if the 2nd
and 3rd rows are "Other" it goes off the first row/record and will do
Me.OtherDescLabel.Visible = False

So the question is, how can I test for the existence of "Other" within
MedicnType values so that I can properly show or hide the label?

Thanks.

 

[Guest]

I think you'd need to look up the rows in the underlying table or query for
the current group value. Lets assume the report is grouped by PatientID and
this is a number data type:

Dim strCriteria As String

strCriteria = "PatientID = " & Me.PatientID & " And MedicnType =
""Other"""

Me.OtherDescLabel.Visible = _
Not IsNull(DLookup("YourID", "YourTableOrQuery", strCriteria))

where YourTableOrQuery is the name of the underlying table or query and
YourID is its primary key or at least a field which cannot be Null in the
table or the query's result set. Note that PatientID would need to be a
bound control in the report as, unlike in a form, you can't refer directly to
a field in the report's underlying recordset in the report's module. If
necessary use a hidden bound control in the group header.

 

[ucdcrush]

Thanks a lot Ken, I will give this a try.