CODEWITHSUNDEEP
ms-access
user745778
user745778

Reputation: 97

disable cells based on another cell

In Access 07 on a form: I need to disable two cells based on a dropdown. Another words, if the user picks "new" from the dropdown, two other cells get disabled. I beleive I will need to right-click the dropdown, build code, but I don't know the IF... script I need to use.

Upvotes: 1

Views: 1212

Answers (1)

HansUp
HansUp

Reputation: 97131

Say your form includes a combo box named cboMyField which is bound to a field named MyField in the form's record source, and two text boxes: txtField2 bound to Field2; and txtField3 bound to Field3.

You could check the combo's value and set the Enabled property of the two text boxes appropriately by calling a procedure in your form's code module.

Private Sub SetButtonStatus()
    If Me.cboMyField = "new" Then
        Me.txtField2.Enabled = False
        Me.txtField3.Enabled = False
    Else
        Me.txtField2.Enabled = True
        Me.txtField3.Enabled = True
    End If
End Sub

Call that procedure from the form's On Current event so the text box status will be set as you navigate between rows.

Private Sub Form_Current()
    SetButtonStatus
End Sub

Do the same for cboMyField's After Update event so the text box status will be updated based on a user change for cboMyField.

Private Sub cboMyField_AfterUpdate()
    SetButtonStatus
End Sub

Edit, Trouble-shooting: Since you're using Access 2007. Put the database in a trusted location and run it from there. See Get started with Access 2007 security

Edit2: Change the SetButtonStatus procedure temporarily to check whether it even runs, and how it sees the combo box value if it does run.

 Private Sub SetButtonStatus()
        MsgBox "SetButtonStatus"
        MsgBox "Value of cboMyField is '" & Me.cboMyField & "'"
        If Me.cboMyField = "new" Then
            Me.txtField2.Enabled = False
            Me.txtField3.Enabled = False
        Else
            Me.txtField2.Enabled = True
            Me.txtField3.Enabled = True
        End If
    End Sub

Also add Option Explicit to the Declarations section (at the top) of your module. Then select Debug->Compile from the VB editor's main menu. That effort should tell us whether the code includes any names which VBA doesn't recognize.

Edit3: From private communication, the combo's bound field was numeric data type, so could never be equal to "new". Therefore every time the procedure ran, it set text box Enabled property to True.

Upvotes: 3

Related Questions