Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 661
  • Last Modified:

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
0
--TripWire--
Asked:
--TripWire--
  • 5
  • 5
2 Solutions
 
omgangCommented:
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
 
omgangCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
--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
 
omgangCommented:
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
 
omgangCommented:
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
 
omgangCommented:
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now