Results 1 to 11 of 11
  1. #1
    jdashm is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2016
    Posts
    58

    Lock fields matching user department

    I am attempting to lock fields in an Access form from future editing after a submit button is clicked.
    The criteria for which records is the user's department which i can use a DLookup to find.
    Any help is appreciated.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Single record view form? Datasheet or continuous? Subform involved? etc.
    Pretty sure it would have to be a single view form that you lock the required fields on the form Current Event. Locking is usually done by looping through the control set, and if it needs to be selective, then the tag property is usually used to identify them. This is a common topic.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Quote Originally Posted by Micron View Post
    Pretty sure it would have to be a single view form that you lock the required fields on the form Current Event.
    Actually, using the Locked Property is the one thing you can do in the Form_Current
    Event
    for all three types of Forms...Single View, Datasheet View and Continuous View...because using Locked doesn't change the appearance of the Controls.

    All Controls on all Forms will be Locked or UnLocked...depending of the Current Record...but will change to the appropriate state when another Record is selected.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you're saying control locking in datasheet or continuous form view works independently per record, then that's a surprise considering you can't do other things because there's only one instance of a control. I take it this means you can lock but not disable or alter visibility based on selected record. Would be nice if I can remember that when I need it, but who knows when that might be...

  5. #5
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    As far as I know, it's the only type of Formatting you can do, on all types of Forms, using VBA code.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    jdashm is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2016
    Posts
    58
    Thanks for the input.
    Still not sure how to proceed. Let me get a bit more detailed...
    Have a user form that only shows records for that user's department (i.e. Marketing). The job of the user is to update several fields in those records and when completed click a "Submit" button.
    That triggers a query that update those values to the matching records.
    Here is where i am stuck...
    We need to lock down those fields permanently that the user (for their department only) was originally able to update. The user can still look at the records but not change.
    Any examples of VBA that you can provide me with would be greatly appreciated.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by jdashm View Post
    Have a user form that only shows records for that user's department (i.e. Marketing). The job of the user is to update several fields in those records and when completed click a "Submit" button.
    A button is optional; not necessary as there are other things that might happen to commit changes to a record. That would include moving off the record (even if just going from one record to another on a datasheet).

    That triggers a query that update those values to the matching records.
    As per previous statement, not necessary. In fact, if the form is bound to a table or updatable query, doing any action that commits changes to a record is immediate. A query isn't necessary either. All of that depends on whether or not your form is bound to anything. If not, then yes you will need something to commit the changes, and would need either a query or vba generated sql.

    We need to lock down those fields permanently that the user (for their department only) was originally able to update. The user can still look at the records but not change. Any examples of VBA that you can provide me with would be greatly appreciated.
    Here's where we get to the meat of the matter. Problem is the unknowns. This means if a field was edited, even just once, then no one can edit that field ever again but other fields in that record could be because they contain the original data? Or the whole record is locked because one field was edited? Or it doesn't matter anyway because there's only one field they can edit anyway? Any of those seem like odd ideas to me. Care to share more of the logic behind this in case there's a better way?

    BTW, I don't see where you clarified anything I asked about the form...

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Consider Conditional Formatting for textbox and combobox. Exactly what is the condition that should cause fields to be locked?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Quote Originally Posted by June7 View Post
    Exactly what is the condition that should cause fields to be locked?
    Per the OP...the 'submit button' being clicked...presumably declaring the Record 'complete.' There may not be set in stone 'conditions.' For instance a Form may have Controls for types of insurance, for a given customer. Having checked 'home owners' may be a completed Record for some customers....while having checked 'home owners' and 'auto insurance' may be a completed Record for other customers. Only the user can decide if a Record is complete.

    It would appear that the user can populate several fields...and not necessarily all at once. When all fields needed have been populated, he clicks the 'submit' button to declare the Record complete.

    As has been stated...you don't need a button to commit the Record (assuming that this is a Bound Form) as access will do this automatically. Rather than a Command Button...I'd simply have a Checkbox and code like this...with Submitted as the Checkbox name:

    Code:
    Private Sub Form_Current()
     If Me.Submitted = -1 Then
      Me.AllowEdits = False
     Else
      Me.AllowEdits = True
     End If
    End Sub

    If you want the Record to be locked immediately it's 'submitted' you'd have to use the Submitted_AfterUpdate Event...but this time you'd have to Lock each Control individually...which could be a hassle. A Form's 'Allow' Properties, when a given Record is opened...cannot be changed, i.e. if

    Me.AllowEdits = True

    is in effect when the Form_Current event fires...trying to set it using

    Me.AllowEdits = False

    will not work...AllowEdits = True will still remain in effect...hence you'd have to Lock each Control independently.

    My experience has shown that immediately someone declares a Record complete...and 'submits' it...they'll discover they forgot something...too late! So I simply allow for this by not locking the Record until it's left.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  10. #10
    jdashm is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2016
    Posts
    58
    First... thanks to all of you for your replies.
    I found a solution with the example that missinglinq placed.
    I added an option to the underlying query that when the form button was clicked to mark a checkbox field to "Yes"
    Then i added some code so that when the form loads if the checkbox is on to make the record locked for editing.
    Not sure why i thought it would be more complicated than that.
    Here is the code i used...

    Private Sub Form_Load()
    ' When form opens...
    ' Looks up SOM User's dept and sets filter (using query) to show only those records


    Application.Echo False


    cboFilterDept = DLookup("strUserDept", "tblUserRoles", "[strUserID]='" & Forms![_frmLoginVerify]!txtLoginName.Value & "'")
    DoCmd.SetFilter "qryFilterForDept-_Budgeting_SOM", "", ""


    Application.Echo True

    If Me.Submitted = True Then
    Me.AllowEdits = False
    Else
    Me.AllowEdits = True
    End If



    End Sub

  11. #11
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Glad we could help!

    Good luck with your project!

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

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

Similar Threads

  1. Replies: 6
    Last Post: 04-08-2018, 03:45 AM
  2. Replies: 2
    Last Post: 11-21-2017, 03:52 PM
  3. Replies: 1
    Last Post: 01-04-2014, 11:04 PM
  4. Replies: 5
    Last Post: 06-29-2010, 06:10 AM
  5. Replies: 5
    Last Post: 03-20-2010, 08:30 AM

Tags for this Thread

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