Would like to spin through collections using For Each Loop 1

[SBendBuckeye]

Does anyone have some examples of container processing or documentation in Access? I would like to be able to use For Each ... in Container to retrieve various pieces of information.

I am looking specifically for things that would allow me to spin through the current form collection and retrieve information on objects that form contains such as control names, properties, etc. I would like to do the same thing with TableDefs?? for field names, properties, etc. and indexes and relationships.

I found a couple examples in help but it is not very intuitive to extrapolate to others. Any help would be greatly appreciated.

 

[KyleS]

I don't have any documentation, (after all that would be helpful ) but I can give you some examples:

For Controls on a form, this will test for the type of control, and if it's a text box it will make the backcolor red.

Dim ctl As Control, i As Integer
For Each ctl In Forms!frmprojects
If ctl.Properties(2) = 109 Then
ctl.BackColor = vbRed
End If
Next ctl

Control Properties:
1 Name
2 ControlType
3 ControlSource
4 Format
5 DecimalPlaces
6 InputMask
7 DefaultValue
8 ValidationRule
9 ValidationText
10 StatusBarText
11 EnterKeyBehavior
12 AllowAutoCorrect
13 Visible
14 DisplayWhen
15 Enabled
16 Locked
17 FilterLookup
18 AutoTab
19 TabStop
20 TabIndex
21 ScrollBars
22 CanGrow
23 CanShrink
24 Left
25 Top
26 Width
27 Height
28 BackStyle
29 BackColor
30 SpecialEffect
31 BorderStyle
32 OldBorderStyle
33 BorderColor
34 BorderWidth
35 BorderLineStyle
36 ForeColor
37 FontName
38 FontSize
39 FontWeight
40 FontItalic
41 FontUnderline
42 TextFontCharSet
43 TextAlign
44 FontBold
45 ShortcutMenuBar
46 ControlTipText
47 HelpContextId
48 ColumnWidth
49 ColumnOrder
50 ColumnHidden
51 Section
52 Tag
53 Text
54 SelText
55 SelStart
56 SelLength
57 InSelection
58 BeforeUpdate
59 AfterUpdate
60 OnChange
61 OnEnter
62 OnExit
63 OnGotFocus
64 OnLostFocus
65 OnClick
66 OnDblClick
67 OnMouseDown
68 OnMouseMove
69 OnMouseUp
70 OnKeyDown
71 OnKeyUp
72 OnKeyPress



For Tabledefs, try this:
Sub looptbldef()
Dim DAO.tbl As TableDef, i As Integer
For Each tbl In CurrentDb.TableDefs
'Do Something here
Next tbl
Set tbl = Nothing
End Sub


TableDefProperties:
1 Updatable
2 DateCreated
3 LastUpdated
4 Connect
5 Attributes
6 SourceTableName
7 RecordCount
8 ValidationRule
9 ValidationText
10 ConflictTable
11 ReplicaFilter
12 OrderByOn
1 Updatable
2 DateCreated
3 LastUpdated
4 Connect
5 Attributes
6 SourceTableName
7 RecordCount
8 ValidationRule
9 ValidationText
10 ConflictTable
11 ReplicaFilter
12 OrderByOn

To loop through all the fields of a table to get their names, something like this will work:

Sub looptbldef()
Dim tbl As DAO.TableDef, i As Integer, fld as DAO.Field
Set tbl = CurrentDB.TableDefs("TableName&quot
For Each fld In tbl
'Do Something here
Next fld
Set fld = Nothing
Set tbl = Nothing
End Sub

Hope this helps,
Kyle