Solved

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

Posted on 2011-03-07
4
480 Views
Last Modified: 2012-05-11
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
Comment
Question by:bill201
  • 2
4 Comments
 
LVL 6

Accepted Solution

by:
JVWC earned 167 total points
ID: 35054651
Not quite sure what you wish to achieve but have you considered using:
Form.Recordlocks ?

Cheers
JC
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 167 total points
ID: 35055194
This line:

Dim Ctrl As Controls

Should be:

Dim Ctrl As Control

(no "s")
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 166 total points
ID: 35059720
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
 
LVL 75
ID: 35059750
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now