Using a field Value in Place of acTextBox, etc (1 Viewer)

[kermit5]

I am trying to create a custom wizard that enables the user to select fields from a list that he/she wants to include on a form. The field properties are in a table tblAvailableFields and include FieldName, FieldType (Control Type i.e. acTextbox, acComboBox, etc.), FieldWidth, FieldRecordSource, etc.

Here is a portion of my code:
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("qryCreateForm")
'Set ProjectName Value
ProjectName = Forms!frmDetails!txtProjectName

'Open "frmCustomForm" form in design view
DoCmd.OpenForm "frmCustomTakeoff", acDesign
Set frm = Forms!frmCustomTakeoff
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(fld.Name, acLabel, , ctlControl.Name, "'" & fld.Name & "'", intLabelX, intLabelY)
intLabelY = intLabelY + 300
intDataY = intDataY + 300
'Restore from
DoCmd.Restore
Next

In the blue line, in place of "acTextBox" I would like to place the value for that recordset so each field will have the pre-determined control type. How do I reference that value?

Scott

 

[Drevlin]

Do you mean that you want the actual value of the acTextBox constant?
like:
acTextBox = 109
acComboBox = 111

if you want to know the actual value of a constant just run it through Debug.Print

Sub whatNumber()
Debug.Print "acTextBox = " & acTextBox
Debug.Print "acComboBox = " & acComboBox
End Sub

Hope this is what you're looking for.

Peace

 

[kermit5]

I am not looking for the numeric constant. What I want is the value in the table tblAvailableFields for the chosen field that contains the type of control that the chosen field is designated.

For example, if the user chooses the field DoorNumber, the control would be a text box (the string value acTextBox is in the record DoorNumber with name FieldControlType). If the user selects the field Exterior, the control would be a check box.

I want to use the FieldControlType value that is in the table in the line of code above.

Does this help clear things up?

 

[Drevlin]

I think it actually does. I have one (i think) more thing I think I need to understand though.
What exactly does your rst.Fields Consist of?
Is one of the rst.Fields.Name = DoorNumber

So that if you went to your code and placed the appropriate information in the line:

Set ctlControl = CreateControl(frm.Name, acTextBox, , , fld.Name, intDataX, intDataY)

it would basically say:

Set ctlControl = CreateControl("frmCustomTakeOff", 109, , , "DoorNumber", 0, 0)

then on the next loop it would say:

Set ctlControl = Createcontrol("frmCustomTakeOff", 106, , , "Exterior", 0, 300)

If this assumption is correct then you can use a DLookup:

Dim lngCntlType as Long

'place inside of for loop after ctlName = fld.Name
lngCtlType = DLookup("[FieldType]", "tblAvailableFields", "[fldName] = " & ctlName)


I would actually place the constant values in your tblAvailableFields instead of acCheckBox. But if you really don't want to do that you will need to use the Eval() statement in your CreateControl():

Dim strCntlType as String
strCtlType = DLookup("[FieldType]", "tblAvailableFields", "[fldName] = " & ctlName)

Set ctlControl = CreateControl(frm.Name, Eval(stCtlType), , fld.Name, intDataX, intDataY)

I really hope this isn't too confusing. I also really hope this helps. I feel silly giving long winded answers when I've misconstrued the question. But hey, I'm not charging anything for the opinions.

Oh... one last thing... I've noticed that you set:
ctlName = fld.Name

Yet you still use fld.Name in your code. I would suggest that you use the ctlName instead. Each time you use the (.) seperator it adds overhead to your code. [JMHO]

Peace