Auto rearrange Fields Or Hide Fields in DataSheet based on criteria

[rpatil]

I have main form and that has one sub-form , this Main form governs/determines the data in the sub form.

This Sub form ( DataSheet Mode) has approximately 130 columns and based on the Main form criteria only ~ 20 columns has to be filled.

What I want to do is based on the main forms criteria I want to show only the columns that are applicable to main form criteria.

If I use Columnar or Tabular single form for the Sub Form I am able to hide the fields that are not required BUT IT LEAVES A SPACE/GAP on form ( for the hidden fields that are not required)

Private Sub Form_Load()
If Forms!frmShowPIforActiveAndCanAddNewPI!FrmSubFrmFilterProductInformationPerFMT!CASETIF = True Then

Me.CASETIF.Visible = True
Else
Me.CASETIF.Visible = False
End If
End Sub


And If I use DataSheet and hide ( visible = no) a particular filed it still shows up in Sub Form

Is there a way to Auto-Re Arrange all the fields in the sub form so that the hidden ( visible = no) fields no not leave gap


Or is there a way by VBA program to select fields ( Columns) from a table to be displayed on a sub form based on a criteria

Thanks
Rahul