Change Display Textbox Based On Another Report Field Value

[sherlocked]

Hello experts,

I'm trying to set an unbound text box to display certain text based on whether a checkbox on another open report is true or false.

Below is what I'm trying but I get a "you cannot assign a value to this object" error.

Any idea what I'm doing wrong?

Thanks in advance

Private Sub Report_Open(Cancel As Integer)

If Report_rptNational.chkID = True Then
               
Me.txtDisplayLabel = "Application Details Report - Identity Cases for the year " & Forms!frmMain.cmboYear
                
End If

End Sub

 

[Frothingslosh]

Reports are read-only. You cannot actively change a value on a report once it has been generated; instead, you'll need to implement the change as part of either the data source for the form or the control source for the control.

For this, you'll need to turn that label into a text box. Once that's done, you can assign its contents either through a function call or an IIF statement.

You'll also need to check that value against a form or a stored variable or a TempVar instead of another report - I'm sure I'll be corrected if I'm wrong, but I'm pretty sure you can't look up a value on a report like that.

 

[Cronk]

Put the code in the OnFormat event of the section where the text box is located. I'm not sure what your report is named. If it is rptNational, then the code should be as follows.
If Reports!rptNational!chkID = True then

 

[sherlocked]

Can you expand on what this IIF statement might look like?

Thank you

 

[Frothingslosh]

IIF(logical test, Action If True, Action If False)

https://support.office.com/en-us/article/IIf-Function-32436ecf-c629-48a3-9900-647539c764e3

 

[sherlocked]

I also tried the below, but it did not work either

Private Sub GetLabel()
Dim varLabel As String

If Report_rptNational.chkID = True Then
varLabel = "Application Details Report - Identity Cases for the year " & Form_frmMain.cmboYear
End If
End Sub

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
GetLabel
End Sub

 

[sherlocked]

Thanks, but I have about 40 different conditions that require a different label for each. I don't think the IIF will work

 

[Frothingslosh]

Don't forget Cronk's advice - you should be using ! instead of .

Also don't forget that labels don't have values; textboxes do, so you need to replace the label with a text box.

Subs cannot return values; GetLabel needs to be a function, instead, and then you need to set GetLabel's value to equal varLabel before you exit the function.

Also, right now, you'll be returning an empty string if chkID is false. Is this what you want?

Finally, in your second sub, GetLabel by itself does nothing. You'll need to access the control's data source or the label's caption and set that to equal GetLabel().

Sorry I can't be more specific, but I'm off to a couple meetings.

 

[sherlocked]

This was perfectly helpful. Wound up going with the below, works like a charm.

Thank you

Private Function GetLabel()
Dim varLabel As String

If Report_rptNational.chkID = True Then
varLabel = "Application Details Report - Identity Cases for the year " & Form_frmMain.cmboYear
GetLabel = varLabel
End If
End Function