Solved

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

Posted on 2014-10-19
4
200 Views
Last Modified: 2014-10-19
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
Comment
Question by:softsupport
  • 2
4 Comments
 
LVL 57
ID: 40390286
<< 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
 
LVL 34

Expert Comment

by:PatHartman
ID: 40390366
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
 

Author Comment

by:softsupport
ID: 40390513
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
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40390596
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now