Dynamically hide/show fields on form

[mmogul]

I am looking for the best approach to:

1) Hide / show fields on a form based on the value on another table. I actually am able to do this by testing the value on the onOpen event of the form and changing the visible property of the field in question. But I also want to:

2) Adjust the physical location of the other fields if one field is hidden - so the user does not see a gap in the display of the fields. The fields are displayed in the standard "single form" access view.

This application is capturing information about events. Some events require the user to capture additional information while others require limited information.

Thanks.

 

[MajP]

This code will give you some ideas. It is not complete. But what this did was take a bunch of unbound, invisible controls and then position the controls and bind them to the correct control source. This basically made a dynamic tabular form.

Public Sub createForm()
  On Error GoTo errlbl
  Dim rs As dao.Recordset
  Dim fld As dao.Field
  Dim cnt As Integer
  Dim txt As Access.TextBox
  Dim lbl As Access.Label
  Dim lft As Long
  Dim rt As Long
  Set rs = Me.Recordset
  
  hideColumns
  For Each fld In rs.Fields
    cnt = cnt + 1
    Set lbl = Me.Controls("lbl" & cnt)
    Set txt = Me.Controls("txt" & cnt)
    lbl.Caption = fld.Properties("Caption")
    lbl.Visible = True
    lbl.Left = rt
    lbl.Width = 1 * 1440
    txt.ControlSource = fld.Name
    txt.Visible = True
    txt.Left = rt
    txt.Width = 1 * 1440
    rt = (lbl.Left + lbl.Width) + (0.5 * 1440)
  Next fld
  Exit Sub
errlbl:
  If Err.Number = 3270 Then
    lbl.Caption = fld.Name
    Resume Next
  Else
    MsgBox Err.Number & " " & Err.Description
  End If
End Sub

Public Sub hideColumns()
  Dim ctl As Access.Control
  Me.cmd4.SetFocus
  For Each ctl In Me.Controls
    If ctl.Tag = "?" Then
      ctl.Left = 0
      ctl.Width = 2
      ctl.Visible = False
    End If
  Next ctl
End Sub

 

[mmogul]

Thank you very much. I will review this.