CODEWITHSUNDEEP
ms-access-2007
user1175126
user1175126

Reputation: 179

How to make fields selectively editable in Access 2007 form?

Is it possible to leave a field editable while making all others read-only in a form in ACCESS 2007? There are around 40 controls on my form and making them read only one by one will be inconvenient. I want to have one of these fields(an option group) editable so that when user selects an option the entire form becomes editable

Upvotes: 1

Views: 7375

Answers (2)

Fionnuala
Fionnuala

Reputation: 91376

Option 1

You could make the form that you want locked a subform and put the option group the main form:

sample form

For example:

Private Sub Form_Current()
    Me.aTable_subform.Form.AllowAdditions = False
    Me.aTable_subform.Form.AllowEdits = False
    Me.aTable_subform.Form.AllowDeletions = False

End Sub

Private Sub PickOne_AfterUpdate()

    Select Case Me.PickOne
        Case 1, 3
            Me.aTable_subform.Form.AllowAdditions = True
            Me.aTable_subform.Form.AllowEdits = True
            Me.aTable_subform.Form.AllowDeletions = True

        Case 2
            Me.aTable_subform.Form.AllowAdditions = False
            Me.aTable_subform.Form.AllowEdits = True
            Me.aTable_subform.Form.AllowDeletions = False

    End Select
End Sub

Further notes re comment

Option 2

You can have a pop-uo form that opens when the main form opens. It will become visible before the main form in this example.

Code on the main form:

Private Sub Form_Close()
    DoCmd.Close acForm, "popupform"
End Sub

Private Sub Form_Load()
    DoCmd.OpenForm "popupform", , , , , acDialog
End Sub

pop-up form

Private Sub PickOne_BeforeUpdate(Cancel As Integer)
    Select Case Me.PickOne
        Case 1
            If MsgBox("You chose this. Continue?", vbYesNo) = vbYes Then
                Forms!mainform.AllowAdditions = True
                Forms!mainform.AllowEdits = True
                Forms!mainform.Form.AllowDeletions = True

                ''Setting the visibility leaves the form
                ''available but it is no longer a dialog form
                ''but you must remember to close it elsewhere
                ''If the form is not needed, just close it here
                Me.Visible = False
            Else
                Cancel = True
            End If
        Case 2
            ''Whatever

    End Select
End Sub

Option 3

Each control has a tag property which is intended for use by the designer, for example, you could add "Admin" to all controls that need to be locked. Yo must use the load event, or a later event.

Private Sub Form_Load()
    For Each ctrl In Me.Controls
        ''For example:
        If ctrl.Tag = "Admin" And TheOption <> "Admin" Then
            ctrl.Locked = True
        End If
    Next
End Sub

You could easily have more than one tag, one for say Admin users or OptionX, another for other users or OptionY.

Upvotes: 1

HansUp
HansUp

Reputation: 97131

You could use a VBA procedure to loop through the form's Controls collection and set each control's .Locked property to True. Check each control's .ControlType property, and ignore controls such a labels and command buttons. IOW, set .Locked for only those types of controls where it makes sense.

Upvotes: 1

Related Questions