Hide a field when it is empty 1

[Davide77]

Hallo,
I would like to hide the label of a field when the field it is empty.

I'm using this code:

If [ReportName]![FieldName] = "" Then
[ReportName]![LabelName].Visible = False
Else [ReportName]![LabelName].Visible = True
End If

But it doesn't work with "", and it's even worst with isNull. What should I write to refer to field (text field) in which the user didn't write anything?

thanks for helping

 

[PaulBricker]

Assuming the label is in the same section as the info, then it should be something like this

If IsNull([FieldName]) Or [FieldName] = "" Then
LabelName.Visible = False
Else
LableName.Visible = True
End If


Paul

 

[fredk]

I put the following code in the after update event of my text box which was named text38

If IsNull(Me.Text38) Or Me.Text38 = "" Then
Me.Label39.Visible = False
Else: Me.Label39.Visible = True
End If

This worked

Fred

 

[dhookom]

There is no After Update event in a report. If you want to make a label invisible if it's associated text box is null, you don't need code. Change the label to a text box and set the Control Source to:
="Spouse Name " + [SpouseName]
Set the width of the "label" text box to only show "Spouse Name" and don't allow it to grow. You need to substitute your field name for SpouseName.

Duane
MS Access MVP

 

[Davide77]

Thank paul, your solution worked

 

[AccessUser22]

Is this for an Access Report? I can't seem to manipulate the visible property in my Access reports for any control(line, text box, combo box, label). Is there something I need to have turned on in the references for this to work?

 

[dhookom]

How are you attempting to manipulate the properties? It would help if you would show us some code. One of the keys is to set the property values in the On Format event of the section containing the control(s).

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[AccessUser22]

Okay, it looks like I'm getting it to work now that I put it in the "On Format" event as you specified. However, now for some reason I'm getting two instances of my message box. Any ideas why it might be coming up twice?

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim strResponse As String

strResponse = MsgBox("Do you want to include the deputy commissioner signature?", vbYesNo, "User Input&quot

If strResponse = vbNo Then
Me.cboDeputyCommissioner.Visible = False
Me.txtDepCommApprTitle.Visible = False
Me.Label213.Visible = False
Me.Line123.Visible = False
Else
Me.cboDeputyCommissioner.Visible = True
Me.txtDepCommApprTitle.Visible = True
Me.Label213.Visible = True
Me.Line123.Visible = True
End If

End Sub

Thanks!

 

[dhookom]

I would declare/Dim a variable in the General Declarations section of the report module:

   Dim intIncludeDCS as Integer 'not string

Then in the On Open event of the report, use:

   Dim strMsg as String
   strMsg = "Do you want to include the deputy commissioner signature?"
   intIncludeDCS = MsgBox(strMsg, vbYesNo, "User Input")

Then in the section on format:

    Me.cboDeputyCommissioner.Visible = intIncludeDCS = vbYes
    Me.txtDepCommApprTitle.Visible = intIncludeDCS = vbYes
    Me.Label213.Visible = intIncludeDCS = vbYes
    Me.Line123.Visible = intIncludeDCS = vbYes

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[AccessUser22]

Excellent! Works like a charm. I'm new to using vba in reports, so I'm slowly getting used to when I should be using each event. I'm used to using vba in forms. Thanks!

 

[jbento]

I want to do something similar, but I can't get it to work, which is below:

If [rpt_Main]![field1] = "Working" Then
[rpt_Main]![memo_Label].Visible = False
[rpt_Main]![memo].Visible = False

End If

Is there a way to get the above to work?

Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!

 

[dhookom]

You would need to place the code in the section containing the controls

[rpt_Main]![memo_Label].Visible = Not [field1] = "Working"
[rpt_Main]![memo].Visible = Not [field1] = "Working"

Field1 must be a control on the report.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jbento]

dhookum,
I will try this at work today.

Thanks for responding.

Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!