Using For Each...Next Loop

[kermit5]

I am designing a wizard that enables the user to select a series of fields to place on a custom table. The selected fields are stored in tblSelectedFields in the order that the user selects. Their pre-determined properties (i.e. control size, control type, control name, etc) are stored in tblAvailableFields. The custom form template is frmCustomTakeoff with a record source of qryCreateForm.

I am trying to create a For Each...Next loop to cylce through the selected fields and place them on frmCustomTakeoff. I don't seem to understand how to properly address the element and the group.

I want the loop to execute as long as there is a field left in my query. I think that FieldName is my element and that qryCreateForm is by group. Is this correct? If so, what is the proper syntax to set up my loop. If this is not correct, what am I missing?

Here is my code:
Private Sub cmdDetailsNext_Click()
Dim frm As Form
Dim ctlLabel As control, ctlControl As control, ctlName As Variant
Dim intLabelX As Integer, intLabelY As Integer
Dim intDataX As Integer, intDataY As Integer
Dim intTabIndex As Integer

' Set project name value
ProjectName = Forms!frmDetails!txtProjectName

'Open "frmCustomForm" form in design view
DoCmd.OpenForm "frmCustomTakeoff", acDesign

' Set positioning values for new controls
intTabIndex = 0
intLabelX = 100
intLabelY = 100
intDataX = 1000
intDataY = 100
For Each ctlName In qryCreateForm
ctlName = [qryCreateForm]![FieldTitle]
Set ctlControl = CreateControl("frmCustomTakeoff", [qryCreateForm]![FieldType], , "", "", intDataX, intDataY)
'Create child label control for text box
Set ctlLabel = CreateControl("frmCustomTakeoff", acLabel, , ctlControl.Name, "New Label", intLabelX, intLabelY)
'Restore from
DoCmd.Restore
Next
End Sub

Thanks in advance.
Scott

 

[glynch]

Look in Help about the Fields collection of a Table object.

You are going to need to do something like:

dim fldField as Field
dim tblTable as Table
dim dbsCurrent

set dbsCurrent = currentdb
set tblTable = dbsCurrent. tables("YourTable")

for each fldField in tblTable.fields
ctlName = fldField.name
etc....

I am winging the syntax, check Help for details.

HTH

 

[charityg]

The first thing that jumps at me is that your code won't know what qryCreateForm is. It seems like what you need to do is create a recordset from the query and loop through that.

Also you would refer to each FIELD in the recordset, and I think if you are adding more than one control, you should change the x and y positions, or all of your controls will site on top of one another.

Dim frm As Form
Dim ctlLabel As Control, ctlControl As Control, ctlName As Variant
Dim intLabelX As Integer, intLabelY As Integer
Dim intDataX As Integer, intDataY As Integer
Dim intTabIndex As Integer
Dim fld As Field
Dim rst As Recordset
Dim db As Database
Set db = CurrentDb
Set rst = db.OpenRecordset("Yourquery")

' Set project name value

'Open "frmCustomForm" form in design view

DoCmd.OpenForm "frmCustomForm", acDesign
Set frm = Forms!frmCustomForm


' Set positioning values for new controls
intTabIndex = 0
intLabelX = 100
intLabelY = 100
intDataX = 1000
intDataY = 100
For Each fld In rst.Fields
ctlName = fld.Name
Set ctlControl = CreateControl(frm.Name, acTextBox, , , fld.Name, intDataX, intDataY)
'Create child label control for text box
Set ctlLabel = CreateControl(frm.Name, acLabel, , ctlControl.Name, "'" & fld.Name & "'", intLabelX, intLabelY)
intLabelY = intLabelY + 300
intDataY = intDataY + 300
'Restore from
DoCmd.Restore
Next

 

[Fuga]

for each row...

I´m trying to do a for each loop for each record in a table.

I can´t get it to work. Please help.

Fuga.

 

[glynch]

Use a recordset and step thru it.

rstMyRecordset.MoveFirst

enter your code to do whatever

rstMyRecordset.MoveNext

This is just part of the code needed for a recordset, look in Help for details and examples.

 

[charityg]

Or a little more precisely

rstMyRecordset.MoveFirst
Do Until rstMyRecordset.EOF
YourCodeHere
rstMyRecordset.MoveNext
Loop