Here's my next hurdle. The way that my code is written, my loop cycles through each field in my recordset and adds a control to my form. What I actually want to do is loop through each record and add a control for each record.
The FeildName field is the control I want to add and the other fields in that record contain values to set various propeties such as control type, control width, default values, etc.
How do I loop through the records in stead of the fields?
Dim ctlLabel As control, ctlControl As control, ctlName As Variant
Dim fld As Object
Dim intLabelX As Integer, intLabelY As Integer
Dim intDataX As Integer, intDataY As Integer
Dim intTabIndex As Integer
Dim frmTakeoff As Form
Dim rstSelectedField As DAO.Recordset
Dim dbs As DAO.Database
Dim qd As DAO.QueryDef
TxtProjectID = Forms!frmDetails!TxtProjectID
Set dbs = CurrentDb
Set qd = dbs.QueryDefs!qryCreateForm
qd.Parameters![Master Project ID] = TxtProjectID
Set rstSelectedField = qd.OpenRecordset
'Open "frmCustomForm" form in design view
DoCmd.OpenForm "frmCustomTakeoff", acDesign
Set frmTakeoff = Forms!frmCustomTakeoff
' Set positioning values for new controls
intTabIndex = 0
intLabelX = 100
intLabelY = 300
intDataX = 100
intDataY = 0
For Each fld In rstSelectedField.Fields
ctlName = fld.Name
Set ctlControl = CreateControl(frmTakeoff.Name, acTextBox, , , ctlName, intDataX, intDataY)
'Create child label control for text box
Set ctlLabel = CreateControl(frmTakeoff.Name, acLabel, acHeader, ctlName, "'" & fld.Name & "'", intLabelX, intLabelY)
'Set Default Values
If Not IsNull(rstSelectedField.Fields("FieldDefaultValue")) Then
ctlControl.DefaultValue = rstSelectedField.Fields("FieldDefaultValue")
End If
'Move to next location
intLabelX = intLabelX + 1500
intDataX = intDataX + 1500
'Set TabIndex and advance TabIndex counter
ctlControl.TabIndex = intTabIndex
intTabIndex = intTabIndex + 1
'Restore form
DoCmd.Restore
Next
Let me explain it this way:
I have a query with the following fields:
<SelectedFields><FieldTitle><FieldDataType><FieldControlType><FieldDefaultValue><FieldSize>
The user selects a group of fields that he/she wants on the form. The way that I want my loop to work is this.
The first record may contain, for example:
"DoorNumber"_"txtDoorNumber"_"Text"_"acTextBox"_""_"1000"
I want to set up a control and label for "DoorNumber. I want to set the name of the control to "txtDoorNumber", set the DataType to Text, set the ControlType to acTextBox, set the default value to Null, and add set the width to 1000. Additionally, I want to add 1000 to the intLabelX and intControlX coordinates. It would then loop to the next record in my set.
This record may contain the following values:
"HardwareGroup"_"cmbHardwareGroup"_"acComboBox"_"3070"_"800"
I want to set up the next control (to the right of the first, in a datasheet format) using the values of this record as before.
It is not exactly clear to me how I reference my collection, my object, my collection's values, etc. I'm pretty new in this realm of programming in VB.
Does this help?
The FeildName field is the control I want to add and the other fields in that record contain values to set various propeties such as control type, control width, default values, etc.
How do I loop through the records in stead of the fields?
Dim ctlLabel As control, ctlControl As control, ctlName As Variant
Dim fld As Object
Dim intLabelX As Integer, intLabelY As Integer
Dim intDataX As Integer, intDataY As Integer
Dim intTabIndex As Integer
Dim frmTakeoff As Form
Dim rstSelectedField As DAO.Recordset
Dim dbs As DAO.Database
Dim qd As DAO.QueryDef
TxtProjectID = Forms!frmDetails!TxtProjectID
Set dbs = CurrentDb
Set qd = dbs.QueryDefs!qryCreateForm
qd.Parameters![Master Project ID] = TxtProjectID
Set rstSelectedField = qd.OpenRecordset
'Open "frmCustomForm" form in design view
DoCmd.OpenForm "frmCustomTakeoff", acDesign
Set frmTakeoff = Forms!frmCustomTakeoff
' Set positioning values for new controls
intTabIndex = 0
intLabelX = 100
intLabelY = 300
intDataX = 100
intDataY = 0
For Each fld In rstSelectedField.Fields
ctlName = fld.Name
Set ctlControl = CreateControl(frmTakeoff.Name, acTextBox, , , ctlName, intDataX, intDataY)
'Create child label control for text box
Set ctlLabel = CreateControl(frmTakeoff.Name, acLabel, acHeader, ctlName, "'" & fld.Name & "'", intLabelX, intLabelY)
'Set Default Values
If Not IsNull(rstSelectedField.Fields("FieldDefaultValue")) Then
ctlControl.DefaultValue = rstSelectedField.Fields("FieldDefaultValue")
End If
'Move to next location
intLabelX = intLabelX + 1500
intDataX = intDataX + 1500
'Set TabIndex and advance TabIndex counter
ctlControl.TabIndex = intTabIndex
intTabIndex = intTabIndex + 1
'Restore form
DoCmd.Restore
Next
Let me explain it this way:
I have a query with the following fields:
<SelectedFields><FieldTitle><FieldDataType><FieldControlType><FieldDefaultValue><FieldSize>
The user selects a group of fields that he/she wants on the form. The way that I want my loop to work is this.
The first record may contain, for example:
"DoorNumber"_"txtDoorNumber"_"Text"_"acTextBox"_""_"1000"
I want to set up a control and label for "DoorNumber. I want to set the name of the control to "txtDoorNumber", set the DataType to Text, set the ControlType to acTextBox, set the default value to Null, and add set the width to 1000. Additionally, I want to add 1000 to the intLabelX and intControlX coordinates. It would then loop to the next record in my set.
This record may contain the following values:
"HardwareGroup"_"cmbHardwareGroup"_"acComboBox"_"3070"_"800"
I want to set up the next control (to the right of the first, in a datasheet format) using the values of this record as before.
It is not exactly clear to me how I reference my collection, my object, my collection's values, etc. I'm pretty new in this realm of programming in VB.
Does this help?
