Restricting form fields in MS Access 2007

Hello,

I have a MS Access 2007 DB; in the forms, whenever the user saves/exits and brings the same information back up in the form, I want all of the populated fields to be disabled from editing.

Is this possible without restricting access to the empty fields?  I want the user to be able to edit those fields after the original fields are disabled.

Thanks
--TripWire--Asked:
Who is Participating?
 
omgangIT ManagerCommented:
Hoping I understood your question correctly.  The following procedure will lock form controls that have values but not lock form controls that are empty.  

OM Gang


Private Sub Form_Current()

On Error GoTo Err_Form_Current

    Dim ctl As Control
   
    For Each ctl In Me.Form.Controls

        Select Case ctl.ControlType
            Case 109, 106      '109 = TextBox; 106 = CheckBox
                If Nz(Me(ctl.Name), "") <> "" Then
                    Me(ctl.Name).Locked = True
                Else
                    Me(ctl.Name).Locked = False
                End If
               
            Case 111            '111 = ComboBox
                If Nz(Me(ctl.Name).Column(1), "") <> "" Then
                    Me(ctl.Name).Locked = True
                Else
                    Me(ctl.Name).Locked = False
                End If
           
            Case Else
           
        End Select
       


    Next
     
Exit_Form_Current:
    Set ctl = Nothing
   Exit Sub

Err_Form_Current:
   MsgBox Err.Number & " (" & Err.Description & ") in procedure Form_Current of VBA Document Form_frmCategory"
   Resume Exit_Form_Current
End Sub
0
 
omgangIT ManagerCommented:
Forgot to include ListBox control

I also changed the column to be evaluated for ComboBox and ListBox as the columns are indexed from 0 and the first column is the one typically saved to the table.

            Case 110, 111            '110 = ListBox, 111 = ComboBox
                If Nz(Me(ctl.Name).Column(0), "") <> "" Then
                    Me(ctl.Name).Locked = True
                Else
                    Me(ctl.Name).Locked = False
                End If
           
            Case Else
           
        End Select
0
 
--TripWire--Author Commented:
Thanks!
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
--TripWire--Author Commented:
I've noticed that some of the CheckBox fields will allow user interaction, while others will not.
If the CheckBox is blank on the form, I would the user to be able to check it.  But if it's already checked, I don't want the user to be able to uncheck it.  How do I go about this?
0
 
omgangIT ManagerCommented:
I don't  think I tested the checkbox controls because the routine doesn't work as-is for them.

Here's updated sub that includes proper check for Checkbox control values - Checkbox is Boolean (Yes/No, True/False, -1/0)
OM Gang


Private Sub Form_Current()

On Error GoTo Err_Form_Current

    Dim ctl As Control
   
    For Each ctl In Me.Form.Controls
   
    'Debug.Print ctl.Name & " : " & ctl.ControlType

        Select Case ctl.ControlType
            Case 106            '106 = CheckBox
                If Me(ctl.Name) = 0 Then
                    Me(ctl.Name).Locked = False
                Else
                    Me(ctl.Name).Locked = True
                End If
               
            Case 109            '109 = TextBox
                If Nz(Me(ctl.Name), "") <> "" Then
                    Me(ctl.Name).Locked = True
                Else
                    Me(ctl.Name).Locked = False
                End If
               
            Case 110, 111            '110 = ListBox, 111 = ComboBox
                If Nz(Me(ctl.Name).Column(0), "") <> "" Then
                    Me(ctl.Name).Locked = True
                Else
                    Me(ctl.Name).Locked = False
                End If
           
            Case Else
           
        End Select
       
    Next
     
Exit_Form_Current:
    Set ctl = Nothing
   Exit Sub

Err_Form_Current:
   MsgBox Err.Number & " (" & Err.Description & ") in procedure Form_Current of VBA Document Form_frmCategory"
   Resume Exit_Form_Current
End Sub
0
 
--TripWire--Author Commented:
For some reason it only works when I code it like this...

If Me(ctl.Name) = -1 Then
                    Me(ctl.Name).Locked = True
'skip else statement
End If

Open in new window


Not sure why.
Thanks anyway
0
 
omgangIT ManagerCommented:
If you don't include the Else part then once the control is locked you have no means to unlock it.  That's why I evaluate the control each time and either Lock or Unlock.

OM Gang
0
 
--TripWire--Author Commented:
Yes, once the checkbox has been locked, I don't want it to be unchecked once you've closed the form.
0
 
omgangIT ManagerCommented:
On my test form I am able to cycle through records so I set the procedure to run OnCurrent (each time the record changes) and lock/unlock controls each time.  If you're only working with one record when the form opens then your change will work just fine.
OM Gang
0
 
--TripWire--Author Commented:
You're right.
I was able to add the else without problem.  *fingers crossed*
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.