Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old June 5th, 2007, 11:08 AM
Friend of Wrox
 
Join Date: Apr 2007
Location: , , .
Posts: 110
Thanks: 1
Thanked 2 Times in 2 Posts
Send a message via MSN to ayazhoda
Default Export Values to Excel Sheet from Sub form

Hi All,

I have 3 combo boxes on main form and sub form is populating with query

Dim SupplierID
Dim ProductID

SupplierID = Me.cmbSupplierList
ProductID = Me.cmbProductTypes.Column(0)
Me.ExportProductToExcel_subform.Form.Recordsource = "SELECT Products.PID, Products.ProductType FROM Products INNER JOIN ProductTypes ON products.ProductType=ProductTypes.ProductType WHERE (((ProductTypes.SID)= '" & SupplierID & "')) And (((ProductTypes.ProductType)= " & ProductID & "))"


Now i want to Export all these value in Subform into Excel Sheet

Any idea suggestion

Regards

Ayaz

  #2 (permalink)  
Old June 5th, 2007, 11:34 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I am sure there is a more elegant way to do this, but my presumption is that you cannot export to excel from any dynamic query, it must come from a saved query or a table.

What I do is create a table that holds all the fields in my query, then create a delete query to empty the table, then create an append query to push new records into the empty table, then export the table to Excel.

In your case, you would create an Append query seperate from your subform, and then in the criteria lines for ProductType and Supplier, add:

[Forms]![myFormWithSubForm].[cmbSupplierList]
[Forms]![myFormWithSubForm].[cmbProductTypes]

No need to specify Column(0) since that should be the bound column anyway.

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDELETETempTable"
DoCmd.OpenQuery "qryAPPENDTempTable"
DoCmd.SetWarnings True
DoCmd.... export to excel here

Did that help?









mmcdonal
  #3 (permalink)  
Old June 6th, 2007, 05:28 AM
Friend of Wrox
 
Join Date: Apr 2007
Location: , , .
Posts: 110
Thanks: 1
Thanked 2 Times in 2 Posts
Send a message via MSN to ayazhoda
Default

 Hi mmcdonal

Thanks for your help its brilliant.

Now few things

when I use DoCmd.OpenQuery --> gives error [Datatype mismatch in Criteria expression]
DoCmd.OpenQuery "AppendTempTableProduct"
here is query
"INSERT INTO TempTableProduct ( PID, SID, SupplierNum, Description, BoxQuantity, ProductType )
SELECT Products.PID,
 Products.SID,
 Products.SupplierNum,
 Products.Description,
 Products.BoxQuantity,
 Products.ProductType
FROM Products INNER JOIN ProductTypes ON products.ProductType=ProductTypes.ProductType
WHERE (((ProductTypes.SID)='" & [Forms]![ExportToExcel].[cmbSupplierList] & "'))
And (((ProductTypes.ProductType)=" & [Forms]![ExportToExcel].[cmbProductTypes].Column(0) & "));
"
And with out column(0) is picking up first colum which i dont need in [Forms]![ExportToExcel].[cmbProductTypes].Column(0)

what i did

DoCmd.SetWarnings False
DoCmd.RunSQL "Delete from TempTableProduct"
'DoCmd.RunSQL "INSERT INTO TempTableProduct ( PID, SID, SupplierNum, Description, BoxQuantity,ProductType ) SELECT Products.PID, Products.SID, Products.SupplierNum, Products.Description, Products.BoxQuantity, Products.ProductType FROM Products INNER JOIN ProductTypes ON products.ProductType=ProductTypes.ProductType WHERE (((ProductTypes.SID)= '" & [Forms]![ExportToExcel].[cmbSupplierList] & "')) And (((ProductTypes.ProductType)= " & [Forms]![ExportToExcel].[cmbProductTypes].Column(0) & "))"
DoCmd.OpenQuery "AppendTempTableProduct"
DoCmd.SetWarnings True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "TempTableProduct", "c:\test2.xls"


any further advice

Regards

Ayaz

  #4 (permalink)  
Old June 12th, 2012, 03:18 PM
Registered User
Points: 6, Level: 1
Points: 6, Level: 1 Points: 6, Level: 1 Points: 6, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2012
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The most elegant way of exporting subform's data to Excel, I am aware of, is with use of MS Access add-in 'A2EE.mda'.
This add-in has been developed exactly for the purpose of exporting data from Access to Excel.
It can export data from both form and subform all at once and show them all in Excel in original Access-like drill-down structure.
You may find more details on:
http://www.limbersti.cz/A2EE/

Rgds
Norbert


Similar Threads
Thread Thread Starter Forum Replies Last Post
Gridview data export to excel sheet abhishekkashyap27 C# 2005 1 August 1st, 2008 10:10 AM
Export data from a datalist to an Excel sheet see07 ASP.NET 1.x and 2.0 Application Design 3 February 23rd, 2007 07:20 PM
How To Export a specific Excel Sheet as a csv file mrjits Excel VBA 5 August 1st, 2006 03:04 PM
display excel sheet in form Kaustav Pro VB 6 0 March 8th, 2006 02:07 AM
Export Image to Excel sheet haribala.raj General .NET 0 September 13th, 2005 11:56 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.