Check-box to hide/show input fields

[RapidFireGT]

I’m trying to use a check-box to show/hide several input fields on a form. The names of the objects are:

Check-box: CustomerCheckBox
Input 1 field: customer_first_name
Input 2 field: customer_last_name
Input 3 field: customer_phone

*EDIT* This check-box is unbound. It does not store data in a table or have any purpose/meaning other than to simply hide/show fields in the form.

According to my Google searching, I have the proper code and I’ve tried entering it into the check-box’s events After Update and On Click, and I’ve also tried adding the code to the form’s On Current event. Here is the code I’ve used in all 3 places (it’s the same code repeated):

Private Sub CustomerCheckBox_AfterUpdate()


  Me.customer_first_name.Visible = (Me.CustomerCheckBox = True)
  Me.customer_last_name.Visible = (Me.CustomerCheckBox = True)
  Me.customer_phone.Visible = (Me.CustomerCheckBox = True)


  End Sub

Private Sub CustomerCheckBox_Click()


  Me.customer_first_name.Visible = (Me.CustomerCheckBox = True)
  Me.customer_last_name.Visible = (Me.CustomerCheckBox = True)
  Me.customer_phone.Visible = (Me.CustomerCheckBox = True)


  End Sub

Private Sub Form_Current()


  Me.customer_first_name.Visible = (Me.CustomerCheckBox = True)
  Me.customer_last_name.Visible = (Me.CustomerCheckBox = True)
  Me.customer_phone.Visible = (Me.CustomerCheckBox = True)


  End Sub

Strange thing is, this code worked just fine when I created a brand new blank database and tried using a check-box to show/hide a label on a form.

Private Sub Check1_Click()

Me.Label0.Visible = (Me.Check1)

End Sub

Any ideas why it’s not working in my populated database/form?

 

[bob fitz]

Have you tried:

Me.customer_first_name.Visible = Me.CustomerCheckBox
Me.customer_last_name.Visible = Me.CustomerCheckBox
Me.customer_phone.Visible = Me.CustomerCheckBox

in the OnCurrent event of the form and in the AfterUpdate event of "CustomerCheckBox" control.
The "CustomerCheckBox" would need to be bound to a field in the form's table/query.

 

[vbaInet]

Any ideas why it’s not working in my populated database/form?

By "populated database/form" do you mean the form is in a Continuous Form or in Datasheet View? If it is then your code won't work.

 

[RapidFireGT]

Have you tried:

Me.customer_first_name.Visible = Me.CustomerCheckBox
Me.customer_last_name.Visible = Me.CustomerCheckBox
Me.customer_phone.Visible = Me.CustomerCheckBox

in the OnCurrent event of the form and in the AfterUpdate event of "CustomerCheckBox" control.
The "CustomerCheckBox" would need to be bound to a field in the form's table/query.

That code did not work either. Also, this check-box is unbound. It does not store data in a table or have any purpose/meaning other than to simply hide/show fields in the form.

By "populated database/form" do you mean the form is in a Continuous Form or in Datasheet View? If it is then your code won't work.

By populated database/form, I mean that I am building this form within a database that is already populated with a good amount of sample records. The form itself, however, is simply showing one record at a time. It is a form where a customer would input data pertaining to a new customer (name, address, phone, etc.) and then click Submit to enter the data into the database.

Thanks guys for the quick responses. Any other ideas?

 

[vbaInet]

That code did not work either. Also, this check-box is unbound.

If it's unbound it will only work on the After Update or Click event of the checkbox. When you move between records, it will carry over the last value.

 

[missinglinq]

Me.customer_first_name.Visible = Me.CustomerCheckBox
Me.customer_last_name.Visible = Me.CustomerCheckBox
Me.customer_phone.Visible = Me.CustomerCheckBox

Bob's code fails because it doesn't take into account the fact that the Visible Property can only be True or False, and a Checkbox initially (as on a New Record) is Null! You'd have to use the Nz() Function, as discussed in Post # 6 at

http://www.utteraccess.com/forum/Check-box-Hide-show-Inpu-t1978264.html

to cover this. And I really can't imagine a scenario for this where it would be appropriate for the Checkbox to not be Bound, unless the entire Form is Unbound, and only one Record is ever entered in a given session.

Linq ;0)>

 

[vbaInet]

Bob's code fails because it doesn't take into account the fact that the Visible Property can only be True or False, and a Checkbox initially (as on a New Record) is Null! You'd have to use the Nz() Function,

That is if the checkbox is unbound or the checkbox is bound to a data type other than the Yes/No data type. If it's bound to the Yes/No data type, it's never Null.

Also, if the Tri State property of the checkbox is set to Yes (i.e. post Access 2003), you can set the checkbox to Null.

 

[missinglinq]

...If it's bound to the Yes/No data type, it's never Null.

It certainly is in my v2003! With the Checkbox Bound to a Yes/No Field, placing a Command Button on the Form, with the code

MsgBox IsNull(Me.CustomerCheckBox)

returns False if the Checkbox is ticked, or ticked and then unticked, but returns True on a new Record when the Control hasn't been diddled at all.

Linq ;0)>

 

[vbaInet]

My bad! You are quite right missinglinq. I was thinking about something else and I didn't pick up on the fact that you mentioned about New Records.

 

[missinglinq]

No problem! Just throws me for a loop when someone I know and trusts makes a statement I know is erroneous!

Some years back, when about half the posters here were running 2003 and half earlier versions, a question was asked about dynamically adding items to a Combobox. Three or four of us went round and round for hours about whether or not you could use AddIem to do this, until finally someone who'd just gone from 2000 to 2003 chimed in to say that in pre-2003 AddIem only applied to built-in Menu Items, but with 2003 you could, in fact, use it for Comboboxes!

Have a good day!

Linq ;0)>

 

[vbaInet]

We all have our good and bad days I've got a copies of Access from 2000 up until 2010 but these days I hardly ever use anything other than 2007 because the older versions are installed an old machines. As for 2010, well that's a different story - no time to convert.

I remember using 97 too but not extensively.

 

[RapidFireGT]

This issue has been resolved, thanks to Linq on another forum. Here is the post:

The correct events for this are the AfterUpdate event of the Checkbox and the OnCurrent event of the Form. The trouble with

Me.customer_first_name.Visible = (Me.CustomerCheckBox = True)

is that it doesn't take into account the fact that the Checkbox is Null before it is ticked or unticked, and will pop an error.

Me.customer_first_name.Visible = Nz(Me.CustomerCheckBox.Value, True)

doesn't work, either, because if Me.CustomerCheckBox.Value is Null (as it is when a New Record is invoked) it assigns the Value of True, making the Textbox Visible, not the intent of the OP.

Also, because the last Value entered in an Unbound Control is the Current Value for that Control on all Records, the Checkbox simply has to be Bound to a Field in the underlying Table! There is no way around this!

This kind of code covers all contingencies:

Private Sub CustomerCheckBox_AfterUpdate()
  Me.customer_first_name.Visible = Nz(Me.CustomerCheckBox = True, False)
End Sub

Private Sub Form_Current()
  Me.customer_first_name.Visible = Nz(Me.CustomerCheckBox = True, False)
End Sub

If it doesn't work, you need to double-check your Controls names for spelling errors.

Linq ;0)>

Thank you. This code was not originally working, but that think that is because of the way I had my form set up (I was having trouble pulling in fields from 3 different tables on one form). After creating my main for and adding the other fields using two sub-forms, and adding the Check boxes/macros to each respective form, it is all working now.

Also, if it helps anyone else in the future: my check boxes did NOT have to be bound for this to function properly.

 

[ma t]

Thank you to everyone who contributed to this solution, it was exactly what I needed!

 

[missinglinq]

Glad we could help!

BTW, this is why accurate, informative thread titles are so important! It allows people searching, in this case, 18 months later, to get the help they need!

Linq ;0)>