Hiding Text Box and Label on Current Event

[sgtclark]

I am tryingto use the On Current Event to hide a text box and label using the expressionbelow:



=IIf([ATStatus]= "Conducting Less Than 15 Days of AT (Remarks Required with total# days to be utilized)",Unit Reported # Days to beUsed].[Visible]=True,[Unit Reported # Days to be Used].[Visible]=False)

Am I usingthe wrong method to hide the text box if the other text box does not containthe specific text or is there a problem with my IIf statement?

[mike60smart]

Hi

This section is wrong:-

Unit Reported # Days to beUsed].[Visible]=True,

It should be:-

[Unit Reported # Days to beUsed].[Visible]=True,

[kd2017]

For the record it's best not to use spaces or special characters for object names, objects being text boxes, labels, form names, tables, queries, fields, etc. Only use spaces and special characters in labels and/or for presentation purposes.

I would use the on current event and a VBA routine. It would simply be something like:

if Me.atstatus = "..." then
Me.textbox_name.visible = false
Else
Me.textbox_name.visible = true
End if

[sgtclark]

I corrected that, thank you. However it still does not do the trick.

[kd2017]

I'm not sure that you can make those property changes from an Iif expression. Give it a try from a VBA sub like I posted in #3, that should do the trick. You'll delete the whole expression from the OnCurrent event properties, then click the '...' button and choose 'Code Builder'

As long as the label is linked to the textbox it should automatically show/hide with the textbox.

[sgtclark]

I have not done much in VBA and am still fumbling with it This I what I tried....



Option Compare Database


If Me.[AT Status] = "Conducting Less Than 15 Days of AT (Remarks Required with total # days to be utilized" Then
Me.textbox_ [Unit Reported # Days to be Used].Visible = True
Else
Me.textbox_ [Unit Reported # Days to be Used].Visible = False
End If
Private Sub Form_Current()
End Sub


AND



Option Compare Database
Private Sub Form_Current()
If Me.[AT Status] = "Conducting Less Than 15 Days of AT (Remarks Required with total # days to be utilized" Then
Me.textbox_ [Unit Reported # Days to be Used].Visible = True
Else
Me.textbox_ [Unit Reported # Days to be Used].Visible = False
End If
End Sub

[kd2017]

That's a good start. The second version is almost right. Try this, delete the textbox_ part so it looks like this Me.[Unit Reported # Days to be Used].Visible = False

[CJ_London]

or slightly more simple

Option Compare Database

Private Sub Form_Current()

        [Unit Reported # Days to be Used].Visible = Me.[AT Status] = "Conducting Less Than 15 Days of AT (Remarks Required with total # days to be utilized"

End Sub

I note in your other thread that you had

[ATStatus]="Conducting Less Than 15 Days of AT (Remarks Required with total #days to be utilized)"

looks to me like you have a typo with missing spaces and brackets

you might want to consider have a table with a PK and description for your status's

[sgtclark]

This worked and I added the same using the label name in the same code.

[sgtclark]

While this did work to make it invisible it throws a debug error when [AT Status] Is Null. What is the added code to fix that?

[kd2017]

if isnull( Me.[AT Status] ) then
   'do something

elseif Me.[AT Status] = "..." then
     'show text box code here

else
     'hide text box code here

end if

[sgtclark]

When I try it that way it tells me there is an error with this part "Private Sub Form_Current()"


Private Sub Form_Current()

If IsNull(Me.[AT Status]) Then
Me.[Unit Reported # Days to be Used].Visible = False
ElseIf Me.[AT Status] = "Conducting Less Than 15 Days of AT (Remarks Required with total # days to be utilized)" Then
Me.[Unit Reported # Days to be Used].Visible = True
Else

Me.[Unit Reported # Days to be Used].Visible = False
End If

[CJ_London]

when [AT Status] Is Null

Option Compare Database

Private Sub Form_Current()

        [Unit Reported # Days to be Used].Visible = nz(Me.[AT Status],"") = "Conducting Less Than 15 Days of AT (Remarks Required with total # days to be utilized"

End Sub

[sgtclark]

Thank you, worked like a charm. I did not realize that the Nz function worked with none numeric values. Thanks for the education.

[CJ_London]

nz works for nulls - strings are often zero length ("") which are not null so nz(Me.[AT Status],"") works for both scenarios