Set Field 'Visible' property based on Field value in Report

[Archer]

Hi folks.



I have a field in a report which contains combined values i.e. =[D Med 1] & " " & [D Med 1 Dose] & "mg" & " " & [D Med 1 Freq]. I'm trying to set the field visible property to false if the field has no specific data in it, ie. only contains "mg" without the medication name or dose or frequency. The following code whilst not throwing up errors, isn't working:-

Private Sub Report_Load()
If Me.Discharge_Meds_10 Like "mg*" Then
Me.Discharge_Meds_10.Visible = False
Else: Me.Discharge_Meds_10.Visible = True
End If
End Sub

Can anyone offer any help on where i'm going wrong?

thanks in advance.

[Minty]

I think you would need to do this on the OnFormat property assuming it's multiline report.
If not conditional formatting to disable the control.

An alternative would be to use the "propagation of nulls" so if you added the values together

=[D Med 1] + " " + [D Med 1 Dose] + "mg" + " " + [D Med 1 Freq]

any null value would make the result null, automatically hiding it.

[CJ_London]

what does 'isn't working' mean?

where is the Discharge_Meds_10 control? in the header? detail? Is your report continuous? If the control is in the detail section and the report is continuous then suggest try the detail onformat event instead

You can also make the code much simpler

Me.Discharge_Meds_10.Visible= not(Me.Discharge_Meds_10 Like "mg*")

finally your code includes a space

=[D Med 1] & " " & [D Med 1 Dose] & "mg" & " " & [D Med 1 Freq]. #

so you probably need

Like " mg*")

[Archer]

Thank you guys. It was the 'On Format' and the " mg*" that was the issue.

Now if I have 10 'Med' fields and I want to apply the same rule for all 10 is it just a case of copying the code and updating the field number, ie. 1, 2 3 etc or does the code need to be varied?

thanks once again for your comments.

[June7]

Same expression. Copy/paste/modify.

If you can manage the calc in textbox, would be better than VBA.

Keep in mind, Format event does not trigger for ReportView, only PrintPreview or direct to printer.

Multiple med fields is not a normalized data structure.

[Archer]

Same expression. Copy/paste/modify.

If you can manage the calc in textbox, would be better than VBA. Yep the calcs are done in the text box.

Keep in mind, Format event does not trigger for ReportView, only PrintPreview or direct to printer.s.Yep am aware of that thanks.

Multiple med fields is not a normalized data structure.

It's working intermittently:

[June7]

Should post code as text between CODE tags, not an image.

Don't see how that structure can work at all. Each control should be an independent If Then Else. However, If Then Else not needed. Use Ajax's one liner for each control

Me.Discharge_Meds_1.Visible = Not Me.Discharge_Meds_1 Like " mg*"
Me.Discharge_Meds_2.Visible = Not Me.Discharge_Meds_2 Like " mg*"
...

Alternative:

For x = 1 to 11
Me("Discharge_Meds_" & x).Visible = Not Me("Discharge_Meds_" & x) Like " mg*"
Next

Or try Minty's expression in textbox and eliminate VBA.
=[D Med 1] + " " + [D Med 1 Dose] + "mg" + " " + [D Med 1 Freq]

[Archer]

Should post code as text between CODE tags, not an image.

Don't see how that structure can work at all. Each control should be an independent If Then Else. However, If Then Else not needed. Use Ajax's one liner for each control

Me.Discharge_Meds_1.Visible = Not Me.Discharge_Meds_1 Like " mg*"
Me.Discharge_Meds_2.Visible = Not Me.Discharge_Meds_2 Like " mg*"
...

Alternative:

For x = 1 to 11
Me("Discharge_Meds_" & x).Visible = Not Me("Discharge_Meds_" & x) Like " mg*"
Next

Or try Minty's expression in textbox and eliminate VBA.
=[D Med 1] + " " + [D Med 1 Dose] + "mg" + " " + [D Med 1 Freq]

Ajax's one liner worked perfectly. Minty's option left the text of 'mg' visible.

Thank you to everyone who offered help on this one gents.

[June7]

I tested Minty's expression and "mg" is not displayed if any field is null. So, your fields must contain empty string if there is no value which would cause "mg" to display.

[Minty]

I tested Minty's expression and "mg" is not displayed if any field is null. So, your fields must contain empty string if there is no value which would cause "mg" to display.

Thanks June - I assumed that was the why there was the spare "mg" in the sample screen print. Rogue data.