Results 1 to 12 of 12
  1. #1
    boboivan is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    Dec 2015
    Posts
    65

    Allow edits with exceptions

    Hello all!

    I have this form with lots of subforms, fields, comboboxes, etc, that I want to be editable if the user is admin and not editable for any other user. There is though a specific combobox (used to find records in a subform) that I want to be editable in any case.



    How can I do that?

    Thx!

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    How can I do that?
    you put code in the form open event to lock each control except the one you want unlocked

    change names to suit but assuming you have a global Boolean variable called 'userisadmin' (usually populated at time of login) and the combo you want allusers to have access to is called 'cboforallusers' then code would be something like

    Code:
    Private Sub Form_Open (Cancel as Integer)
    dim ctrl as control
    
       on error resume next 'error will be ctrl does not have a locked property
       
       for each ctrl in me.controls
           ctrl.locked=userisadmin=false or ctrl.name<>"cboforallusers"
       next ctrl
    
       on error goto 0
    
    End Sub
    edited to correct typos

  3. #3
    boboivan is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    Dec 2015
    Posts
    65
    Thx Ajax!
    Sorry for my late answer!

    So, my current code is
    Code:
    Private Sub Form_Current()
    If Me.Text14.Value = TempVars("CurentUser") Or TempVars("CurentUser") = "ME" Or TempVars("CurentUser") = "ADMIN" Then
    Me.Command12.Enabled = True
    Me.Status1.Value = "Editable"
    Me.AllowEdits = True
    Me.Command39.Enabled = True
    Me.Subform1.Locked = False
    Me.Subform2.Locked = False
    Me.Subform3.Locked = False
    Me.Subform4.Locked = False
    Me.Subform5.Locked = False
    Me.Subform6.Locked = False
    Me.Subform7.Locked = False
    Me.Subform8.Locked = False
    Me.Subform9.Locked = False
    Me.Subform10.Locked = False
    Else
    Me.Command12.Enabled = False
    Me.Status1.Value = "Uneditable"
    Me.AllowEdits = False
    Me.Command39.Enabled = False
    Me.Subform1.Locked = True
    Me.Subform2.Locked = True
    Me.Subform3.Locked = True
    Me.Subform4.Locked = True
    Me.Subform5.Locked = True
    Me.Subform6.Locked = True
    
    Me.Subform6.Form.Sub_Subform6.Form.Combo65.Locked = False
    
    
    Me.Subform7.Locked = True
    Me.Subform8.Locked = True
    Me.Subform9.Locked = True
    Me.Subform10.Locked = True
    End If
    End Sub
    The combobox that I what to be always unlocked is placed on a subform of Subform6.

    I've tried to add the following line code, but with no success:
    Code:
    Me.Subform6.Form.Sub_Subform6.Form.Combo65.Locked = False
    Any idea?

    Thx again!

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Perhaps:
    Code:
    Me!Subform6.Form!Combo65.Locked = False
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    boboivan is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    Dec 2015
    Posts
    65
    No Bob. It doesn't work either!

  6. #6
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by boboivan View Post
    No Bob. It doesn't work either!
    Sorry, I missed that the control is on a subform that is on a subform. Try:
    Code:
    Me!Subform6.Form!Sub_Subform6.Combo65.Locked = False
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  7. #7
    boboivan is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    Dec 2015
    Posts
    65
    This is what I've tried in the first place.

    It returns
    Code:
     Run-time error "438":
    Object doesn't support this property or method
    So I've added .Form. in front of Combo65 in order to avoid this error

    In any case, it doesn't work. It seams like if in the same condition I have Subform6.Locked = True, I cannot unlock any other field in the subform of this subform.

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    part of the problem is you have set allowedits to false for the whole form (which will include all subforms)

    Else
    Me.Command12.Enabled = False
    Me.Status1.Value = "Uneditable"
    Me.AllowEdits = False
    - so trying to unlock a control on a subform will have no effect.

  9. #9
    boboivan is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    Dec 2015
    Posts
    65
    Ajax, I've deleted this line, with no effect whatsoever.
    I still believe there is a contradiction between these 2 lines:
    Code:
    Me.Subform6.Locked = True
    Me.Subform6.Form.Sub_Subform6.Form.Combo65.Locked = False
    I've tried:
    Code:
    Private Sub Combo65_GotFocus()
    Me.Combo65.Locked = False
    End Sub
    and I don't understand why is not working.

  10. #10
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Me.Subform6.Locked = True
    Me.Subform6.Form.Sub_Subform6.Form.Combo65.Locked = False
    as explained before, if you've locked a subform, all controls on that subform will also be locked - you cannot unlock them individually

  11. #11
    boboivan is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    Dec 2015
    Posts
    65
    I think I've got it!

    Instead of
    Code:
    Me.Subform6.Locked = True
    Me.Subform6.Form.Sub_Subform6.Form.Combo65.Locked = False
    is

    Code:
    Dim ctl As Control
    For Each ctl In Me.Subform6.Controls
    On Error Resume Next
    If ctl.Name <> "Sub_Subform6" Then
    ctl.Locked = True
    End If
    Next ctl
    For Each ctl In Me.Subform6.Form.Sub_Subform6.Controls
    If ctl.Name <> "Combo65" Then
    ctl.Locked = True
    End If
    Next ctl
    On Error GoTo 0
    Thx Ajax and Bob for your support!

  12. #12
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    glad you got there in the end!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Form won't allow edits
    By lawdy in forum Forms
    Replies: 5
    Last Post: 03-08-2014, 03:48 PM
  2. Replies: 4
    Last Post: 11-02-2012, 11:00 PM
  3. delete query with exceptions
    By Jerseynjphillypa in forum Queries
    Replies: 3
    Last Post: 07-11-2012, 08:07 AM
  4. Allow Edits on Subform
    By scoughlan in forum Programming
    Replies: 5
    Last Post: 01-16-2012, 02:12 PM
  5. Deletions vs. Edits
    By jparker1954 in forum Forms
    Replies: 7
    Last Post: 09-20-2011, 08:02 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums