[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 491
  • Last Modified:

problem with a formula to close all controls in a form in microsoft access 2010

my code is


but i get a compile error because vba don't recognize the  "controltype"
If Me.Closed = True Then
    Dim Ctrl As Controls
    For Each Ctrl In Me.Controls
       If Ctrl.ControlType = acTextBox Or Ctrl.ControlType = acCheckBox Or Ctrl.ControlType = acComboBox Or Ctrl.ControlType = acSubform Then
            Ctrl.Locked = True
        End If
    Next Ctrl
Else
    Dim Ctrl As Controls
    For Each Ctrl In Me.Controls
       If Ctrl = acTextBox Or Ctrl.ControlType = acCheckBox Or Ctrl.ControlType = acComboBox Or Ctrl.ControlType = acSubform Then
            Ctrl.Locked = True
        End If
    Next Ctrl
End If

Open in new window

0
bill201
Asked:
bill201
  • 2
3 Solutions
 
JVWCCommented:
Not quite sure what you wish to achieve but have you considered using:
Form.Recordlocks ?

Cheers
JC
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
This line:

Dim Ctrl As Controls

Should be:

Dim Ctrl As Control

(no "s")
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Also ... you have this Dim statement twice

Dim Ctrl As Controls

You can only the same variable Dimmed once per Function/Sub.

And in the Else case ... you have:

If Ctrl

which s/b If Ctrl.ControlType

And I suspect for the Else case you meant

Ctrl.Locked = False   ' Instead of True - which is the same as the IF part.

My guess:

    Dim Ctrl As Control
    If Me.Closed = True Then
        For Each Ctrl In Me.Controls
            If Ctrl.ControlType = acTextBox Or Ctrl.ControlType = acCheckBox Or Ctrl.ControlType = acComboBox Or Ctrl.ControlType = acSubform Then
                Ctrl.Locked = True
            End If
        Next Ctrl
    Else
        For Each Ctrl In Me.Controls
            If Ctrl.ControlType = acTextBox Or Ctrl.ControlType = acCheckBox Or Ctrl.ControlType = acComboBox Or Ctrl.ControlType = acSubform Then
                Ctrl.Locked = False
            End If
        Next Ctrl
    End If

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
And I suspect it can all be shortened to:

    Dim Ctrl As Control

    For Each Ctrl In Me.Controls
        If Ctrl.ControlType = acTextBox Or Ctrl.ControlType = acCheckBox Or Ctrl.ControlType = acComboBox Or Ctrl.ControlType = acSubform Then
            Ctrl.Locked = (Me.Closed = True) ' or (Not Me.Closed = True)
        End If
    Next Ctrl
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now