Make field Invisible/Visable

I am trying to make a field visible (EmpEndDate) if the EmployeeStatus field
is Inactive. I would like it to be invisible if the field is active or null.
I have read other posts but still need some assistance. I have the below
code so far but it is not working quite right. When I change one record,
they all change so I dont know if I need to incude the ID (AutoNumber) in the
code?

Private Sub EmployeeStatus_AfterUpdate()
If Me.EmployeeStatus = "Inactive" Then
Me.EmpEndDate.Visible = True
Else
Me.EmployeeStatus = "Active"
Me.EmpEndDate.Visible = False
End If
End Sub

Dee wrote:
I am trying to make a field visible (EmpEndDate) if the EmployeeStatus field
is Inactive. I would like it to be invisible if the field is active or null.
I have read other posts but still need some assistance. I have the below
code so far but it is not working quite right. When I change one record,
they all change so I dont know if I need to incude the ID (AutoNumber) in the
code?

Private Sub EmployeeStatus_AfterUpdate()
If Me.EmployeeStatus = "Inactive" Then
Me.EmpEndDate.Visible = True
Else
Me.EmployeeStatus = "Active"
Me.EmpEndDate.Visible = False
End If
End Sub

you need to reset it in the OnCurrent event of the form.

Me.EmpEndDate.Visible = Not (Me.EmployeeStatus = "Inactive")

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201004/1

It sounds like you are working with a continuous form, in which
case doing this through code - as you've discovered - is not going
to work. That's because a continuous form is really just one row
of controls repeated over and over with different data, so any
change you make to one row (using code) affects all rows.

The way to get around this is to use Conditional Formatting
(in design view go to Format/Conditional Formatting on the menu).
However, I don't think you can modify the actual visibility of a control
from here but you could set both the fore color and back color of
the control to be the same as the back color of the form so it would
blend in and appear as though it were not there (you may also
need to change the border color of the control as well in the
control's properties).

So you would select the EmpEndDate text box, go to Conditional
Formatting and use something like;

Expression Is [EmployeeStatus] "Inactive"

Then set the fore and back colors accordingly.

--
_________

Sean Bailey


"Dee" wrote:

I am trying to make a field visible (EmpEndDate) if the EmployeeStatus field
is Inactive. I would like it to be invisible if the field is active or null.
I have read other posts but still need some assistance. I have the below
code so far but it is not working quite right. When I change one record,
they all change so I dont know if I need to incude the ID (AutoNumber) in the
code?

Private Sub EmployeeStatus_AfterUpdate()
If Me.EmployeeStatus = "Inactive" Then
Me.EmpEndDate.Visible = True
Else
Me.EmployeeStatus = "Active"
Me.EmpEndDate.Visible = False
End If
End Sub

If you are using a form in single form view then you also need to set the
Visible property of the control in the form's Current event procedure. You
can use the same code, but you can in fact simplify it to a single line as
you are not assigning a value to the EmployeeStatus field in this case:

Me.EmpEndDate.Visible = (Nz(Me.EmployeeStatus,"") = "Inactive")

If you are using a form in continuous form's view, however, each instance of
the EmpEndDate field will be shown or hidden, not just the current one; you
are really seeing the same control multiple times. In this situation you can
hide the value in the contol by means of conditional formatting by setting
the ForeColor property of the control to the same colour as its BackColor
property. In the conditional formatting dialogue for the EmpEndDate control
do this on the basis of the expression:

Nz([EmployeeStatus],"") = "Active"

If you do this I'd suggest that you also enable/disable the control in both
the EmployeeStatus control's AfterUpdate event procedure and the form's
Current event procedure. Otherwise a user would still be able to enter data
in the EmpEndDate control when its value is 'hidden', but would not see what
they are entering. The code for this would be:

Me.EmpEndDate.Enabled = (Nz(Me.EmployeeStatus,"") = "Inactive")

Note the use of the Nz function in the above expressions. This is because a
Null is neither equal nor unequal to anything, not even to Null. By using
the Nz function to return a zero-length string however, comparison of the
string values becomes possible.

Ken Sheridan
Stafford, England

Dee wrote:
I am trying to make a field visible (EmpEndDate) if the EmployeeStatus field
is Inactive. I would like it to be invisible if the field is active or null.
I have read other posts but still need some assistance. I have the below
code so far but it is not working quite right. When I change one record,
they all change so I dont know if I need to incude the ID (AutoNumber) in the
code?

Private Sub EmployeeStatus_AfterUpdate()
If Me.EmployeeStatus = "Inactive" Then
Me.EmpEndDate.Visible = True
Else
Me.EmployeeStatus = "Active"
Me.EmpEndDate.Visible = False
End If
End Sub

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201004/1

On second thoughts the conditional formatting expression should be:

Nz([EmployeeStatus],"Active") = "Active"

In addition to enabling/disabling the control, I'd also lock/unlock it. This
keeps the normal appearance of the control rather than greying it out:

Me.EmpEndDate.Enabled = Nz(Me.EmployeeStatus,"") = "Inactive"
Me.EmpEndDate.Locked = Nz(Me.EmployeeStatus,"Active") = "Active"

Ken Sheridan
Stafford, England

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201004/1

Thank you, this works perfectly. (I am using a single form).

"KenSheridan via AccessMonster.com" wrote:

If you are using a form in single form view then you also need to set the
Visible property of the control in the form's Current event procedure. You
can use the same code, but you can in fact simplify it to a single line as
you are not assigning a value to the EmployeeStatus field in this case:

Me.EmpEndDate.Visible = (Nz(Me.EmployeeStatus,"") = "Inactive")

If you are using a form in continuous form's view, however, each instance of
the EmpEndDate field will be shown or hidden, not just the current one; you
are really seeing the same control multiple times. In this situation you can
hide the value in the contol by means of conditional formatting by setting
the ForeColor property of the control to the same colour as its BackColor
property. In the conditional formatting dialogue for the EmpEndDate control
do this on the basis of the expression:

Nz([EmployeeStatus],"") = "Active"

If you do this I'd suggest that you also enable/disable the control in both
the EmployeeStatus control's AfterUpdate event procedure and the form's
Current event procedure. Otherwise a user would still be able to enter data
in the EmpEndDate control when its value is 'hidden', but would not see what
they are entering. The code for this would be:

Me.EmpEndDate.Enabled = (Nz(Me.EmployeeStatus,"") = "Inactive")

Note the use of the Nz function in the above expressions. This is because a
Null is neither equal nor unequal to anything, not even to Null. By using
the Nz function to return a zero-length string however, comparison of the
string values becomes possible.

Ken Sheridan
Stafford, England

Dee wrote:
I am trying to make a field visible (EmpEndDate) if the EmployeeStatus field
is Inactive. I would like it to be invisible if the field is active or null.
I have read other posts but still need some assistance. I have the below
code so far but it is not working quite right. When I change one record,
they all change so I dont know if I need to incude the ID (AutoNumber) in the
code?

Private Sub EmployeeStatus_AfterUpdate()
If Me.EmployeeStatus = "Inactive" Then
Me.EmpEndDate.Visible = True
Else
Me.EmployeeStatus = "Active"
Me.EmpEndDate.Visible = False
End If
End Sub

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201004/1

.