How do I say all objects below this object

[Zak14]

I want to assign a variable that points to each object (i.e. textboxes, subforms, buttons, labels, etc) below a specific object (let's call it "Object1") so that when I use this variable to do something, it does it to all those objects below Object1.

 

[pr2-eugin]

How about giving another shot at explaining? This time just use simple words. Using Access terminology makes it less understandable.

 

[vbaInet]

This is a vague statement. What should it do? What do you mean by a variable that points to each object?

First tell us what the whole idea behind this exercise is and we can look at better solutions.

 

[Zak14]

Okay, forget the variable thing.

I'm making an expand/collapse system for my subforms,
so when a subform is collapsed, everything below it moves up; when it is expanded, everything below it moves down.

How do I say "everything below the subform".

 

[vbaInet]

You can use the Tag property to identify those controls and you'll need to loop through all the controls on the form using the form's Controls collection.

 

[Zak14]

Sorry, I'm not familiar with what you're saying.

What's a tag property and what does the controls section refer to?

 

[vbaInet]

You want to do some a bit advanced you need to be willing to do some research. Have you Googled to see what they are?

 

[Zak14]

Well yeah I know what the tag property is, but I don't know what it's used for, and I don't understand how to "loop through all the controls on the form using the form's Controls collection" and how I use it with VBA.

Sorry, I'm not quite there yet with my advanced Access skills, but 2 weeks ago, I knew almost nothing, so allow me if I don't understand everything google says; now though, I kind of understand VBA. I'm rapidly learning.You've helped me out a lot in this forum too.

 

[vbaInet]

The Tag property can be used to store any extra information about a control. So in your case you would want to enter something like "hideme" without the quotes. Whilst you're looping through each control, you check against its Tag property for the word "hideme" and perform the necessary actions.

Does this make sense?

 

[Zak14]

Yes, so I've got all tag properties of the objects I want to moveup set as "belowsubform". Is there a way I can in VBA say, "All objects with the tag belowsubform", without doing the loop thing?

 

[vbaInet]

Yes, so I've got all tag properties of the objects I want to moveup set as "belowsubform". Is there a way I can in VBA say, "All objects with the tag belowsubform", without doing the loop thing?

No! You have to loop. Search the forum (or the net) for this topic as it's been covered a lot of times and come back with any questions.

 

[Zak14]

I tried looking into the loop thing and I tried altering the code, but I just don't fully understand it; I can't make it work

Below is my code before everything though:

I've simplified the code of the button click with the parts that are necessary and renamed the page items to make it easier to read.

ObjectBelow1,2,3 are a few of the many ObjectBelows which I want to move positions with the button click.

How do I reference all the ObjectBelows in my form together?

P.S. I have all ObjectBelows ready with a tag: "BelowSubform1"

Private Sub cmdCollapseSubform1_Click()
    Subform1.Visible = False
    ObjectBelow1.Top = ObjectBelow1.Top - Subform1.Height
    ObjectBelow2.Top = ObjectBelow2.Top - Subform1.Height
    ObjectBelow3.Top = ObjectBelow2.Top - Subform1.Height
End Sub

 

[vbaInet]

To loop through controls you would do something like this:

dim ctl as control

for each ctl in me.controls
    if ctl.tag = "belowsubform"
        ... do whatever you want with ctl here ...
    end if
next

If you want to ask about how you create an expand/collapse scenario that's a different topic altogether and to be honest probably too complicated for you because there are several variables to consider.

I would advise that you understand the basics of Access and VBA before you start thinking about cosmetics or fancy stuff like expand/collapse. You need the basic knowledge to be able complete complex tasks.

 

[Zak14]

Okay, but how do I reference the objectbelows this way then? Is it not possible this way or do I need to take a different approach that is too complicated for me.

I've already made the expand/collapse system (if I don't mind referencing every object individually). I just need a way to reference them individually, but in 1 line.

 

[vbaInet]

Okay, but how do I reference the objectbelows this way then?

Have a look at my code again. It answers your question.

 

[Zak14]

I've tried the following; it doesn't work. That's why I asked.
Everything goes all over the place when I click the button.

Private Sub cmdCollapseSubform1_Click()
    Dim ctl As Control
    For Each ctl In Me.Controls
        If ctl.Tag = "belowsubform" Then
            Subform1.Visible = False
            ctl.Top = ctl.Top - Subform1.Height
        End If
    Next
End Sub

 

[vbaInet]

1. What do you mean by "everything goes all over the place"? Does it move up too high or too low?
2. Is it moving the right controls?

 

[Zak14]

Sorry, I put the hide subform command before the "For" command and it works now.

Private Sub cmdCollapseSubform1_Click()
    
    Dim ctl As Control
    
    Subform1.Visible = False
    
    For Each ctl In Me.Controls
        If ctl.Tag = "belowsubform" Then
                ctl.Top = ctl.Top - Subform1.Height
        End If
    Next
    
End Sub

 

[vbaInet]

Good to hear!

 

[Zak14]

I thought I'd share my learnings. Here's how to make an Expand/Collapse system for a Section of your page or a Subform