Solved What am I doing wrong...For Each

[zeroaccess]

Sub HideAllMenus()

    Dim ctrl As Control

    For Each ctrl In Screen.ActiveForm.Controls
        If ctrl.Tag = "MenuAdmin" Or "MenuReports" Then
            If Not ctrl.Visible = False Then
            ctrl.Visible = False
            End If
        End If
Next
End Sub

This works great when only one of the items is listed (for example just "MenuAdmin"), but when using OR, I get "Type mismatch". Thanks from this VBA newbie.

 

[pbaldy]

If ctrl.Tag = "MenuAdmin" Or ctrl.Tag = "MenuReports" Then

 

[zeroaccess]

If ctrl.Tag = "MenuAdmin" Or ctrl.Tag = "MenuReports" Then

Hmm, ok. Do you know of a better way to code this without having to repeat ctrl.Tag?

I've tried Len(ctrl.tag) > 0, to no avail.

 

[pbaldy]

Well, to use OR you have to repeat whatever is being tested. That may be because the second item tested may not be the same as the first. I could test "If A = 1 OR B = 2" or whatever. The Len() test would check for something being entered in the tag. In SQL you can use In() to test multiple values, but I don't think it's valid in VBA.

 

[MajP]

If you had a lot of Ors you could do a select case
select case ctrl.tag
case "MenuAdmin","menureports,"....","...."

But for two you are not saving anything.

 

[Deleted member 147267]

If InStr(1, "MenuAddinMenuReports", ctl.Tag) Then

 

[plog]

Sub HideAllMenus()
...

If you truly want to hide all menus, and all menus are named "Menu*" you could use one LIKE comparison instead.

 

[zeroaccess]

If you had a lot of Ors you could do a select case
select case ctrl.tag
case "MenuAdmin","menureports,"....","...."

But for two you are not saving anything.

There are I think 6 or 7. I just didn't think it was necessary to post them all here.

So with Select Case you're saying I could put them all on one line like that? My Select Case statements usually have:

Case Is
Abc
Case Is
Xyz

Etc

I do like the length test but I kept weird getting errors like I was referring to the visible property incorrectly.

 

[MajP]

The case statement has a lot of ways you can write the cases. Ranges, comma seperated, is expression, between, logical expressions

Select Case statement (VBA)

Office VBA reference topic

docs.microsoft.com

That has a few examples
case 1 To 4, 7 To 9, 11, 13, Is > MaxNumber

 

[zeroaccess]

It's a mystery to me why I get this:

When using this:

Sub HideAllMenus()

    Dim ctrl As Control

    For Each ctrl In Screen.ActiveForm.Controls
        If Len(ctrl.Tag) <> 0 Then
       -> If Not ctrl.Visible = False Then <-
            ctrl.Visible = False
            End If
        End If
Next
End Sub

The controls are just command buttons and lines.

I've also tried:
Not IsNull
> 0

All give the same result. I'm also getting this error if I convert the IF to a Select Case...so I take it all of these seem valid, and now we're onto something being wrong with the next line. Which is weird because this routine works if I only name one specific control - the "HIDE" works. It's also weird because my routine to "SHOW" the controls also works, and it's basically the same code:

Sub MenuAdmin()

    Dim ctrl As Control

    For Each ctrl In Screen.ActiveForm.Controls
        If ctrl.Tag = "MenuAdmin" Then
            If Not ctrl.Visible = True Then
                ctrl.Visible = True
            End If
        End If
Next
End Sub

So it's failing only when I try to act on multiple controls

 

[isladogs]

Although almost all control types can be hidden, there are exceptions
For example acPageBreak has no visible property and if you have that on your form it will cause the above code to error.
If so, you need to exclude that control type

If Not Ctrl.Type= acPageBreak Then
    For Each ....

 

[zeroaccess]

Although almost all control types can be hidden, there are exceptions
For example acPageBreak has no visible property and if you have that on your form it will cause the above code to error.
If so, you need to exclude that control type

If Not Ctrl.Type= acPageBreak Then
    For Each ....

That is a good point, but I'm having trouble using that.

If ctrl.Type = acCommandButton Then
Select Case ctrl.Tag

"Object doesn't support this property or method".

 

[isladogs]

Sounds like you may have a missing or corrupt reference. Does your code compile?

You could also look at my example app which perhaps makes this type of code more streamlined : Set Controls

 

[moke123]

ctrl.Type

Shouldn't it be ctl.ControlType?

 

[Deleted member 147267]

Not if you declared it as ctrl.
It's ControlType, not Type.

 

[moke123]

Not if you declared it as ctrl.
It's ControlType, not Type.

It's these Shi**y readers. I usually use ctl, I didnt see the r.

 

[isladogs]

Oops. Sorry about that. My code in post #11 should indeed be ctrl.ControlType.
Thanks to Moke and Micron for spotting the error

 

[The_Doc_Man]

In order to be selective about trying to dink around with control properties I often used a SELECT CASE construct based on the .ControlType, after which I had a CASE acTextBox, acComboBox, acListBox (and a couple of other things) in a comma-separated list, and the only other case was an "empty" ELSE.

All that really matters for this or any other similar approach is that as one-time thing, you check the properties of each type of control you use to verify that it in fact HAS that property. However, be aware that Access will not include all properties with a control sometimes. For instance, for an UNBOUND textbox there is no .OldValue property, and this applies to some other control types as well. Not only that, but even if you have declared an event routine, certain events won't fire. Like an unbound control won't fire update-related events and unbound forms won't fire the Current event. So when dealing with this kind of problem, just take a quick peek to see if the control or object will cooperate.

 

[MajP]

If Not ctrl.Visible = True Then
ctrl.visible = true
End If

Since you were concerned about brevity, there is no need for this if then. Simply
ctrl.visible = true
Do not need to check if it is not visible. You do not get an error setting something to visible that is already visible. Not that it makes any practical difference, but it would be slightly more efficient. You are not doing an if check, just setting a property.

If you want to toggle (true to false, false to true), instead of an if then else
ctrl.visible = not ctrl.visible

 

[zeroaccess]

In order to be selective about trying to dink around with control properties I often used a SELECT CASE construct based on the .ControlType, after which I had a CASE acTextBox, acComboBox, acListBox (and a couple of other things) in a comma-separated list, and the only other case was an "empty" ELSE.

Thanks Doc. Looks like we were thinking the same thing. I just tried this, however I "...can not hide a control that has the focus". Now I could try to find some other type of thing to hide on the form to setfocus, but there is another reason this won't work. It ends up hiding things I don't want to hide. I really need to hide only the things tied to the menus. So here is what I ended up doing:

Sub HideAllMenus()

    Dim ctrl As Control
  
        For Each ctrl In Screen.ActiveForm.Controls
            Select Case ctrl.ControlType
                Case acComboBox, acCommandButton, acLabel, acLine, acRectangle, acTextBox
                    Select Case ctrl.Tag
                        Case "MenuAdmin", "MenuHelp", "MenuHome", "MenuInspections", "MenuReports", "MenuTasks", "MenuUsers"
                            If Not ctrl.Visible = False Then
                                ctrl.Visible = False
                            End If
                        End Select
                End Select
        Next
End Sub

So a nested Case is needed here. First narrow the scope to just certain control types that we know don't have a problem with tags or the visible property, THEN specify which tags this will apply to. It works! I'll post a sample DB in a bit once I strip it down.

EDIT: Looks like I am still going to need to set focus somewhere. Any ideas? Or can I set it to nowhere?