Solved

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

Posted on 2014-10-19
4
205 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 35

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 35

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

856 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