Loop: Each subform in Form

[mbreu996]

Greetings,

Well this sure is a simple question and I'm embarrassed I haven't gotten it right yet, but a few minutes of your time will certainly solve my issue. All i need is the following pseudo code translated into actual working VBA code. For each value in a public array(1), I am attempting to loop through all fields(3) within all the subforms(2) on one particular form. If there is a match, the field is visible, if not, the field is hidden (I am using datasheet view).

Dim i as long

1)For each i = 0 to Ubound(PublicArray)
2)For each SUBFORM in PARENTFORM
3)For each CONTROL in SUBFORM
If CONTROL.name = PublicArray(i) Then
CURRENTCONTROL.ColumnHidden = False
Else
CURRENTCONTROL.ColumnHidden = True
End If
Next
Next
Next

Thanks!

 

[ajetrumpet]

i have no idea if this works in datasheet view, but if it does, then this code is relevant:

dim i as long
dim cSub as control
dim cCon as control

for i=0 to ubound(YOUR ARRAY HERE())
  for each cSub in forms("mainform name").controls
    if typeof cSub is subform then
      for each cCon in forms(forms("mainform name").controls(cSub).sourceobject)
        if cCon.name=YOUR ARRAY HERE(i) then
          cCon.visible=false
          exit for
        else
          cCon.visible=true
        end if
      next cCon
    end if
  next cSub
next i

i don't know if this works, but it is worth a try. if it doesn't work, a variation of it will. i don't have time to test it in a test database. sorry. another thing ... this will probably not work if you have the main form OPEN when running the code.

 

[vbaInet]

I'm hoping this wouldn't be a function that would be performed many times? This is because of the level of looping you're doing, the number of inner loops you've asked for.

 

[ajetrumpet]

I'm hoping this wouldn't be a function that would be performed many times? This is because of the level of looping you're doing, the number of inner loops you've asked for.

isn't that code I posted though AWESOME?

sometimes the only reason I offer a solution is to see the smooth flow of indentation incrementing along the left wall of the code. that just looks so NEAT!

 

[vbaInet]

haha!! yeah the colours are fab. I keep forgetting the php tags exist. props to Adam lol

 

[ajetrumpet]

haha!! yeah the colours are fab. I keep forgetting the php tags exist. props to Adam lol

the one thing I like about you is that you always HAVE to reply to something. laughing...

I wonder if I should make a poll on how many OP's actually:

1) Realize that PHP color coding means something
2) Actually notice it
3) Don't know the difference between tag types
4) Don't even care, as long as the words make it work

 

[vbaInet]

Hmmmm... might be a good idea. Go for it. Since you go on the PHP section alot, do people on there actually use the php tags?

 

[ajetrumpet]

Hmmmm... might be a good idea. Go for it. Since you go on the PHP section alot, do people on there actually use the php tags?

yes they do. I've never seen CODE wrapped around PHP syntax. In other PHP forums, where there is nothing else but server side languages to talk about, I've never seen CODE used either. Other forums though are a lot stricter than this one is. It's painfully obvious.

 

[vbaInet]

Newbies may get confused when they see a solution wrapped in PHP code hehe!

Get the poll started.

 

[ajetrumpet]

Newbies may get confused when they see a solution wrapped in PHP code hehe!

Get the poll started.

I think I will pass on the poll. I'm not interested in some of the responses it will generate. I think my enemy base is expanding here. But hey, a friend of mine told me the other day told me that if I have no enemies, than I'm making no effort to fight to stay alive. I would rather have them. You're welcome to join the party sir, but I should warn you that I have a rifle in my basement.

 

[vbaInet]

Fan base I would call it lol. The party is not for me

 

[mbreu996]

Thank you for the response. I haven't had any luck using the .visible property of a control, text box, etc with datasheet view (works with other views like single form). This code works (sorry no fancy colors).

Me.TESTTABLE1_sub.Controls("ControlName").ColumnHidden = True

But I want to be able to refer to the control just as "the current control" rather then its exact name.

Any ideas? Thanks!

 

[ajetrumpet]

Thank you for the response. I haven't had any luck using the .visible property of a control, text box, etc with datasheet view (works with other views like single form). This code works (sorry no fancy colors).

Me.TESTTABLE1_sub.Controls("ControlName").ColumnHidden = True

But I want to be able to refer to the control just as "the current control" rather then its exact name.

Any ideas? Thanks!

I don't think we are connecting, although iNET can maybe read you better than I can. I will try to help more if you upload the database. I will do it myself for you if the instructions for the problem form, where to find it, is simple enough.

 

[vbaInet]

In datasheet view you cannot use the Visible property of a control, you can only use the ColumnHidden property. Take for example, in Excel you cannot hide a cell, you hide the Column.

 

[ajetrumpet]

In datasheet view you cannot use the Visible property of a control, you can only use the ColumnHidden property.

THERE YA GO! Like I said, don't take my advice...

 

[vbaInet]

THERE YA GO! Like I said, don't take my advice...

Your efforts are futile. The OP had already taken your (good) code/advice from before.

I'm sure mbreau996 values your input.

 

[mbreu996]

Thanks! I guess my question has been narrowed down to this:

How can I use the ColumnHidden property of the "current control"?

Dim cntrl as control
Dim i as publicarray()

For i = 0 to ubound(publicarray)
For each cntrl in FORM
If cntrl.name = publicarray(i) Then
cntrl.ColumnHidden = False 'this line generates error
Else
cntrl.ColumnHidden = True 'this line generates error
End If
Next
Next

This should work right?

 

[ajetrumpet]

see post #13. good luck!

 

[vbaInet]

Three things:

1. Dim your publicArray() as a String
2. FORM isn't set to anything
3. Where is the code being run from, a module or the form itself?

 

[mbreu996]

1) it is a public variable already defined out of this sub
2) I got lazy with this - it is the parent form = Forms!F_labnew
3) module