subform column selection

[bigmac]

hi all can you help please , I would like the user to be able to display in a subform only the columns that they want , so I was thinking maybe a list of all columns and tick boxes along side so they can choose what is displayed in the subform (or is there a better way?), is this possible ? and if so can you show me in a small demo please how this works

 

[spikepl]

If you are using datasheet view, you can right-click on a column header and select Hide Fields or Unhide Fields.

 

[bigmac]

but I need the user to be able to select the fields/columns before the subform opens

 

[sneuberg]

I think you could do this by setting various properties of the controls on the subform like Visible, Left, Width, etc, but unless there are only a few columns I think keeping track of left positions of the controls is going to be a challenge. I played with this a little and in the attached database the checkboxes switch the textbox widths through the on click events of the checkboxes on the main form. This is a long way from want you want but maybe this will get you started.

I don't think you can do this easily before the subform opens. It's probably better to open the subform hidden (Visible = False) and then unhide it when you want the user to see it.

If you choose to do it this way please let us know how it goes. I'm going to have to do something like this but for a report which will have two options columns.

 

[bigmac]

hi sneuberg could not get your demo to work for me , and I think you are right about hiding the subform , any idea why your demo didn't work for me ?

 

[sneuberg]

Stopped working for me too. I'll get you something shortly. I hope.

 

[sneuberg]

Sorry about that. This one should work. I also have the Column2 in this shifting left and right, but not in any meaningful way.

 

[bigmac]

great, how do you hide the column labels as well?

 

[sneuberg]

Me![SubForm].Form!lblColumn2.Caption = ""

for example where lblColumn2 is the name I gave to the label over column 2 in my example database.

 

[spikepl]

Datasheet view makes it all quite simple:

 

[bigmac]

can you use tick boxes for his instead of a list box? is can you show me please

 

[spikepl]

no you cannot,

But for this type of selectrion oe mechanism is to have 2 listboxes: one showing what is available and one showing what is selected. Each time you click in the available-listbox, the item is removed from there and added to the "selected listbox", and vice versa.

Google access two listboxes

 

[bigmac]

I say tick boxes so the user can remove or re instate a field

 

[bigmac]

ok thank you ,

can you show me how your vba works in your datasheet view please

 

[spikepl]

You got all the code - look yourself

 

[sneuberg]

I've been playing with spikepl's interesting approach, but haven't been able to unhide the columns. Have you?

 

[bigmac]

not yet, but my vba is not very good , that's why I asked for an explanation as to how it works ,

 

[spikepl]

Must have uploaded wrong copy -sorry folks:

 

[bigmac]

thanks spikepl , this works great , I will try and read the vba to understand how it works

 

[bigmac]

hi all, how would could this be modified so when the database is re opened it will show only my selection and not all the fields in the subform please?