I need to re-order columns in MS Access form on click of the button.

1. If Column Sequence1 button clicked order should be Col1, Col2, Col3
2. If Column Sequence2 button clicked order should be Col3, Col2, Col1
3. If Column Sequence3 button clicked order should be Col2, Col1, Col3

Please refer below screenshot for the reference

I tried, but column order is changing only when it is saved manually in the mainform or Subform. Please help on VBA script

CodePudding user response：

Assuming two columns (instead of your two, but easy to extend); you can change these properties:

Private Sub Command1_Click()
  With Me.MyTbl_subform.Controls
       !Col1_Label.Caption = "ColA" ' Column Heading on the form
       !Col2_Label.Caption = "ColB"
       !Col1.ControlSource = "ColA" ' Column name from Table/Query in RecordSource
       !Col2.ControlSource = "ColB"
  End With
End Sub

Private Sub Command2_Click()
  With Me.MyTbl_subform.Controls
       !Col1_Label.Caption = "ColB"
       !Col2_Label.Caption = "ColA"
       !Col1.ControlSource = "ColB"
       !Col2.ControlSource = "ColA"
  End With

End Sub

Col1_Label, Col2_Label, Col1, and Col2 are the control names on the subform; ColA and ColB are the column names on the subform's source table/query.