Enter password then unlock all controls in Main form and in Subforms

[Seiquo]

Hello Forum,



I have been searching the net (and finding answers that give me error messages) and am about to say goodbye to any sanity I have left…

Context : I have a main form (MainForm) based on a query, with:

• Unbound controls at the top that allow users to filter data – UNLOCKED by default
• Tab control (5 tabs)
• Textboxes, comboboxes, checkboxes scattered in the MainForm and the tabs - all of those are set to LOCKED by default.
• Subforms, whose controls are set to LOCKED by default, scattered in the tabs

I have a button on said MainForm that prompts a Password window and when you have entered the right password and clicked ok, you should be able to change any data you need.
Here is the code I have for the Ok button:

Private Sub btnOK_Click()
 
If IsNull(Me.txtPassword) Then
MsgBox "Enter password", vbInformation
Me.txtPassword.SetFocus
Exit Sub
End If
If Me.txtPassword = "Secret" Then
 
Forms!MainForm.FiltreBail.SetFocus
 
DoCmd.Close acForm, "Fm_Password"
 
With Forms![MainForm]
Dim ctl As Control
    For Each ctl In Forms![MainForm].Controls
        If TypeOf ctl Is TextBox Or TypeOf ctl Is CheckBox Or TypeOf ctl Is ComboBox Then
        ctl.Locked = False
        ElseIf TypeOf ctl Is CommandButton Then
        ctl.Visible = True
        End If
    Next
 
End With
  
blnPasswordOK = True
Else
MsgBox "Incorrect password.", vbExclamation
Me.txtPassword.SetFocus
End If
 
End Sub

I am facing two problems:

1. Controls on my last tab (5) don’t unlock and I am unable to modify anything in this tab (4 combos and 8 textboxes).
2. I have been trying for two day to add code to unlock controls in my subforms with pieces of code I pick here and there but it is a fail each time: either I don’t get any error message but nothing happens, either I get the 2465 or 2467 error message.

Here are a few of the examples I adapted to my case:

Me.[Subform1].Form.[Controlname1].Locked = False
…
Me.Controls("Subform1").Form.Controls("Controlname1").Locked = False
…
With Forms![MainForm]!Subform1
    .Form! Controlname1.Locked = False
    .Form!Controlname2.Locked = False
    .Form!Controlname2.Locked = False
End With

May I add that I know enough to adapt any code to my projects but not enough to get myself out of that kind of trouble

Thank you very much in advance.

S.

[June7]

If form is in Datasheet or Continuous view, all instances of controls will be affected for all records - but I gather that is what you want. However, could use Conditional Formatting to enable/disable textboxes and comboboxes. Set the value of a textbox (can be hidden) in the main form header as a result of the password prompt. Reference that textbox in Conditional Formatting.

Could enable/disable subform container control instead of individual controls on form.

[Micron]

You're saying that code unlocks all necessary controls on 4 out of 5 tab control pages, including any of those 4 than contain subforms, but on the 5th, no? I can't make sense out of that because your code is only written for controls on the main form. Regardless, IIRC subforms on tabs aren't, actually. Their parent is the main form, not the page or the tab control. So if you simply want to make the subform control locked instead of looping through a bunch of controls on a subform on every page then
Me.SubformControlName.Endabled = False

[Seiquo]

Good morning June7 and Micron,

First thank you for your time and help.

I just solved my first problem (controls on tab 5): the control source was wrong…

Micron, I tried to add your suggestion to my code with my first subform (Me.CtlFm_Options.Enabled = True) and I get the message "Method or Data member not found". VBA points to the ".CtlFm_Options" part .

Do you have any idea of what is going wrong? Thanks!

[Micron]

What is Ctlfm_Options, subform or subform control?

[Seiquo]

Subform control. The name I gave in the "Other" tab and "Name" row of the properties window.

[Micron]

It worked for me. However, in one code example you use Me and in another you use the Forms! reference. You cannot use Me in code to refer to a control on a form or report unless the code is on the same form or report as the control. You have to use the Forms! syntax. I'd guess that is the issue.

[Seiquo]

Good morning Micron,

I totally agree with you. What I was trying to say is that I tried both: Me and the forms reference. Nothing works, it either does nothing (no error message but no unlocked controls) or I get error messages.
I am aware that the code is triggered form my Enter_password form, and I suspect this is the cause of the problem but I really don't know where to go from there .

The last (but not prefered) solution is to create exactly the same form with all controls unlocked and that form will be opened from the Enter_password form to allow changes. I am just reluctant to do so though because I will have to maintain two forms instead of one when users ask for modifications to the form (add controls, change the layout, etc.).

*sigh*

A few minutes later…

I am thinking of adding something like

With Forms![MainForm]
Dim ctl As Control
    For Each ctl In Forms![MainForm].Controls
        If TypeOf ctl Is TextBox Or TypeOf ctl Is CheckBox Or TypeOf ctl Is ComboBox Then
        ctl.Locked = False
        ElseIf TypeOf ctl Is CommandButton Then
        ctl.Visible = True
        ElseIf TypeOf ctl Is subform Then
        ctl.enabled = True

        End If
    Next

Just tried it… Doesn't work either: no error message but I'm unable to change data in my subforms.

[Seiquo]

This is my conclusion:

Just in case somebody faces the same "problem": I found out that all the subforms properties were set to NO (allow additions, allow deletions, allow edits, etc.). That's what prevented me from changing data…

So… I set them to YES (except Data Entry) and disabled the subform control on main form, instead of locking each of their controls by default, as suggested by June7 (this solution was not suitable for the other controls though, for many reasons).

The final code that works for me is as follows:

Private Sub btnOK_Click()
 
If IsNull(Me.txtPassword) Then
MsgBox "Enter password", vbInformation
Me.txtPassword.SetFocus
Exit Sub
End If
If Me.txtPassword = "Secret" Then
 
Forms!MainForm.FiltreBail.SetFocus
 
DoCmd.Close acForm, "Fm_Password"
 
With Forms![MainForm]
Dim ctl As Control
    For Each ctl In Forms![MainForm].Controls
        If TypeOf ctl Is TextBox Or TypeOf ctl Is CheckBox Or TypeOf ctl Is ComboBox Or TypeOf ctl is SubForm Then
        ctl.Locked = False
        ctl.Enabled = True
        ElseIf TypeOf ctl Is CommandButton Then
        ctl.Visible = True
        End If
    Next
 
End With
  
blnPasswordOK = True
Else
MsgBox "Incorrect password.", vbExclamation
Me.txtPassword.SetFocus
End If

Thank you both for your help, and my apologies for making you spend time on a beginner's fault…

Have a great day.