Disable All Controls Except One On Main Form, including Subform (1 Viewer)

[Phisaw]

Hi All,
I'm looking to disable/lock all controls, except search text box, including subform control. Currently, I'm using the code below and added the 'TypeOf ctrl is Subform', but am still able to enter data into the subform. I have users who occasionally enter data in the wrong field when the form opens and I need to disable as much as I can and force them to enter the needed info into the search field "NavJobSelect".

Dim Ctrl As Control

If Me.NewRecord Then
For Each Ctrl In Me.Controls
If (TypeOf Ctrl Is TextBox) Or (TypeOf Ctrl Is CheckBox) Or (TypeOf Ctrl Is ComboBox) Or (TypeOf Ctrl Is SubForm) Then
If Ctrl.Tag <> "DoNotLock" Then
Ctrl.Enabled = False
Ctrl.Locked = True
DoCmd.GoToControl ("NAVJobSelect")
Else
Ctrl.Enabled = True
Ctrl.Locked = False
End If
End If
Next
Else
For Each Ctrl In Me.Controls
If (TypeOf Ctrl Is TextBox) Or (TypeOf Ctrl Is CheckBox) Or (TypeOf Ctrl Is ComboBox) Or (TypeOf Ctrl Is SubForm) Then
Ctrl.Enabled = True
Ctrl.Locked = False
End If
Next
End If

End Sub

Will someone please tell me how to include the subform with all controls?
Any help is greatly appreciated.
Thanks in advance,
Pam

 

[theDBguy]

Hi Pam. You're talking about when the form is on a new record, right? Otherwise, you want the subform unlocked, correct? Also, why do you tag the control "DoNotLock" and then disable it if it has that tag? Just curious...

 

[Phisaw]

Hi DBGuy,
Thanks for the prompt reply. After much searching and trials of various codes, I ran across the code posted here: https://www.access-programmers.co.u...w-edits-to-some-fields-but-not-others.182661/ by Missinglinq.

The original code had 'If Not Me New Record', but I needed mine to be if it was a new record, so I dropped the 'Not" and added the Typeof control Subform. All worked well, until I added the subform control, as I can still enter the subform and enter data.

I also tried Bob Larson's solution here: https://www.access-programmers.co.u...thing-on-form-except-for-one-combobox.207138/ but kept getting object variable errors, that I couldn't resolve.

If this isn't the way to handle this, or there is a better approach, I would love to hear your solution(s).
Thanks again for your time and help.
Pam

 

[Micron]

You should tell us at a high level what it is you are trying to do, rather than what you've tried. If you disable a subform control thus all of it's controls you could end up with a record in edit mode and no way to release it. It all depends. Tell us more about the process, because without that knowledge it is difficult to say what method could possibly be better.

I need to disable as much as I can and force them to enter the needed info into the search field "NavJobSelect".

It could be as simple as designing the form with all controls disabled but one. Then again, without knowing anything about the process, that may not be viable.

 

[Phisaw]

I have a form with a search textbox.
The form has a subform.
To prevent data errors on new Record when form opens - disable/lock all controls on main and subform, except search textbox.
When record is found, enable/unlock all controls on main and subform, including search textbox.
Does this help?
Thanks,
Pam

 

[Micron]

Umm, sort of but also no. The code you showed applies if on a new record, but now you're saying 'when form opens'. If you're on a new record, then you're in data entry mode, so why go there if you want to search instead? It sounds like you're mixing functions - data entry and record searching. Also, when a bound form opens, it won't open to a new record as long as there is already at least one record behind it - unless you code to avoid that.

Suppose we start with the user opening the form.
Does it have a record source defined at that time and are records visible?
What do I do next - search for and display a particular record? Then why not just open the form to that record from a search form? That way, you can choose to lock the record or not at that time. You can also deal with the user getting no record based on the search criteria. With your current approach, I don't think you've allowed for that. I'm just not grasping the idea for doing searches when the form is on a new record. It implies that you're not allowing a search when on an existing record, which is something you haven't addressed. Did you check that the subform is/isn't locked when on a new record using your current code?

Anyway, you've asked 2 questions:
- how to refer to the subform control? < Forms!NameOfFormHere.Controls!NameOfSubformControlHere.Enabled = False (or True)
- is there a better way?
The jury is still out on that one. I do think your code can be compressed though. How depends on the process, which I still don't understand.

 

[Micron]

Re: code 'compression' I had in mind something like

Dim ctl As Control

For Each ctl In Me.Controls
    Select Case ctl.ControlType
        Case acTextBox, acCheckBox, acComboBox, acSubform
            If ctl.Tag <> "DoNotLock" Then ctl.Enabled = False
            If ctl.Tag = "DoNotLock" Then ctl.Enabled = True
        Case Else
    'do nothing?
    End Select
Next

I don't see the point in looping through twice; nor is it necessary to lock and disable. If disabled, locking it has no effect. Like the db_guy said, the tag seems to be the opposite of what you do to the control.

 

[arnelgp]

convert your code into a function:

Option Compare Database
Option Explicit

Public Function EnableDisableControlsOnForm(ByRef F As Form)

Dim Ctrl As control

If F.NewRecord Then
    For Each Ctrl In Me.Controls
        If (TypeOf Ctrl Is textbox) Or (TypeOf Ctrl Is CheckBox) Or (TypeOf Ctrl Is ComboBox) Or (TypeOf Ctrl Is SubForm) Then
            If TypeOf Ctrl Is SubForm Then
                Call EnableDisableControlsOnForm(Ctrl.Form)
              
            Else
                If Ctrl.Tag <> "DoNotLock" Then
                    Ctrl.Enabled = False
                    Ctrl.Locked = True
                    'DoCmd.GoToControl ("NAVJobSelect")
                Else
                    Ctrl.Enabled = True
                    Ctrl.Locked = False
                End If
            End If
        End If
    Next
Else
    For Each Ctrl In F.Controls
    If (TypeOf Ctrl Is textbox) Or (TypeOf Ctrl Is CheckBox) Or (TypeOf Ctrl Is ComboBox) Or (TypeOf Ctrl Is SubForm) Then
  
        If TypeOf Ctrl Is SubForm Then
            Call EnableDisableControlsOnForm(Ctrl.Form)
      
        Else
            Ctrl.Enabled = True
            Ctrl.Locked = False
        End If
    End If
    Next
End If

End Function

then on the Current Event of the Form, call it:

Private Sub Form_Current()
   Call EnableDisableControlsOnForm(Me)
End Sub

the function will also take care of the Subform.
on the subform, add the Tag to to appropriate control.

 

[Phisaw]

Thank you Arnelgp for supplying the function. It works as I asked, but will you please tell me how to unlock the controls,
including subforms, when I click on a button I have set to open a new form.

Micron suggested I give detailed information for what I was trying to do.
I wanted to avoid a lengthy explanation, but now I believe it may be necessary.

I have a switchboard with a command button "Repairs" to open a repair form, with various controls, including subforms.
On the left side of the repair form, I have a series of command buttons to open other forms, etc.
One of these is a textbox used to search for existing job numbers.
Another is a command button to open a new record.

Previously, I had a command button that when user was on a record, they could "Select Job" from a command button
and have the option to open an existing number or go to a new record from a pop-up form.
I did not like the Access default message form or the pop-up idea - just more "OK" and "Cancel" buttons to click through.

So I decided to separate the two and keep them on the form, instead of a separate pop-up search form.
If I allow the form to open with an existing record, it confuses users, so I set it to open to a blank form.

I have users who occasionally overwrite data when the form is first opened, so I have many "traps" set to avoid wrong entries,
make sure all data is filled in correct sequence, etc., and the reason to disable all fields except the search box and the
command button to open a new record.

Below is the code used for BeforeUpdate event, and command buttons for Close and New Job, and Search Textbox.

After much research on others with the same problem - eliminating data overwrite upon form opening,
I thought locking the form until the user either entered the job number they were searching for in the search textbox or
clicking 'Enter New Job' command button to open form to a new record would be the way to go.

If you have any suggestions that may handle this better, please let me know. Your suggestions are greatly appreciated.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Dirty And Len(Me.JobNumber & vbNullString) = 0 Then
MsgBox "Before Update - You need the JobNumber"
Cancel = True
Me.JobNumber.SetFocus
 End If

Private Sub NAVcmdClose_Click()
If Me.Dirty And IsNull(Me.JobNumber) Then
MsgBox "NAV Close - Job Number is Required."
Cancel = True 'wont let the user move to the next record
 Me.JobNumber.SetFocus

Private Sub NAVJobSelect_AfterUpdate()

Dim strSql As String
Dim strInput As String
Dim vCheck As Variant
Dim sWhere As String
Dim sMsg As String
Dim sMsgHdr As String

If Me.Dirty And IsNull(Me.JobNumber) Then
MsgBox "Job Select After Update - Need Job."
Me.JobNumber.SetFocus
Else
CheckJobNumber:
vCheck = ""
sWhere = "Jobnumber = " & NAVJobSelect
vCheck = DLookup("Jobnumber", "trepair", sWhere)
If IsNull(vCheck) Or vCheck = "" Then
DoCmd.OpenForm "frepairjobselectwithmessage"
  Else

  strSql = "SELECT qrepair.* " & _
" FROM qrepair " & _
" WHERE qrepair.Jobnumber = " & NAVJobSelect & ";"
    Me.RecordSource = strSql

  End If

   NAVJobSelect = ""
End If

End Sub

Private Sub NAVcmdNewJob_Click()
If Me.Dirty And IsNull(Me.JobNumber) Then
MsgBox "NAV cmd new job - Need Job."
Me.JobNumber.SetFocus
Else
DoCmd.GoToRecord , , acNewRec
End If
End Sub

As always, thank you for your time and help.
Pam

 

[arnelgp]

try to call the function EnableDisableControlsOnForm(), and now pass a 2nd parameter (True) to force lock the controls on the form:
the syntax:

Call EnableDisableControlsOnForm(theFormToLock, True)

you don't need to modify the previous code that calls this function, it will do the "normal locking/unlocking".
replace your function with this one:

Public Function EnableDisableControlsOnForm(ByRef F As Form, Optional ByVal ForceLock As Boolean = False)

Dim Ctrl As Control

If F.NewRecord Or ForceLock Then
    For Each Ctrl In Me.Controls
        If (TypeOf Ctrl Is textbox) Or (TypeOf Ctrl Is CheckBox) Or (TypeOf Ctrl Is ComboBox) Or (TypeOf Ctrl Is SubForm) Then
            If TypeOf Ctrl Is SubForm Then
                Call EnableDisableControlsOnForm(Ctrl.Form, ForceLock)
             
            Else
                If ForceLock Then
                    Ctrl.Enabled = False
                    Ctrl.Locked = True
                Else
                    If Ctrl.Tag <> "DoNotLock" Then
                        Ctrl.Enabled = False
                        Ctrl.Locked = True
                        'DoCmd.GoToControl ("NAVJobSelect")
                    Else
                        Ctrl.Enabled = True
                        Ctrl.Locked = False
                    End If
                End If
            End If
        End If
    Next
Else
    For Each Ctrl In F.Controls
      If (TypeOf Ctrl Is textbox) Or (TypeOf Ctrl Is CheckBox) Or (TypeOf Ctrl Is ComboBox) Or (TypeOf Ctrl Is SubForm) Then
   
          If TypeOf Ctrl Is SubForm Then
              Call EnableDisableControlsOnForm(Ctrl.Form)
       
          Else
              Ctrl.Enabled = True
              Ctrl.Locked = False
          End If
      End If
    Next
End If

End Function

 

[Phisaw]

Hi Arnelgp - Thank you again for replying and providing additional code.

I still cannot get the form to unlock when the New Job button is clicked.

I have tried the following for OnCurrent:
EnableDisableControlsOnForm Me, True
Call EnableDisableControlsOnForm(Me, True)
Call EnableDisableControlsOnForm(Me)

NewJob Command Button has the following and I tried Call Enable...(False) hoping it would unlock from this button

Private Sub NAVcmdNewJob_Click()
If Me.Dirty And IsNull(Me.P21Number) Then
MsgBox "NAV cmd new job - Need P21."
Me.P21Number.SetFocus
Else
DoCmd.GoToRecord , , acNewRec
Call EnableDisableControlsOnForm(Me, False)
End If
End Sub

OnLoad is set to go to new record

I don't understand your statement of:
you don't need to modify the previous code that calls this function, it will do the "normal locking/unlocking".
Which previous code do I not need to modify?

Again, I appreciate your time and help. If you can please tell me what I'm doing wrong, I would greatly appreciate it
Thanks again,
Pam

 

[arnelgp]

another change in code, this time you pass True to "force unlock" the form.

Public Function EnableDisableControlsOnForm(ByRef F As Form, Optional ByVal ForceUnlock As Boolean = False)

Dim Ctrl As Control

If ForceUnlock Then
    For Each Ctrl In F.Controls
        If (TypeOf Ctrl Is textbox) Or (TypeOf Ctrl Is CheckBox) Or (TypeOf Ctrl Is ComboBox) Or (TypeOf Ctrl Is SubForm) Then
            If TypeOf Ctrl Is SubForm Then
                Call EnableDisableControlsOnForm(Ctrl.Form, ForceUnlock)
              
            Else
                Ctrl.Enabled = True
                Ctrl.Locked = False
            End If
        End If
    Next
    
ElseIf F.NewRecord Then
    For Each Ctrl In F.Controls
        If (TypeOf Ctrl Is textbox) Or (TypeOf Ctrl Is CheckBox) Or (TypeOf Ctrl Is ComboBox) Or (TypeOf Ctrl Is SubForm) Then
            If TypeOf Ctrl Is SubForm Then
                Call EnableDisableControlsOnForm(Ctrl.Form, ForceUnlock)
              
            Else
                If Ctrl.Tag <> "DoNotLock" Then
                    Ctrl.Enabled = False
                    Ctrl.Locked = True
                    'DoCmd.GoToControl ("NAVJobSelect")
                Else
                    Ctrl.Enabled = True
                    Ctrl.Locked = False
                End If
            End If
        End If
    Next
Else
    For Each Ctrl In F.Controls
        If (TypeOf Ctrl Is textbox) Or (TypeOf Ctrl Is CheckBox) Or (TypeOf Ctrl Is ComboBox) Or (TypeOf Ctrl Is SubForm) Then
            If TypeOf Ctrl Is SubForm Then
                Call EnableDisableControlsOnForm(Ctrl.Form, ForceUnlock)
              
            Else
                Ctrl.Enabled = False
                Ctrl.Locked = True
            End If
        End If
    Next

End If
End Function

on your form add a Form-wide variable:

Option Compare Database
Option Explicit

Dim bolUnlock As Boolean

Private Sub Form_Current
    Call EnableDisableControlsOnForm(Me, bolUnlock)
    bolUnlock = False
End Sub

Private Sub NAVcmdNewJob_Click()
If Me.Dirty And IsNull(Me.P21Number) Then
    MsgBox "NAV cmd new job - Need P21."
    Me.P21Number.SetFocus
Else
    bolUnlock = True
    DoCmd.GoToRecord , , acNewRec
End If
End Sub