Add data field from table to form automatically using vba

Question

In an Access database, I have a split form. The datasheet is based on a query. The table that is queried sometimes grows in fields.

This field is in the query, but not in the form because it has to be dragged from the "available fields" on design view on access into the form.

Is there some code that will allow a field to be added to a form when a button on the form is pressed instead of the user going into design view?

This would be more convenient than having every user opening and editing (possibly breaking) the database in design view.

Possible duplicate of How to create controls at run time Access VB? — Andre
There is Application.CreateControl() (see linked question), but it needs design view too. -- If your table changes regularly, and the user has to fix his form, it is likely that your table design is broken. — Andre
Agree with @Andre - db structure that implements routine design changes is bad, probably not normalized. Once a db is deployed, design changes should be infrequent and desired to meet a new requirement and certainly not accomplished by end user. — June7

C Perkins C Perkins · Accepted Answer · 2017-04-14T22:52:00

As you can see from the comments, similar questions have been asked and so the default answer is that even with VBA code the form must be in design mode to add new controls - you cannot do this in Form View. But despite the limitations of Access there is still often a perfectly reasonable need that still requires a useful solution.

One potentially cluttered and limited solution is to add hidden unbound controls (i.e. ControlSource property is blank) that can then be bound and shown during Form View. First, during design time add hidden, unbound TextBox and Label controls to the form. Here is some template code within a button click event handler.

Private Sub cmdAddFields_Click()
  Dim rs As Recordset2
  Set rs = Me.RecordsetClone
  Dim fld As Field2
  ...
  '* Determine which new fields need to be shown
  set fld = rs.Fields(missingFieldIndexOrName)
  ...
  Me.txtCustom1.ControlSource = fld.Name
  Me.lblCustom1.caption = fld.Name
  Me.txtCustom1.Visible = True
  Me.lblCustom1.Visible = True
  ...
End Sub

It should be apparent that you would be limited to the number of these dummy fields that you have added, and it'll require some clever way of determining what your missing fields are but that could be done by looping through the rs.Fields collection and the form's Control collection to match names (for instance).

Add data field from table to form automatically using vba

1 Answers