We help IT Professionals succeed at work.

How to rearrange / reorder the fields in a continuous form?

Murugesh Perumal
on
I have a subform which has 80 fields in it from the same table. Based on some criteria on the main form, the number of columns to be displayed in the subform varies. If I make the width as 0 or set Visible as False for the columns which are not required, still a small gap is shown between the previous and next ciolumns in the subform.

How to avoid this white space? Or how to move the fields with width set as 0 to the end of all columns?
Comment
Watch Question

Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
Are you working with Continuous forms, or a Datasheet form?

If a continuous form, the you can move that column to the end, but you'll have to reorder all other columns. I've used routines where I reset the ControlSource for the necessary controls based on which columns the user wishes to see. I start from the leftmost control and set the ControlSources as needed, and then hide all those controls on the right which don't need to show.

If this is a Datasheet form, you can use the ColumnHidden attribute to hide columns.

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
Are you really trying to display 80 columns at a time?  Sounds to me like your data stucture needs to be reworked.

If in a continuous form, are you displaying these all on one line, or in multiple lines?

Like Scott (LSM), I have also done this type of thing for a client that wanted to be able to select which columns to display, and in what sequence.  Unlike Scott, I left the control source untouched.  

I created a table containing all of the fields and a form which allowed the user to check whether to include the field, and to sort the fields in whatever order they wanted them (the table also include the name of the controls associated with each field).  

I then used the Form_Open event to loop through the sequence of fields and change the Left property of each of the associated labels and textboxes (buy just adding the Left and Width properties of the previous control).  For those that the user doesn't want displayed, just set the Visible property of the associated controls to False.  

I also created a shortcut menu that allowed them to pull up the form and hide/unhide and sort the fields on the fly (similar to the what you see when you right click on a tables column header).
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
I've attached a sample database showing how to do this.
ReorderFields.mdb