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

x
?
Solved

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

Posted on 2014-10-19
4
Medium Priority
?
213 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 58
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 40

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 40

Accepted Solution

by:
PatHartman earned 2000 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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

783 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