Hiding a Text Box depending on whether another field is empty or not

[LadyScot101]

I'm really new. I'm working in Access 2010. I did the following and my [Probation Ends:] is always invisible now. I have a Label Box that I changed to a Text Box. I set the Format for Visible to Yes and Can Shrink to Yes. The Control Source is [Probation Ends:] and that is the Name also. I added an Event as follows:

Private Sub Probation_Ends__Click()

    If IsNull(Me![Probation End Date]) Then
        Me![Probation Ends:].Visible = False
    Else
        Me![Probation Ends:].Visible = True
    End If
    
End Sub

If the [Probation End Date] is null, then I want the [Probation Ends:] box to be invisible. What do you think is the problem?

[Seth Schrock]

Try putting your code in the form's OnCurrent event.

[LadyScot101]

This is a report. I don't see OnCurrent...

[Seth Schrock]

Change the name of your textbox to be different than it Control Source. Then put your code (with the new control name) in the Detail section's OnFormat event.

[LadyScot101]

I changed the name to PE and I moved the event to "On Enter", but it still didn't work. I didn't see an OnFormat event on the Event tab. Here is the changed event...

Private Sub PE_Enter()

    If (Me![Probation End Date]) = "" Then
        Me!PE.Visible = True
    Else
        Me!PE.Visible = False
    End If
    
End Sub

[LadyScot101]

I had also changed the IsNull out and put = "". Didn't work...

[Seth Schrock]

You are looking the control's event list, not the report's detail section (or whatever section your textbox is in). Click on an empty space beside your textbox and then look at your event list. Also, you need to change the reference in your code to be Me.PE not Me.[Probation End Date].

[LadyScot101]

I found it. I don't understand why all of the controls would be pointed to PE? It is only to print if the Probation End Date is empty.

[LadyScot101]

Got it to work!!! Yea!! I don't use a VBA code. I just put the following in the Control Source line and use the Control Source Names, not the Names of the fields.

=IIf([PROBATION END DATE] Is Null,Null,"PROBATION ENDS:")

[Seth Schrock]

That doesn't hide the control, it just makes it so that there isn't anything in it. But if that is all you need then great.

[NeoPa]

I've changed the post selected as Best Answer for a number of reasons. Not least of which is that it actually answers the question posed - rather than simply providing a way round the issue.

[NeoPa]

As a tip, you can also simplify the code greatly :

Private Sub Detail_Format()
    Me.[Probation Ends:].Visible = Not IsNull(Me.[Probation End Date])
End Sub

[LadyScot101]

I tried adding the code and it wouldn't work. Seth's didn't work either.

[Seth Schrock]

Based on the code that you provided, you didn't try exactly what I had said as it wasn't in the report detail section's OnFormat event.

[NeoPa]

LadyScot:
Seth's didn't work either.

I can confirm that Seth's suggestion itself does work. I can't say why it was that it didn't work for you as I can't see exactly what it was that you tried. It does seem clear, however, that it wasn't exactly as he suggested.

There are a number of reasons why it might not work for you - from all VBA code being disabled due to security restrictions to your placing the code in the wrong container (module), or even not setting the event property up correctly to access the code.