I'm trying to create a dynamic form that is built based on a table that is given when the function is called.

The form has Textboxes labeled txtFieldy where y is 0 - 18. My lookup fields always have ID in them and is converted to a combobox. The first ID field is skipped.

My problem is that I need a property that will look at the table fields and return the Lookup or Rowsource, so I can define my combo box in my dynamic form the same way.

Any Ideas?

Function PopulateFields(strTableName As String)

    Const conNumButtons = 9

    DoCmd.OpenForm "frmDynForms", acDesign
    Forms.Caption = strTableName



    For intOption = 1 To conNumButtons
        Forms![frmDynForms]("txtField" & intOption).Visible = False
        Forms![frmDynForms]("lblField" & intOption).Visible = False
    Next intOption

    Set con = Application.CurrentProject.Connection
    stSql = strTableName

    Forms![frmDynForms].Form.RecordSource = strTableName

    Set rs = CreateObject("ADODB.Recordset")
    rs.Open stSql, con, 1 ' 1 = adOpenKeyset
    i2 = 0

    For i2 = 0 To rs.Fields.Count - 1
        Forms![frmDynForms]("txtField" & i2).Visible = True
        Forms![frmDynForms]("lblField" & i2).Visible = True
        Forms![frmDynForms]("lblField" & i2).Caption = rs.Fields(i2).Name

        If InStr(rs.Fields(i2).Name, "ID") > 0 And i2 <> 0 Then
                If Forms![frmDynForms]("txtField" & i2).ControlType = acTextBox Then
                    Forms![frmDynForms]("txtField" & i2).ControlType = acComboBox
                    Forms![frmDynForms]("txtField" & i2).BoundColumn = 1
                    Forms![frmDynForms]("txtField" & i2).ColumnCount = 2
                    'Forms![frmDynForms]("txtField" & i2).ColumnWidths = "0in;1in"
                    'strdan = rs.Fields(i2).Source
                    'MsgBox ("Rowsource = " & strdan)
                End If
        End If
        Forms![frmDynForms]("txtField" & i2).ControlSource = rs.Fields(i2).Name
    Next i2

    DoCmd.Save
    DoCmd.Close

    rs.Close
    Set rs = Nothing
    Set con = Nothing

    DoCmd.OpenForm "frmDynForms", acNormal


End Function

You can make an sql statement with a string so that you can concatenate your variables together. Then, you can make the object.rowsource equal to the sql string - just like you did for your recordset.

--------------------------------
Ben

"It's my way, or the Hemingway."
--------------------------------

Actually, I would like the VBA code to discover the source of the field source and then set the source of the form combo box.