Saving hidden/unhidden columns in datasheet

[rlarkin]

SOLVED: Saving hidden/unhidden columns in datasheet

Hello,

I'm struggling with this one.

I have a form, which contains a subform, which is a custom datasheet. It's custom in that the user selects the columns they wish to see (achieved by using ColumnHidden), along with any criteria they've entered in a form. This all works great, except I'd like the user to be able to export this custom datasheet to Excel.

The issue I'm finding is that when exporting the datasheet it doesn't maintain the current hidden/unhidden columns. If the form is closed down and then reopened the subform is updated to reflect the chosen columns. Is there any code I can use to update the subform? Saving the form itself via VBA doesn't see to work.

Thanks for your help,

Russ

 

[jleach]

Hi,

How are you exporting to excel? Can you use a dynamic query and select only those fields shown on the subform at the time they export? (assuming using TransferSpreadsheet anyway).

You said when the form is closed and reopned, the chosen columns are selected, but you want to update the subform? What exactly were you looking to update?

FWIW, you can probably gather all the column info (width and visible settings) on form close, save them to a table, and apply them on the form's reopening if need be, but I'm not quite clear on what your final goal is here.

hth

 

[rlarkin]

Hi jleach,

I had a dynamic query originally when building the custom datasheet, but I found when you remove them from SELECT you cannot then unhide those columns via the datasheet's unhide option on right-click. I liked the extra functionality it gave the user to be able to add in an column they'd previously not included on the build.

At the moment I'm exporting via a button with DoCmd.OutputTo acOutputForm, "Custom Query", acFormatXLSX

Here's my code for showing selected columns on load:

DoCmd.OpenForm "frmCustomQuery", acNormal
 
    For Each varItm In ctl.ItemsSelected
        strSelect = Me!lstFields.ItemData(varItm)
        Forms![frmCustomQuery].[Custom Query].Form.Controls(strSelect).ColumnHidden = False
    Next varItm

It's unhiding any columns that match the selected items in the listbox after it's opened the form. So the user sees only the columns they've selected. Now if the user clicks to output to Excel, it will create an empty spreadsheet because the Custom Query subform hasn't updated to reflect the unhidden columns. At the moment I'm saving, closing, reopening and exporting to update the Custom Query subform to get it to work. What I'm essentially looking for a piece of code that says "Save how this datasheet looks within this subform that is within this form". Would a dynamic query on the export be better?

Thanks,

Russ

 

[jleach]

Couple things:

You won't be able to save the layout of the form unless you're working in design view, which is generally not an available practice for actual use (it's more of a development thing, and not very common at that). So you can probably scratch that idea.

You're outputting the form itself to Excel... have you considered using DoCmd.TransferSpreadsheet to output the data itself, rather than OutputTo? (this is originally what I had in mind). Then you can implement a procedure to check for visible columns in your open subform, build a SELECT query, and use TransferSpreadsheet to output the sql you just built.

Something along these lines (aircode):

Dim ctl As Access.Control
Dim SQL as string

For Each ctl In Me.Controls
  If ctl.Visible Then SQL = SQL & ", " & Me.ControlName.ControlSource.Name
Next ctl

SQL = Mid(SQL, 2)
SQL = "SELECT " & SQL & "FROM ..."

DoCmd.TransferSpreadsheet...

hth

 

[rlarkin]

That sounds like a better solution. Thanks for your help jleach, much appreciated.

 

[jleach]

Glad to help.