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:
?AddControlsToFormFromTable("frmAddControls","AddControls")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)
Forms(FormName).SetFocus
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
DoCmd.Restore
DoCmd.Save
AddControlsToFormFromTable = True
Exit_Proc:
On Error Resume Next
Set ctlText = Nothing
Set ctlLabel = Nothing
Set fld = Nothing
Set tbl = Nothing
Set db = Nothing
Exit Function
Err_Handler:
MsgBox Err.Number & " " & Err.Description, vbCritical, "AddControlsToFormFromTable()"
AddControlsToFormFromTable = False
Resume Exit_Proc
End Function
Making Microsoft Access Work. Call (505) 369-1650.