Results 1 to 12 of 12

Thread: Locking Fields

  1. #1
    New Lounger
    Join Date
    Jun 2010
    Location
    Atlanta
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I need to find a way to lock down a field so the people doing the work on an issue will not change their start dates. I am wanting to disable the field after the answer the question whether they want to finalize the date or nor. Below is the code that does not work.

    Private Sub Days_AfterUpdate()
    Dim response As Integer
    response = MsgBox("Is this value correct?", vbYesNo)
    If response = vbYes Then
    Me.Days.Enabled = True
    Else
    Me.Days.Enabled = False

    End If

    End Sub

    The else part is the thing that does not work. Thanks for the help.

  2. #2
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You probably need to move the focus away from the textbox before you disable it (assuming Days is a textbox).
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by Benny C View Post
    Private Sub Days_AfterUpdate()
    Dim response As Integer
    response = MsgBox("Is this value correct?", vbYesNo)
    If response = vbYes Then
    Me.Days.Enabled = True
    Else
    Me.Days.Enabled = False
    End If
    End Sub
    Haven't you got this the wrong way round? If they say yes (It is correct) you should set enabled to false ... and leave enabled as true it if they say no...

    And I agree with SteveH, you should set the focus to another control before you disable this control.
    Regards
    John



  4. #4
    New Lounger
    Join Date
    Jun 2010
    Location
    Atlanta
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You are right I have it backwards. How would the code change by changing focus? I am still learning how to code in Access.

  5. #5
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Benny C View Post
    You are right I have it backwards. How would the code change by changing focus? I am still learning how to code in Access.
    The Error number is 2164 - You can't disable a control while it has the focus.

    Your code should look something like:

    Private Sub Days_AfterUpdate()
    Dim response As Integer
    response = MsgBox("Is this value correct?", vbYesNo)
    If response = vbYes Then
    Me.SomeOtherControl.SetFocus
    Me.Days.Enabled = False
    Else
    Me.Days.Enabled = True
    End If
    End Sub
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  6. #6
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    It might have been easier just to use the Locked property rather than the Enabled property.
    You can set this while a field has the focus, because it just makes it Read Only,
    rather than completely disabled.

    Code:
    Private Sub Days_AfterUpdate()
    Dim response As Integer
    response = MsgBox("Is this value correct?", vbYesNo)
    If response = vbYes Then
        Me!Days.Locked= True
    Else
        Me!Days.Locked= False
    End If
    End Sub
    Also when referencing a forms field in Access you should
    really use either the Me!FieldName syntax or Me("FieldName") syntax rather than Me.FieldName
    Although if the field is a control on the form toy can use just FieldName as long as it is the same as the Control name.

    e.g. Me!Days.Locked is the same as Days.Locked

    It is also worth putting something into the On Current Event to check on existing Records as you change from Row to Row, otherwise once this question is answered on one record, the field will be locked on the next one as well.

    Usually an After Update Event such as this that does a form or Field Locking will have an accompanying equivalent on On Current, although omitting the question.
    Not sure what the Criteria would be on yours.
    Maybe checking for NULL and locking if Not Null.

    Indeed IF you need to have Finalised Dates and Non Finalised dates as well, and want to Lock when the Date is Finalised, then you may well need an Additional Field to Flag DaysFinalised (type Yes/No) Default False, and also set that to True after update response is Yes. Then you could use that as the On Current Trigger Locking Criteria.
    Andrew

  7. #7
    New Lounger
    Join Date
    Jun 2010
    Location
    Atlanta
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It works and thanks for your help.

  8. #8
    New Lounger
    Join Date
    Jun 2010
    Location
    Atlanta
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am needing more help for I need to lock down 3 fields and I am stuck on the coding. I need them to put a creation date in then lock it down, then expected date and lock the value and later on the closed date and lock that value down. I need it to not allow them to go back and reverse their dates because I need to truly see what the real dates are so they can't cheat on finishing projects.
    Attached Files Attached Files

  9. #9
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Have a look at this example.
    I have used Locked and conditional formatting to colour cells rather than Enabled.
    This is because of my earlier reasons.
    Also because on a continuous form a colour or enabled change appears the same on all records within the field
    But the conditional formatting by formula is on each value.
    I have also added some automatic flagging fields to the table for the 3 fields
    so it is easier to know that they have been locked when using on current.
    This does allow an entry to be made BUT not agreed and changed later.
    Once agreed it becomes locked.
    As an alternative strategy, you could just clear the field back to NULL if they say NO, but you would have to move focus
    and then reset.
    I have added another form with that as the alternative strategy of Clearing data when answer is NO
    It has slightly different code and rules for conditional formatting

    [attachment=89147:New Microsoft Office Access Application Modified.zip]
    Attached Files Attached Files
    Andrew

  10. #10
    New Lounger
    Join Date
    Jun 2010
    Location
    Atlanta
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It looks good but the question I have now is if they answer no then they can type any date they want to in there and change it later. I need to make all the fields required but not the closed date because that will be in the future and they wouldn't know that at the time of entry. I guess I could make all fields required and locked except for the closed and tell them that any date they type in can't be changed. Any advice would help? I just don't know how to keep people from messing with the data and cheating. Thanks in advance for your help.

  11. #11
    New Lounger
    Join Date
    Jun 2010
    Location
    Atlanta
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I looking back your answer I think the alternative strategy would work if I just just put required on expected days and creation dates but not required the closed date because they wouldn't know that at the time of entering. Your thoughts.

  12. #12
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Your strategy seems reasonable to me - if you simply use the field validation rule that those dates are required, then if they fail to complete one of them, you can use the validation message to tell the user that they must enter that date. Once the record is saved you may want to use a second copy of the form that locks those controls so the data cannot be edited. Your data entry version of the form would not have those locked.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •