We help IT Professionals succeed at work.

Code for greying out the form is not working

gigifarrow
gigifarrow used Ask the Experts™
on
The greyed out field is based on the model number that is in a drop down box. Once the model number is selected then certain fields are greyed. In my code  I have two model numbers,"A3 BFIST" and  "M2A2" then in the code I have two fields one is enabled based on the model number.


Private Sub Form_Current()

If Model = "A3 BFIST" Then
Me.verification.Enabled = True

Else
'The field you want to keep open
 Me.doorMod.Enabled = False
End If

If Model = "M2A2" Then
'The field you want to keep open
 Me.doorMod.Enabled = False
End If


End Sub
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2013

Commented:
Try the following sub (it starts with the controls enabled, and then disables specific controls based on the value of the Model combo) - you will have to edit it as shown in the comments to disable speific controls.  The sub needs to be called from the Form's current event and the Model combo's After Update event:

Private Sub EnableControls()
      ' Start with controls enabled
      Me.verification.Enabled = TRUE
      Me.doorMod.Enabled = TRUE
      
       ' Then disable them according to Model
       Select Case  Model
             Case "A3 BFIST"
                    'Use Me.FieldName.Enabled = False  for any fields that need to be disabled
             Case "M2A2"
                    'Use Me.FieldName.Enabled = False  for any fields that need to be disabled
             Case Else
                    'Use Me.FieldName.Enabled = False  for any fields that need to be disabled for all other models
       End Select
End Sub

Open in new window



Private Sub Form_Current()

       EnableControls
End Sub

Open in new window

Private Sub Model_AfterUpdate()
      EnableControls
End Sub

Open in new window

Author

Commented:
Thank you, I will try it and see if it works.
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
"The sub needs to be called from the Form's current event and the Model combo's After Update event:"

In case you don't understand why you need to call this from both the Form_Current and the combo_AfterUpdate event.  Assuming you are using a bound form, when you land on a record that already has data in it, you want it to automatically enable/disable the controls based on what has already been selected, so you need to use the Form_Current event as well as the AfterUpdate event of the combo box.

I tend to use a similar technique, with a twist.  I have a function that I frequently use for situations like this.

Public Function IsIn(TestValue As Variant, ParamArray ArrayOfValues() As Variant) As Boolean

    'Tests to see whether a value is in a list of other values.
    'Similar to both the SQL IN ( ) clause and SELECT Case statements
    'Accepts a value of Variant data type, followed by an array of values.
    'Checks to see if the first value is in the array.
    Dim intLoop As Integer
   
    IsIn = False
   
    If IsNull(TestValue) Then Exit Function
   
    For intLoop = LBound(ArrayOfValues) To UBound(ArrayOfValues)
        If TestValue = ArrayOfValues(intLoop) Then
            IsIn = True
            Exit For
        End If
    Next
   
End Function


The function accepts a TestValue and then accepts an array of other values to test the TestValue against (similar to the SQL IN () clause).  Since this returns a boolean value, I can do something like the following, which modifies the code that you provided.

Private Function EnableControls

    Me.verification.Enabled = IsIn(me.cbo_Model, "A3 BFIST")

    me.doorMod.Enabled = NOT IsIn(me.cbo_Model, "A3 BFIST", "M2A2")

End Sub

But the challenge with this type of logic is that as the number of models grows or as the number of conditional controls (those that need to be filled in for a particular model) grows you have to increase the number of tests, which can get extremely complicated

Generally, I prefer one of two techniques:

1.  Add a field (or multiple fields) to my table (I assume you have a "Models" table) which identifies what fields would be enabled/disabled for a particular model.  This could be a bunch of Yes/No fields.

2.  Or, my preferred method, is to create separate subforms for each model type, which only contain the fields that are appropriate for that model.  Then you only need to add a single field to the models table, indicating which subform to use.  Then, instead of enabling/disabling controls, the EnableControls subroutine, would be replaced with EnableSubform.  Then, all you have to do is modify the combo boxes RowSource, to include the name of the subform to use for that model (hidden of course).  The EnableSubform subroutine would look something like:

Private Sub EnableSubform

    if me.cbo_Model & "" = "" Then
        'no model selected    
        me.sub_Model.visible = false
        me.sub_Model.ControlSource = ""
    else
        me.sub_Model.ControlSource = me.cbo_Model.column(2)
        me.sub_Model_Visible = true
    end if

End Sub
Chris BRetired

Commented:
Seems to be two versions of this currently -

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27676085.html

Chris B
Most Valuable Expert 2012
Top Expert 2013

Commented:
gigifarrow,

Did you actually try the solution I posted?  Just curious what you found wrong with it.

Also - since you found a solution here, you should delete your original question.  EE does not allow duplicate questions.