conditional fields

[boglevee]

I've got a form that is basically like a survey. I've got a yes/no field that i'll call "condition1"

if someone does not check condition1, there are several other fields that don't apply to them. how can i make it so that these fields remain locked / invisible until condition1 is checked? Ideally these locked fields would be skipped over by tab order as well.

I know that I can use conditional formatting, but this doesn't exactly solve my problem. Thanks!

(I'm new to this forum and to access, so sorry if this question has already been posted)

[missinglinq]

Exactly how many fields are we talking about here? And do you want them invisible (which may make the form look unbalanced) or simply greyed out? (Go into Design View, select a textbox, goto Properties - Data and change Enabled to No. Run the form to see what I mean by this)

Welcome to TheScripts!

Linq ;0)>

[boglevee]

greyed out would be great. can i start with them disabled and then set up a macro to enable them if conditional1 is checked?

i think there are about 30 or so fields that i'd like to disable.

[damonreid]

Disabling them as discribed above is the frist step. The next thing to do is go to the onchange part of your yes/no tick box and enter some code to enable them once it is selected.

Private Sub TickBox_Click()
'Once TickBox chagnes check to see if it is true.
'If it is enable details, else disable details.
    If TickBox= True Then
        Me.Field1.Enabled = True
        Me.Field2.Enabled = True
.
.
.
        Me.Fieldx.Enabled = True
    End If
    If TickBox= False Then
        Me.Field1.Enabled = False
        Me.Field2.Enabled = False
.
.
.
        Me.FieldX.Enabled = False
    End If
End Sub

[boglevee]

great! that looks like it should solve my problem, thanks!

[missinglinq]

Damon's code has the right idea, but it requires you set the enabled/disabled property for each of your many textboxes, one at a time. It also won't set the enabled/disabled property correctly as you move from record to record. This needs to be done in the Form_Current event.

This may sound and look complicated at first, but it really isn't, especially compared with the alternative, which is to deal with the status of each and every one of your 30 textboxes separately! In order to do this you need to use the Tag Property of your textboxes to tell Access which ones to make enabled /greyed out depending on the status of Condition1.

Note: You have referred to your checkbox as Condition1 in one of your posts and Conditional1 in another! I went with Condition1 for this code; just make sure the the actual name of the checkbox matches the references in the code.

To set the Tag property of a textboxes:

Select all the textboxes you want to be affected by holding down <Shift> and clicking on each one.
Click on Properties
Click on Other

In the Tag Property box enter Cond1 (no quotation marks!)

While you’ve got them all selected, you can also set Enabled to No for all of them, so when the form opens they’ll start out disabled.

Now what this routine does, basically, is check to see whether Condition1 is checked or not, loop thru your textboxes, seeing if they have the Tag Property set to Cond1 and setting them to Enabled = True or Enabled = False, dependng on the status of the checkbox. If the user makes a mistake and has to change Condition1 , the textboxes will change accordingly.

Select the Condition1 checkbox
Right click and click on Properties
Click on Events
Click to the right of the box for the OnClick Property
Click on the ellipsis (...)
Click on Code Builder

You’re now in the VBA Editor! You should see this sub:

Private Sub Condition1_Click()

End Sub

Copy and paste this code between these two lines:

'This sets the textboxes to Enabled or not Enabled immediately

Dim ctrl As Control
 
If Condition1 Then  'If the box is checked
 For Each ctrl In Me.Controls
   If ctrl.ControlType = acTextBox Then
    If ctrl.Tag = "Cond1" Then
     ctrl.Enabled = True
    End If
   End If
 Next
 
Else   'If the box is not checked
 For Each ctrl In Me.Controls
   If ctrl.ControlType = acTextBox Then
    If ctrl.Tag = "Cond1" Then
     ctrl.Enabled = False
    End If
   End If
 Next
End If
 

While you’re in the VBA Editor, copy and paste this code also. It does the same check as the previous code, but it does it each time you move from one record to another, so that the Enabled status of each textbox is set properly.

Private Sub Form_Current()
Dim ctrl As Control
 
If Condition1 Then
 For Each ctrl In Me.Controls
   If ctrl.ControlType = acTextBox Then
    If ctrl.Tag = "Cond1" Then
     ctrl.Enabled = True
    End If
   End If
 Next
 
Else
 For Each ctrl In Me.Controls
   If ctrl.ControlType = acTextBox Then
    If ctrl.Tag = "Cond1" Then
     ctrl.Enabled = False
    End If
   End If
 Next
End If
End Sub
 

Click on the “Save” button and exit the Editor. Run the form and try the checkbox out.

Note: If the lines for the code appear when you copy and paste this into the code editor, be sure to delete them, along with the following period.

Good Luck!

Linq ;0)>

[boglevee]

thanks a lot linq, that sounds a lot easier than handling each of the thirty individually!

[missinglinq]

The Tag property is great for grouping controls so that you can do something to a bunch of them at the same time, such a the case here! The only real quirk that I like to remind people of is that the Tag property, which can be any string, has to be entered in the Property Box like

Tag1

without quotes, but referred to in code as

"Tag1"

with quotes.

Let us know if you need more help.

Good Luck!

Linq ;0)>

[boglevee]

The code doesn't seem to work for some reason. Although I've got the relevant fields disabled right now, when I click nothing happens. Here's the code I have pasted in:

Private Sub Accountopened_Click()
'This sets the textboxes to Enabled or not Enabled immediately

Dim ctrl As Control

If Account_opened Then 'If the box is checked
For Each ctrl In Me.Controls
If ctrl.ControlType = acTextBox Then
If ctrl.Tag = "Cond1" Then
ctrl.Enabled = True
End If
End If
Next

Else 'If the box is not checked
For Each ctrl In Me.Controls
If ctrl.ControlType = acTextBox Then
If ctrl.Tag = "Cond1" Then
ctrl.Enabled = False
End If
End If
Next
End If
End Sub

Any ideas as to why this is?

[boglevee]

although, actually testing on a smaller file than what i want to eventually apply this to, i have the problem that text fields respond to clicking condition1, whereas yes/no fields don't

[missinglinq]

Sorry, that's because the code doesn't tell them to look at the checkboxes!

In all of the code, replace each occurrence of this line

If ctrl.ControlType = acTextBox  Then
 

with this line

If ctrl.ControlType = acTextBox Or ctrl.ControlType = acCheckBox Then
 

That way it'll check both textboxes and checkboxes.

Linq ;0)>

[boglevee]

I might just remove the if/endif statement altogether just to make it a little more universally applicable. Thanks again!

[missinglinq]

You can't do that!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

If you remove the If...End If, Access will loop thru all controls, including those such as labels, that don't have the Enabled property, try to set them to Enabled Yes or No, and the code will fail!

Before looping and attempting to change a property, you have to be sure the type of controls you're checking actually has that property!

Here's the constants for the various types:

acCheckBox
acComboBox
acCommandButton
acLabel
acTextBox
acListBox