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

[data808]

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?

[June7]

Conditional Formatting can enable/disable textboxes and comboboxes.

Could try code to change form mode. Let us know what happens.

[writeprivate]

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.

[Welshgasman]

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.

[AlexFim]

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

[Welshgasman]

Surely a Select Case would be more appropriate?

[data808]

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.

[data808]

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:

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.

[data808]

I just realized that Me.txtName.Locked would be a better one to use. I'm going to mess around with that more.

[Welshgasman]

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.

[data808]

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.

[Welshgasman]

You can shorten that by using the syntax in post 4, whether locked or enabled.

[madpiet]

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

[J.Kannengiesser]

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