• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 215
  • Last Modified:

Disable edits on form while allowing two control boxes to view data on the form

I have a form, I want to allow users to view, but not change the data.  the form has two combo boxes, cboStatus, cboCenter.  The User.AccessID will determine if user can add, delete, edit, but still want users without these permissions to change the combo boxes to view all data.  Here is my code, but not working properly.

Private Sub Form_Open(Cancel As Integer)
If User.AccessID <= 2 Then
    Me.AllowAdditions = True
        Me.AllowDeletions = True
        Me.AllowEdits = True
    Else
        Me.AllowAdditions = False
        Me.AllowDeletions = False
        Me.AllowEdits = False
        Me.cboCenter.enabled = True
        Me.cboStatus.enabled = True
       
    End If
       
Exit_Form_Open:
    Exit Sub
   
Err_Form_Open:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_Form_Open
 
End Sub


Any assistance appreciated
0
softsupport
Asked:
softsupport
  • 2
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<< The User.AccessID will determine if user can add, delete, edit, but still want users without these permissions to change the combo boxes to view all data.>>

You can't do it the way your trying to.  You must lock/unlock all the individual controls.

Jim.
0
 
PatHartmanCommented:
I've attached some code that I use in  several applications.  I call it from the Current event of the form.  It checks permission level (security for this application only so you can ignore that part) and also takes into account whether this is a new record since for new records you have to unlock some fields that can't be updated once the record is saved.  It also makes provision for occasionally having to control specific controls differently.  I use the Tag property of a control and place the value "Lock" or "NoLock" to enforce a specific treatment for certain controls.  This is what you will need to do.  You want the code to lock everything EXCEPT the combos/buttons you use for searching.

The LockControls sub can be copied EXACTLY and placed in your code.  You would use the sample Current event code to write your code to call the sub.  Passing in "Me" allows the LockControls sub to work with controls on ANY form.  This is generic code and should be placed in a module rather than in the class event of a form.  Doing that will allow you to call the code from any form/subform by simply passing in "Me".

Private Sub Form_Current()    
    If Me.NewRecord Then
        If Forms!frmLogin!txtLevel >= 7 Then
            Call LockControls(Me, False)        'unlock
        Else
            Call LockControls(Me, True)         'lock
        End If
    Else
        Call LockControls(Me, True)             'cannot change exixting records but can rerun export.
    End If
End Sub

Public Sub LockControls(frm As Form, bLock As Boolean)
    Dim ctl As control
    For Each ctl In frm.Controls
    Select Case ctl.ControlType
        Case acTextBox, acComboBox, acListBox, acCheckBox
            Select Case ctl.Tag
                Case "NoLock"
                    ctl.Locked = False
                Case "Lock"
                    ctl.Locked = True
                Case Else
                    ctl.Locked = bLock         'toggle locks
            End Select
        Case acCommandButton
            Select Case ctl.Tag
                Case "NoLock"
                    ctl.enabled = True
                Case "Lock"
                    ctl.enabled = False
                Case Else
                    ctl.enabled = Not bLock         'toggle locks
            End Select
    End Select
Next ctl
Set ctl = Nothing
End Sub

Open in new window

0
 
softsupportAuthor Commented:
Pat...
Thank you.  I made the changes you posted and now the entire form is lock down regardless of the User.AccessID.  I believe your suggestion will work, but want the User.AccessID <=2 to allow access and User.AccessID >3 to lock the form but allow viewing.  I have used the tag property of the control as you suggested but "Locks " or "NoLocks"  regardless of User.AccessID.  What am I doing wrong?

Private Sub Form_Current()
If Me.NewRecord Then
        If User.AccessID <= 2 Then
           Call LockControls(Me, False)        'unlock
        Else
            Call LockControls(Me, True)         'lock
        End If
    Else
        Call LockControls(Me, True)             'cannot change exixting records but can rerun export.
    End If

End Sub


will this also not allow for edits or deletions?
0
 
PatHartmanCommented:
Sorry, The code I pulled came from a form where existing records couldn't be changed at all so the security check is inside the If Me.NewRecords.  You can eliminate the NewRecords check unless you have to specifically lock/unlock a control.

Private Sub Form_Current()
     If User.AccessID <= 2 Then
         Call LockControls(Me, False)        'unlock
     Else
         Call LockControls(Me, True)             'cannot change exixting records but can rerun export.
     End If
 End Sub

Open in new window


Also, the code checks for "Lock" or "NoLock" so that is what you need to put in the Tag property.
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

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