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) 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