none
Yes/No Questions

    Question

  • I am trying to set up an input form in Access that includes both Yes/No Questions and Number Inputs. I would like the 3 number inputs only to appear on the form if the yes box is checked. Additionally, all of this data has to go into one master sheet that has all the data from every instance the form is filled out. If there is no data in the number fields because the "No" box was checked, then they can remain empty. What's the best way to go about doing this?

    Sorry if this is a really obvious question, I only recently began to work with Access.

    Tuesday, July 02, 2013 6:01 PM

Answers

  • You have to do this in two places.  Firstly in the form's Current event procedure so that the text boxes are shown/hidden on the basis of the value of the check box in the current record:

         Me.txtOne.Visible = Nz(Me.chkOne, False)
         Me.txtTwo.Visible = Nz(Me.chkTwo, False)
         Me.txtThree.Visible = Nz(Me.chkThree, False)

    Note that the Nz function needs to be called here as when the form is moved to an empty new record the check box will be Null until the user begins to enter data.  Consequently an error would occur if the code tried to set the Visible property to Null.

    Secondly in the AfterUpdate event procedure of each check box to show/hide the relevant control when the user updates the check box.  However, if the value of the check box is updated from True to False you will probably also want to make the control Null (or zero if Nulls are disallowed) to remove any value which might have been entered while the controls were visible:

        If Me.chkOne Then
            Me.txtOne.Visible = True
        Else
            Me.txtOne.Visible = False
            Me.txtOne = Null
        End If

    Do the same in the AfterUpdate event procedures of the other two check boxes.

    One thing to note is that this really only works satisfactorily in single form view.  In continuous forms view the text boxes would be shown/hidden in all rows, not just the current row.  In continuous forms view it's better to lock the text box controls rather than hiding them.  

    Ken Sheridan, Stafford, England

    • Marked as answer by HarryHarvey Tuesday, July 09, 2013 6:16 PM
    Tuesday, July 02, 2013 10:56 PM

All replies

  • You can use the AfterUpdate event of the checkbox to set the Visible property of each number box. This can be done with a macro or code.

    Code example:

    Private Sub MyCheckBox_AfterUpdate()
        Me.MyNumberBox.Visible = (Me.MyCheckBox = True)
    End Sub


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Tuesday, July 02, 2013 6:10 PM
  • You have to do this in two places.  Firstly in the form's Current event procedure so that the text boxes are shown/hidden on the basis of the value of the check box in the current record:

         Me.txtOne.Visible = Nz(Me.chkOne, False)
         Me.txtTwo.Visible = Nz(Me.chkTwo, False)
         Me.txtThree.Visible = Nz(Me.chkThree, False)

    Note that the Nz function needs to be called here as when the form is moved to an empty new record the check box will be Null until the user begins to enter data.  Consequently an error would occur if the code tried to set the Visible property to Null.

    Secondly in the AfterUpdate event procedure of each check box to show/hide the relevant control when the user updates the check box.  However, if the value of the check box is updated from True to False you will probably also want to make the control Null (or zero if Nulls are disallowed) to remove any value which might have been entered while the controls were visible:

        If Me.chkOne Then
            Me.txtOne.Visible = True
        Else
            Me.txtOne.Visible = False
            Me.txtOne = Null
        End If

    Do the same in the AfterUpdate event procedures of the other two check boxes.

    One thing to note is that this really only works satisfactorily in single form view.  In continuous forms view the text boxes would be shown/hidden in all rows, not just the current row.  In continuous forms view it's better to lock the text box controls rather than hiding them.  

    Ken Sheridan, Stafford, England

    • Marked as answer by HarryHarvey Tuesday, July 09, 2013 6:16 PM
    Tuesday, July 02, 2013 10:56 PM
  • Thanks Ken, the code you gave me worked, though in a rather odd way. On my computer, the boxes are never hidden. But if I save the file and email it to someone else who opens it on another computer, the boxes are hidden as desired. I have no idea why that happens, but it works.

    I did have one additional problem I was unable to solve. When the user checks the box, the questions appear as desired. However, if they attempt to uncheck the box, Access spits out a runtime error saying that the 'Recordset is not Updateable'. Any idea on how to fix that issue?

    Tuesday, July 09, 2013 6:22 PM
  • I don't know why it should work on one machine but not another.

    As regards the other issue, at first sight I'd have assumed that the form is based on a non-updatable query, and the check boxes are unbound, so when the code tries to assign a Null to the column to which the text box is bound, this results in the error.  However, your original post suggests that the check boxes are in fact bound controls, and I'm assuming that you are able to set them to True (checked), which would seem to rule out the form's recordset not being updatable.

    Try commenting out the 'Me.txtOne = Null' line, and its equivalents and see what happens.

    Ken Sheridan, Stafford, England

    Tuesday, July 09, 2013 8:23 PM