none
Hide and show field if checkbox is ticked. RRS feed

  • Question

  • In my database, Agents, Employees, Customers, Creditors etc all have addresses (postal and street) of course. After the street address is entered there is a question, " Is the postal address the same?" then tick the check box. I would like the normal Postal address fields to be hidden on the form, if the box is checked. On some forum I found someone suggesting years ago the following.

    • Private Sub ChkPostal_AfterUpdate()
    • If Me.ChkPostal = False Then
    • Me.POBoxNo.Visble = True
    • Else
    • Me.POBoxNo.Visible = False
    • End If
    • End Sub

    I show only POBoxNo in this example. It works. When I tick the check box, the fields are not visible. My problem is when I save it and open the form again, I have to tick it again. I would like it if I open the form and the box was checked the fields should not be visible. It also seem that if for one record I check the box, the next record hide those fields even when the box is not checked. If I check and uncheck the box it works again, but when I exit and open again, it is all messed up again.

    When the box is checked, meaning Postal address is the same as street address, in the forms the fields should be hidden for Postal address. I would like the same on the report. 

    Saturday, April 16, 2016 12:33 AM

Answers

  • Two more steps needed:1. You need to save the checkbox value to a new YesNo field in your table. So add the field, then set your checkbox' ControlSource to that field name.

    2. Move the AfterUpdate code to a new private sub, say ShowOrHidePostalAddressFields. Then call that procedure from ChkPostal_AfterUpdate and from Form_Current.

    By the way, nothing wrong with your code, but a bit verbose. You could have written this one-liner:

    Me.POBoxNo.Visible = Not Me.ChkPostal


    -Tom. Microsoft Access MVP

    Saturday, April 16, 2016 1:39 AM
  • Hi Hans van Niekerk,

    to set a value in check box you can use following syntax.

    Checkbox11.DefaultValue = False

    or

    Me.chkBox.Value = 1
    or Me.chkBox.Value = 0

    or you can also set the default value using interface. see below.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, April 26, 2016 6:17 AM
    Moderator

All replies

  • Two more steps needed:1. You need to save the checkbox value to a new YesNo field in your table. So add the field, then set your checkbox' ControlSource to that field name.

    2. Move the AfterUpdate code to a new private sub, say ShowOrHidePostalAddressFields. Then call that procedure from ChkPostal_AfterUpdate and from Form_Current.

    By the way, nothing wrong with your code, but a bit verbose. You could have written this one-liner:

    Me.POBoxNo.Visible = Not Me.ChkPostal


    -Tom. Microsoft Access MVP

    Saturday, April 16, 2016 1:39 AM
  • Thank you Tom. It works.
    Saturday, April 16, 2016 10:00 AM
  • Hi.

    I would like the same to happen on the report. The form works with the expression you suggested so far. My form and report is designed the same way and I named the fields the same. Probably the choice of event and procedure??

    Thursday, April 21, 2016 9:36 AM
  • On a report you show and hide in the Report_Open event or in the Detail_OnFormat.

    -Tom. Microsoft Access MVP

    Thursday, April 21, 2016 2:21 PM
  • I am sorry if I misunderstood. Under detail_onformat I can hide objects manually. My need is to hide the fields on the report if the checkbox is checked in the form. When not checked the field should be visible. Under the events I don't find Report_Open choice or help me with my dumb moment. 

    I don't want to show and hide manually. The form is working exactly correct after your advice. The report should automatic hide the named fields when checkbox is checked and display when not checked.

    Friday, April 22, 2016 2:36 PM
  • You added a new field to the table to store the value of ChkPostal. This field (let's call it IsPostal) should also be in the report query.

    Then in the report's Detail_OnFormat property write something like:
    Me.POBoxNo.Visible = Not Me.IsPostal


    -Tom. Microsoft Access MVP

    Saturday, April 23, 2016 2:17 AM
  • I only saw later. On the form. As soon as I call the procedure from Form_Current it works as far as show and hide of fields. When I then want to add a new record the message box comes up with; "Run-time error '94', Invalid use of null". I could click end and just continue adding the new record. But I would like the error not to show.
    Sunday, April 24, 2016 2:17 AM
  • Debug this. Is the value of IsPostal null? That should not be the case.

    -Tom. Microsoft Access MVP

    Sunday, April 24, 2016 2:35 AM
  • IsPostal is a checkbox and not checked when I open a new record? Does it mean it has a null value? The other six fields that makes up Postal address are all null because I still want to create that record.  How do I fix it?
    Sunday, April 24, 2016 5:47 AM
  • Hi Hans van Niekerk,

    if you don't want null value then you can set a default value .

    setting the default value can solve your null value error.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, April 25, 2016 9:35 AM
    Moderator
  • Please indicate exactly how I set a default value. I have an idea and my dbase is doing very well after 5 months but I do not know exactly how to do it.

    Thank you.

    Monday, April 25, 2016 11:34 AM
  • Monday, April 25, 2016 11:44 AM
  • Hi Hans van Niekerk,

    to set a value in check box you can use following syntax.

    Checkbox11.DefaultValue = False

    or

    Me.chkBox.Value = 1
    or Me.chkBox.Value = 0

    or you can also set the default value using interface. see below.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, April 26, 2016 6:17 AM
    Moderator
  • Thanks. Simple. Many hours of training and commitment in the past 8 months. Just haven't come across it yet. 
    Tuesday, April 26, 2016 10:45 AM