Return all objects available on a form.

[fordboy0]

Hey all,

I have an interesting problem that I can't seem to find information on.

I have the need to create a Sub (or function or class) that will, given the form.object, return all of the objects (or controls) on that page.

The reason for this is that one of my fellow programmers has a need to make the controls on a form that have a solid border, have a transparent border, and then switch them back again. If there was a way for me to get the names of all the available objects on a form, I could do it.

Any thoughts?

-Jeff

 

[beetee]

sub ShowAllControlsOnForm(TheForm as form)
dim CurCtrl as control
for each curctrl in theform.controls
debug.print curctrl.name
next curctrl
end sub

The Access Basic help can give you information on what you can look at and change in a control. Double click the word 'control' in your code and press F1 for help on it.

You could put a call to this subroutine in your form open event handler.

HTH

 

[fordboy0]

Aha! Thanks for the info... Still kinda getting my feet wet on some of the intricacies of VBA.

"theform.controls" is considered a Collection object right?

In order to get this straight in my head... PLEASE correct me if I'm WAY off base

I would call it as such:

Command_Whatever_click()
dim CurCtrl as control
dim NumOfControls as long
dim ReturnedControls(100) as control

NumOfControls = ShowAllControlsOnForm(me.form.object,ReturnedControls)

for count=0 to NumOfControls

CurCtrl = ReturnedControls(count)
CurCtrl.(whatever property) = "MyNewValue"

next count

end sub

and the Function would be:

function ShowAllControlsOnForm(TheForm as form, ReturnedControls() as control) as long
dim CurCtrl as control
dim Count as long

for each curctrl in theform.controls

ReturnedControls(Count) = curctrl
count=count+1
debug.print curctrl.name

next curctrl

ShowAllControlsOnForm = count

end sub

Thanks again,

-Jeff

 

[beetee]

while not actually running the code, it looks like it would be ok.

one thing you may consider is using the redim preserve command to allocate your array:

dim ArraySize as integer
dim ArrayEls as integer
dim MyArray() as <whatever>

...
if (arrayels >= arraysize) then
arraysize = arraysize + <whatever>
redim perserve MyArray(arraysize)
endif
MyArray(ArrayEls) = <whatever>
ArrayEls = ArrayEls + 1
...

then you don't have to worry about changing the array dimension. Use the 'erase' command to clear out the array; if you do that don't forget to reset your arraysize and arrayels variables if you plan on reusing the array.

That being said, I don't think you really need to copy the controls into an array, just deal with them in a loop as shown in the original message.


Additionally, keep in mind that when dealing with objects you need to use 'Set' for assignments, e.g.

Set CurCtrl = array(index)

Once you get into collections and containers, you will have all the objects of the Access database at your fingertips. I have written code that converts my access modules into linked HTML documents; created search/replace routines that search out queries, forms, etc. and replaces all instances of, say, a given field name with a new name, etc.

I learned all this by reading the on-line help, and trial and error. It's kinda fun.

Good luck.

 

[SBendBuckeye]

Basically everything in Access is a collection of collections. Just to get you started, a database has among others TableDefs and QueryDefs collections which in turn have a Fields collection, etc.

For an open form, there is the Controls collection and the form itself is part of the Forms collection (open forms only so it fluctuates.

If you have a reference to DAO set, you can do things like the following:

Dim dbs As Database
Dim tdf As TableDef
Dim qdf As QueryDef

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(&quot;yourTableName&quot or

For Each tdf In dbs.TableDefs
Debug.Print tdf.Name, tdf.Fields.Count
Next tdf

Set qdf = dbs.QueryDefs(&quot;yourQueryName&quot or

For Each qdf In dbs.QueryDefs
Debug.Print qdf.Name, qdf.Fields.Count, qdf.SQL
Next qdf

dim ctl As Control
dim frm As Form

For Each ctl in Forms(&quot;YourOpenFormName&quot.Controls
Debug.Print ctl.Name
Next ctl

Good LucK!

 

[scking]

You are making a few errors is your code, which I assume you would discover during operations, and are making it way too hard.

'This works for Labels
Public Sub ChgPrp(PrpValue As Variant)
Dim i As Integer
Dim ctl As Control
For i = 0 To Me.Controls.Count - 1
If TypeOf Me.Controls(i) Is Label Then
Set ctl = Me.Controls(i)
ctl.Caption = PrpValue
End If
Next i
End Sub

RE: for count=0 to NumOfControls
Any loop using the .count would have to decrement the count by one since it is zero based. If there are 19 controls then the loop would read to 19 - 1 or 18 so the series is stored in 0 - 18.

RE: your statement that you wish to change control properties.
No two control types are required to have the same properties. You may need to use the TypeOf to determine the type of the control before you process it.

-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------