505.369.1650 [email protected]

In a recent project, I needed to create a Microsoft Access datasheet form based on a table with a lot of fields.  If I used the form wizard, there would be more text boxes than would easily fit on the form.  The manual solution was to drag a set of fields from the Field List over to the form, shorten the height of all of the text boxes and remove the space between them, and then continue that process until all of the fields were accounted for.

The function below allows you to add controls for all the fields automatically.  It will create a text box and associated label for each field in the TableName table.  The height of the controls is defined by lngcHeight.  With the current value in the code for lngcHeight, the controls are very short.

Here is an example of how to call the function in the Immediate window:

Public Function AddControlsToFormFromTable(FormName, TableName) As Boolean
' This procedures adds textbox controls to the FormName form,
' using the field list from the TableName table.
' It assumes that the form is open in design view.

' AddControlsToFormFromTable() Version 1.0.0
' Copyright © 2013 Extra Mile Data, www.extramiledata.com.
' For questions or issues, please contact [email protected].
' Use (at your own risk) and modify freely as long as proper credit is given.

On Error GoTo Err_Handler

    Dim db As DAO.Database
    Dim tbl As DAO.TableDef
    Dim fld As DAO.Field
    Dim frm As Form
    Dim ctlText As TextBox
    Dim ctlLabel As Label
    Dim lngTop As Long
    Const lngcHeight As Long = 144
    Const lngcWidth As Long = 4320
    Const lngcLabelLeft As Long = 0
    Const lngcTextLeft As Long = 4320
    Set db = CurrentDb
    Set tbl = db.TableDefs(TableName)
    Set frm = Forms(FormName)
    frm.RecordSource = TableName
    lngTop = 0
    For Each fld In tbl.Fields
        Set ctlText = Application.CreateControl(FormName, _
            acTextBox, , , , lngcTextLeft, lngTop, lngcWidth, lngcHeight)
        ctlText.Name = "txt" & fld.Name
        ctlText.ControlSource = fld.Name
        Set ctlLabel = Application.CreateControl(FormName, _
            acLabel, , ctlText.Name, fld.Name, 0, lngTop, lngcWidth, lngcHeight)
        ctlLabel.Name = "lbl" & fld.Name
        ctlLabel.Width = lngcWidth
        ctlLabel.Height = lngcHeight
        lngTop = lngTop + lngcHeight
    Next fld


    AddControlsToFormFromTable = True

    On Error Resume Next
    Set ctlText = Nothing
    Set ctlLabel = Nothing
    Set fld = Nothing
    Set tbl = Nothing
    Set db = Nothing
    Exit Function

    MsgBox Err.Number & " " & Err.Description, vbCritical, "AddControlsToFormFromTable()"
    AddControlsToFormFromTable = False
    Resume Exit_Proc
End Function