Export From Access Subform Datasheet Including Unbound Control Source

1
votes

I have been searching this online for hours without luck. I have a subform embedded in my main form. The subform's recordsource is from a SQL result based on some filtering criteria user sets in the main form. After user clicks "Search", data gets populated in my subform. Most of its controls are then bound. However, I also have an unbound control in my subform. This unbound control gets data from a separate SQL based on the values of my other controls' values. Everything in my subform is presented in a datasheet view.

Now, I would like to design an "export" function that allows user to save the current search result in my subform datasheet into an Excel file. I have tried the following ways:

DoCmd.OutputTo acOutputForm, "my_Subform", acFormatXLS, exportFile & ".xls"

This only gives me the header of my subform without any data.

I have also tried to use CopyFromRecordset function. However, as I mentioned my subform has one control that is not bound to the record source of the subform. I can store the SQL object and do CopyFromRecordset, but this gives me only data from the bounded controls.

Thanks a lot in advance.

2
sqlms-accessvbaexport-to-excelms-access-2003
You are definately calling the OutputTo function after the queries have been run? and the data is in the datasheet view when you run it? – JMG
@JMG Yes, definitely. I have already had the data shown in my subform. That is done after my user clicks "Search" button. Then the "Export" button can be clicked. – got2nosth

2 Answers

0
votes

you may use MultiLevelExporter tool / add-in for MS Access for this. It can export data of Subform, including unbound control's data of the Subform into MS Excel. Norbert

0
votes

I think I got this to work -- and with Unbound Field

  1. The following is the syntax (obviously change the output path):

    DoCmd.OutputTo acOutputForm, [Forms]![Main_form].Form.[Subform_XXX].Name, acFormatXLS, "C:\temp\exportSub.xls"

  2. The output XLS file had 4 columns - including the content of the Unbound.

Good Luck, Wayne