Solved What am I doing wrong...For Each (1 Viewer)

zeroaccess

Active member
Local time
Today, 04:24
Joined
Jan 30, 2020
Messages
671
NGINX:
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

Wino Moderator
Staff member
Local time
Today, 02:24
Joined
Aug 30, 2003
Messages
36,118
If ctrl.Tag = "MenuAdmin" Or ctrl.Tag = "MenuReports" Then
 

zeroaccess

Active member
Local time
Today, 04:24
Joined
Jan 30, 2020
Messages
671
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

Wino Moderator
Staff member
Local time
Today, 02:24
Joined
Aug 30, 2003
Messages
36,118
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

You've got your good things, and you've got mine.
Local time
Today, 05:24
Joined
May 21, 2018
Messages
8,463
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.
 

Micron

AWF VIP
Local time
Today, 05:24
Joined
Oct 20, 2018
Messages
3,476
If InStr(1, "MenuAddinMenuReports", ctl.Tag) Then
 

plog

Banishment Pending
Local time
Today, 04:24
Joined
May 11, 2011
Messages
11,611
Code:
Sub HideAllMenus()
...

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

zeroaccess

Active member
Local time
Today, 04:24
Joined
Jan 30, 2020
Messages
671
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.
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:24
Joined
May 21, 2018
Messages
8,463
The case statement has a lot of ways you can write the cases. Ranges, comma seperated, is expression, between, logical expressions
That has a few examples
case 1 To 4, 7 To 9, 11, 13, Is > MaxNumber
 

zeroaccess

Active member
Local time
Today, 04:24
Joined
Jan 30, 2020
Messages
671
It's a mystery to me why I get this:

2455.png


When using this:

SQL:
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:

SQL:
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 :unsure:
 

isladogs

MVP / VIP
Local time
Today, 09:24
Joined
Jan 14, 2017
Messages
18,186
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
Code:
If Not Ctrl.Type= acPageBreak Then
    For Each ....
 

zeroaccess

Active member
Local time
Today, 04:24
Joined
Jan 30, 2020
Messages
671
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
Code:
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

MVP / VIP
Local time
Today, 09:24
Joined
Jan 14, 2017
Messages
18,186
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
 

Micron

AWF VIP
Local time
Today, 05:24
Joined
Oct 20, 2018
Messages
3,476
Not if you declared it as ctrl.
It's ControlType, not Type.
 

isladogs

MVP / VIP
Local time
Today, 09:24
Joined
Jan 14, 2017
Messages
18,186
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

Immoderate Moderator
Staff member
Local time
Today, 04:24
Joined
Feb 28, 2001
Messages
26,996
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

You've got your good things, and you've got mine.
Local time
Today, 05:24
Joined
May 21, 2018
Messages
8,463
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

Active member
Local time
Today, 04:24
Joined
Jan 30, 2020
Messages
671
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:

SQL:
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?
 

Users who are viewing this thread

Top Bottom