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

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

 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

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