Results 1 to 14 of 14
  1. #1
    data808 is offline Noob
    Windows 10 Access 2013 32bit
    Join Date
    Aug 2012
    Posts
    727

    Best Way To Lock All Fields In Form If Checkbox Is True

    I wanted to give some authority to the users by allowing them to click a checkbox to lock a record from being edited. What would be the best way to go about this?

    I had the idea of doing something like this on the current form event:

    If Me.checkbox = True Then
    Me.textbox1.Enabled = False
    Me.textbox2.Enabled = False
    Me.textbox3.Enabled = False
    Me.Dirty = False
    Else


    Me.textbox1.Enabled = True
    Me.textbox2.Enabled = True
    Me.textbox3.Enabled = True
    Me.Dirty = False
    End If

    I noticed I had to implement the Me.Dirty = False because the Enables do not take effect until the record is saved. I would also implement something similar to the checkbox click event as well so that when the user clicks the checkbox it will automatically lock up the textboxes right away.

    I was just wondering if there was a better or easier way to do this? Like turn the whole form to Snapshot if the checkbox is true. Then turn it back to Dynaset if the checkbox is false.

    What are your thoughts?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Conditional Formatting can enable/disable textboxes and comboboxes.

    Could try code to change form mode. Let us know what happens.
    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.

  3. #3
    writeprivate is offline Advanced Access Coder
    Windows 10 Access 2021
    Join Date
    Feb 2023
    Posts
    42
    Quote Originally Posted by data808 View Post
    I wanted to give some authority to the users by allowing them to click a checkbox to lock a record from being edited. What would be the best way to go about this?

    I had the idea of doing something like this on the current form event:

    If Me.checkbox = True Then
    Me.textbox1.Enabled = False
    Me.textbox2.Enabled = False
    Me.textbox3.Enabled = False
    Me.Dirty = False
    Else
    Me.textbox1.Enabled = True
    Me.textbox2.Enabled = True
    Me.textbox3.Enabled = True
    Me.Dirty = False
    End If

    I noticed I had to implement the Me.Dirty = False because the Enables do not take effect until the record is saved. I would also implement something similar to the checkbox click event as well so that when the user clicks the checkbox it will automatically lock up the textboxes right away.

    I was just wondering if there was a better or easier way to do this? Like turn the whole form to Snapshot if the checkbox is true. Then turn it back to Dynaset if the checkbox is false.

    What are your thoughts?
    If you're wanting to disable in the middle of a form input (ie. Dirty), maybe try using an after update event. You can call a function to minimise code.

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Me.Textbox.Enabled = Not Me.checkbox
    Me.dirty = Not Me.checkbox

    Any time you repeat code in both blocks of an if/else block, you can remove it and execute outside the block.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    AlexFim is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2023
    Location
    São Paulo (Brazil)
    Posts
    5
    Hello,
    You can create a generic function in any module of your application, to use in any form. This is the function:


    Public Function fnEnableControl(nForm As Form, blnValue As Boolean)
    On Error GoTo handle_error
    Dim ctl As Control


    For Each ctl In nForm.Controls
    If TypeOf ctl Is TextBox Then ctl.Enabled = blnValue
    If TypeOf ctl Is ComboBox Then ctl.Enabled = blnValue
    If TypeOf ctl Is OptionButton Then ctl.Enabled = blnValue
    If TypeOf ctl Is CheckBox Then ctl.Enabled = blnValue
    If TypeOf ctl Is ListBox Then ctl.Enabled = blnValue
    If TypeOf ctl Is Page Then ctl.Visible = blnValue
    If TypeOf ctl Is Picture Then ctl.Enabled = blnValue
    If TypeOf ctl Is Attachment Then ctl.Enabled = blnValue

    Next
    Exit Function

    handle_error:
    MsgBox "Error generated: " & Err.Number & " - " & Err.Description & "", vbCritical, "Message"
    Exit Function


    End Function


    And in the Form_Current event insert this:

    If Me.CheckBox = True Then
    Call fnEnableControl([NAME_OF_YOUR_FORM], False)
    else
    Call fnEnableControl([NAME_OF_YOUR_FORM], True)
    End If


    Me.Dirty = False


    I believe it will help you.


    Yours sincerely


    AlexFim


  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Surely a Select Case would be more appropriate?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    data808 is offline Noob
    Windows 10 Access 2013 32bit
    Join Date
    Aug 2012
    Posts
    727
    Quote Originally Posted by AlexFim View Post
    Hello,
    You can create a generic function in any module of your application, to use in any form. This is the function:


    Public Function fnEnableControl(nForm As Form, blnValue As Boolean)
    On Error GoTo handle_error
    Dim ctl As Control


    For Each ctl In nForm.Controls
    If TypeOf ctl Is TextBox Then ctl.Enabled = blnValue
    If TypeOf ctl Is ComboBox Then ctl.Enabled = blnValue
    If TypeOf ctl Is OptionButton Then ctl.Enabled = blnValue
    If TypeOf ctl Is CheckBox Then ctl.Enabled = blnValue
    If TypeOf ctl Is ListBox Then ctl.Enabled = blnValue
    If TypeOf ctl Is Page Then ctl.Visible = blnValue
    If TypeOf ctl Is Picture Then ctl.Enabled = blnValue
    If TypeOf ctl Is Attachment Then ctl.Enabled = blnValue

    Next
    Exit Function

    handle_error:
    MsgBox "Error generated: " & Err.Number & " - " & Err.Description & "", vbCritical, "Message"
    Exit Function


    End Function


    And in the Form_Current event insert this:

    If Me.CheckBox = True Then
    Call fnEnableControl([NAME_OF_YOUR_FORM], False)
    else
    Call fnEnableControl([NAME_OF_YOUR_FORM], True)
    End If


    Me.Dirty = False


    I believe it will help you.


    Yours sincerely


    AlexFim

    Thanks. This looks very helpful. Do I just create a module for the function? Or can I just add it at the top of the VBA code builder window for the form?

    Also the other problem I was running into while doing it my way was that I have this text box that has this in the On Key Press event:

    KeyAscii = Asc(UCase(Chr(KeyAscii)))

    Then also had this in the After Update event:

    Me.txtName = UCase(Me.txtName)

    And this would cause issues when trying to enable and disable the text box. It would always stay enabled no matter what. Only when I removed the codes for uppercase was it able to disable the text box. Is there a way around this while still being able to type all upper case in that field? It's very strange because I have the same codes for uppercase in another text box and it has no problems disabling it.

  8. #8
    data808 is offline Noob
    Windows 10 Access 2013 32bit
    Join Date
    Aug 2012
    Posts
    727
    Quote Originally Posted by Welshgasman View Post
    Surely a Select Case would be more appropriate?
    What is a Select Case?

    Also would you happen to know why these lines would interfere with disabling a text box?

    KeyAscii = Asc(UCase(Chr(KeyAscii))) = Key Press Event

    Me.txtName = UCase(Me.txtName) = After Update Event

    These are both for the Me.txtName text box. I should also add that it will put a "0" in that field once it attempts to disable it using this:

    Code:
    If Me.CheckBox = True Then    
        Me.txtName.Enabled = False
        Me.Dirty = False
    Else
        Me.txtName.Enabled = True
        Me.Dirty = False
    End If


    So the original value will be replaced with a "0". In other words, if I had John Doe saved in the txtName field and I run the VBA to disable all the text boxes on the form, it will disable all text boxes except the txtName field and I think it's because of those uppercase lines of code because if I remove those lines, it will disable it.

    However, I have another text box (Me.txtCompany) that has those uppercase lines of code for the same events and the code has no trouble disabling it. So it's a mystery.


  9. #9
    data808 is offline Noob
    Windows 10 Access 2013 32bit
    Join Date
    Aug 2012
    Posts
    727
    I just realized that Me.txtName.Locked would be a better one to use. I'm going to mess around with that more.

  10. #10
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    What is a Select Case?
    That reply was addressed to @AlexFim as all thos IFs would be highly inefficient.

    https://learn.microsoft.com/en-us/of...case-statement

    No idea as to why any Upper code would affect your textbox, but if one works and another does not, then there must be something different between them.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  11. #11
    data808 is offline Noob
    Windows 10 Access 2013 32bit
    Join Date
    Aug 2012
    Posts
    727
    Sorry I figured out the problem. The uppercase code had nothing to do with it. It was because I wrote it like this:

    If Me.CheckBox = True Then
    Me.txtName = True

    instead of this:

    If Me.CheckBox = True Then
    Me.txtName.Locked = True

    So it would always try to fill in those fields because I forgot to type the .Locked portion of the code.

    It's all working fine now. The lock feature is working much better than the enable feature for my scenario. Thanks for the help and suggestions.

  12. #12
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    You can shorten that by using the syntax in post 4, whether locked or enabled.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  13. #13
    madpiet is online now Competent Performer
    Windows 10 Office 365
    Join Date
    Feb 2023
    Posts
    122
    Quote Originally Posted by data808 View Post
    Sorry I figured out the problem. The uppercase code had nothing to do with it. It was because I wrote it like this:

    If Me.CheckBox = True Then
    Me.txtName = True

    instead of this:

    If Me.CheckBox = True Then
    Me.txtName.Locked = True

    So it would always try to fill in those fields because I forgot to type the .Locked portion of the code.

    It's all working fine now. The lock feature is working much better than the enable feature for my scenario. Thanks for the help and suggestions.
    Why not keep it really simple?

    Me.txtName.Locked = me.Checkbox

  14. #14
    Join Date
    Feb 2023
    Posts
    4
    to save code for locking each control we have put the controls on a register and simply lock the whole page:

    Sub GUI_lock()
    Me.RegisterFA.Pages(0).Enabled = False
    End Sub

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

Similar Threads

  1. Replies: 6
    Last Post: 01-05-2023, 05:13 PM
  2. Replies: 5
    Last Post: 11-10-2022, 10:42 AM
  3. Replies: 7
    Last Post: 08-02-2016, 01:19 PM
  4. Replies: 1
    Last Post: 03-27-2016, 10:29 PM
  5. if checkbox= true subtract 8.75 from A to=B
    By VanillaAwesome in forum Queries
    Replies: 6
    Last Post: 07-28-2012, 12:48 PM

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