How to Export Data from a Subform in Access

[Catdog01]

Hello -
I have a subform within a Form that get it's data from a parameter query. I would like to be able to export the data from the subform into Excel without having the parameter of the query pop up each time I run my command button. Here's what I currently have. Any suggestion will be helpful.

DoCmd.OutputTo acOutputForm, Forms!Search_Addr_Form1![Search_Addr_Q subform].Form.Name, acFormatXLS, "c:\temp15\test.XLS

 

[Pampers]

Hi Catdot01,
Maybe you could write the data from the parameter query first to a temporary table (tblTemp) and the export it from there with the TransferSpreadsheet-method. Don't forget to clean out the temptable before using it again.

Pampers
Keeping it simple can be complicated

 

[Catdog01]

I borrow this from someone else.
I'm not quite getting this to work. The code is
giving me "Run-time error '3061' Too few parameters. Expected 3. When I go to debug, it is highlighting
the "Current

Dim StrSQL As String
CurrentDb.Execute "Delete * from Temp"
StrSQL = "Insert into Temp " & _
"Select Search_Addr_Q.* " & _
"From Search_Addr_Q "
If Me.Filter <> "" Then
StrSQL = StrSQL & "Where " & Me.Filter
End If
CurrentDb.Execute StrSQL
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "Temp", "C:\Temp15\Test.xls", True

 

[Pampers]

Hi Catdog01,
Your code is doing about the same.
1. delete a temp table
2. append records to temp table
3. export from temp table

To keep things simple,
create a append query in the query designer - take the same query as the subform, but change to an append query (insert into) and have it append records to your temp table (execute the query by clicking a button on your form, you could use the docmd.openquery method).

You could also - for the first time - use a make table query, and then change it to an append query.

Follow your code (with the append query execution) up with the transferspreadsheet-method with the source your temptable... if you still follow



Pampers
Keeping it simple can be complicated

 

[ProgramError]

quote] without having the parameter of the query pop up each time I run my command button [/quote]

Suggests there is missing information which you have to enter first before the query is run. Is this correct?


Ian Mayor (UK)
Program Error
Always make your words sweet and nice. Because you never know when you may have to eat them.

 

[Catdog01]

ProgramError -
That is correct. It is a parameter query, which will have a couple of pop up dialog boxes to help filter the needed data whenever the query is ran. I guess the only other way of kicking out what I want is to somehow grab and export the subform's record after the querying is done, but I haven't been successful with that either. It only kicks out a blank excel spreadsheet and it is referencing the main form.

 

[ck1999]

Could you on the form have some text boxes hidden. Then when the command button is clicked the first time have msgboxes appear to get the "parameter query" information. Then have it save these to the textboxes. Then next time you click the command button it can check to see if the txtboxes are blank if not blank proceed with query without msgbox.

You will have to clear the txtboxes on form_Close.

ck1999

 

[Catdog01]

Pampers -
That's kind of what I'm trying to avoid. I have a situation here where I have alot of different queries to run
and each will require an output to Excel. It would be more streamline if I can keep the exporting to a click of a button rather than maintaining a bunch of action queries and macros. Thanks for the suggestion though.

 

[Pampers]

StrSQL = "Insert into Temp " & _
   "Select Search_Addr_Q.* " & _
   "From Search_Addr_Q "

This looks like an action query to me.

You don't need a LOT of action queries, you just need one. In fact, the same one you have in your code but a bit easier to control and create.


Pampers
Keeping it simple can be complicated

 

[Catdog01]

Ok. I got to work, but not in the way I wanted. But it'll
do for now. I took a couple of your suggestions and decided to go the macro way. First to insert the data into a temp table which activates the parameter popup.Once in the temp table I then have it open up in the form!subform so the end user can review the data. Now that the data has been filtered and inserted into the temp table, I can now create a separate button to transportspreadsheet as needed. It's not the route I wanted to take since it requires I create additional temp table(s) for each of my queries for outputting, but it'll suffice. Thanks all for your input.
Greatly appreciated.