MS ACCESS: How to enable control in a form based on another control

[EORTIZ]

I want to know how I can enable (activate) a control in a form if another control is filled. For example, I have a questionnaire and I want to skip questions depending on the answer of a previous question.

[Stewart Ross]

I want to know how I can enable (activate) a control in a form if another control is filled. For example, I have a questionnaire and I want to skip questions depending on the answer of a previous question.

In the After Update event of the control that you want filled you can use VBA code to to un-enable controls you do not wish the user to enter, as follows:

 Dim IsFilled as Boolean
IsFilled = Not IsNull(Me.NameOfControl)
Me.FirstSkippedControl.Enabled = Not IsFilled
Me.FirstSkippedControl.Locked = IsFilled
Me.SecondSkippedControl.Enabled = Not IsFilled
Me.SecondSkippedControl.Locked = IsFilled
....

It would be best to store this in the form's code module as a separate Sub which could be called from the original control's AfterUpdate event. If you do go this route you also have to call the same routine from the form's On Current event, to ensure that the right pattern of enabled/unenabled controls is shown for each record subsequently scrolled by the user.

-Stewart

[EORTIZ]

Hi Stewart, thanks a lot for the reply.

I am in the very beginner stage of using programming so I need a little moe help. Let's say that I have an option group control called Q1, and a second option group control called Q2. I need that Q2 be disabled until selected in Q1 the value of 1. Otherwise need to be disabled.

Another thing that I need to do is to be able to clear the option group controlQ1 for the case that it is filled incorrectly and should be left blank. In that case Q2 need to be disabled and its value deleted.

[Stewart Ross]

Hi Stewart, thanks a lot for the reply.

I am in the very beginner stage of using programming so I need a little moe help. Let's say that I have an option group control called Q1, and a second option group control called Q2. I need that Q2 be disabled until selected in Q1 the value of 1. Otherwise need to be disabled.

Another thing that I need to do is to be able to clear the option group controlQ1 for the case that it is filled incorrectly and should be left blank. In that case Q2 need to be disabled and its value deleted.

Hi Eortiz. I'm not clear that you can choose 'incorrect' values in an option group - the range of values is restricted to those you define when you create the options. Anyway, to answer your question, you could use a Select Case construct in the AfterUpdate event of control Q1. I can only offer a guide as to how to construct it:

Select Case Me.Q1
   Case 1
       Me.Q2.Enabled=True
       Me.Q2.Locked = False
   Case 2
   (add suitable code for other correct values if needed)
   Case 3
   (add suitable code for other correct values if needed)
   Case else
      Q1 = Null
      Q2 = Null
      Q2.Enabled = False
      Q2.Locked = True
End Select

Whilst I have included the enabling of Q2 in the Case statement I reckon this would be better separated out as a separate test. I can't advise further on this as you don't give any details of what other conditions may also be valid for the Q1/Q2 controls.

You will also need to refer to this code in the On Current event of your user form, which means it is best to write it as a separate subroutine that is itself called from the Q1 after update and form on current events.

-Stewart

[EORTIZ]

Thanks for your response, I appreciate the timeliness of it.


It is possible than the person doing data entry by mistake select an option and there was not a response for a particular question, so need to uncheck the control. If selecting option1 from the control, the second control will be enabled, and if it is changed to option2 in the first control, the second control need to be cleared and disabled.

I had found out how to do it:

In the after update of control 1:

If.Me.optgroup.value=1 then 
  me.optgroup2.enabled=true
else me.optgroup2.enabled=false
       me.optgroup2=null
end if

to only uncheck an option in the option group control, in the Double Click event:

Private Sub optgroup_DblClick(Cancel As Integer)
Me.optgroup = Null
End Sub

[NeoPa]

All you need if you're simply setting the visibility is :

Me.optGroup2.Visible = (Me.optGroup = 1)

This automatically sets it visible if Me.optGroup = 1, and sets it invisible if Me.optGroup <> 1.