Filter data in subform then Export

[Denver]

I have a Form with a subform i want to export to excel
what i have filter in my subform thru my cmdExportExcel.

is this possible????
i doubt because i think i can export only data thru queries?
can anyone give some good suggestions how to accomplish this one?

thanks, i appreciate...

 

[Allen Browne]

It can be done but you will need some experience with VBA and SQL to achieve
this.

Essentially, you create a query to use for export, and programmatically
write its SQL statement so that the WHERE clause contains the same thing as
the Filter of the form.

This is how to write the SQL propety of qryExport:

Dim strSql As String
strSql = "SELECT * FROM Table1"
With Me.Sub1.Form
If .FilterOn Then
strSql = strSql & " WHERE " & .Filter
End If
End With
strSql = strSql & " ORDER BY SomeField;"
Currentdb.QueryDefs("qryExport").SQL = strSql
DoCmd.TransferSpreadsheet ...

It can be a bit more messy than that if you are using Access 2002 or later
and your subform's filter string contains lookup values for combos. (You
will have to craft the query statement to use the same aliases for the
tables as the filter string does.)

 

[Denver]

Thank You, Mr Allen Browne.

one more thing do i need to create another Query?
my subform is bound in a query can i use it in my SQL?

thank you very much........
*****************************************

 

[Allen Browne]

Yes: you will need a query specific for the export.

The query your subform is bound to is not sufficient, as you say you are
applying a filter too.

 

[Denver]

yes i have a query now name qryExport

i have this code but i have this error
Run-Time error 3131:Sytax error in FROM Clause.
then when i try ti debug it highlights this one

CurrentDb.QueryDefs("qryExport").SQL = strSql

******here is my code with this error*********
Dim strSql As String

strSql = "SELECT * FROM [DCN01]"
With Me.Data_Entry2_subform.Form
If .FilterOn Then
strSql = strSql & " WHERE" & .Filter
End If
End With
strSql = strSql & " ORDER BY [Drawing Ref];"
CurrentDb.QueryDefs("qryExport").SQL = strSql
DoCmd.TransferSpreadsheet acImport, 8, "qryExport", _
"D:\Database\Export Folders\EXCEL\"

can you help how to fix this........

thanks again...thank you very much Mr. Allen Browne....

*************

 

[Allen Browne]

Add the line
Debug.Print strSql

When it fails, open the Immediate Window (press Ctrl+G), and look at what
came out.

For eample, you may have missed a space between words.

 

[Denver]

Hello Mr. Allen,
I think im succesful checking with spacing and commas and adding
Debug.Pring SQL....

the problem is i have this error now
Run time error '3051: The Microsoft Jet Database engine cannot open the file
"D:\Database\Export Folders\EXCEL\". It is already opened exlusively by
another user, or you need permission to view its data.

can you help what is this error all about????
or maybe i need arguments for my ..."D:\Database\Export Folders\EXCEL\"
i think i miss something here??????
****
Dim strSql As String

strSql = "SELECT* FROM [DCN01]"
With Me.Data_Entry2_subform.Form
If .FilterOn Then
strSql = strSql & " WHERE" & .Filter
End If
End With
Debug.Print SQL
strSql = strSql & " ORDER BY [Drawing Ref];"
CurrentDb.QueryDefs("qryExport").SQL = strSql
DoCmd.TransferSpreadsheet acImport, 8, "qryExport", _
"D:\Database\Export Folders\EXCEL\"


thanks again Mr. Allen Browne.....

***************************

Add the line
Debug.Print strSql

When it fails, open the Immediate Window (press Ctrl+G), and look at what
came out.

For eample, you may have missed a space between words.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

yes i have a query now name qryExport

i have this code but i have this error
Run-Time error 3131:Sytax error in FROM Clause.
then when i try ti debug it highlights this one

CurrentDb.QueryDefs("qryExport").SQL = strSql

******here is my code with this error*********
Dim strSql As String

strSql = "SELECT * FROM [DCN01]"
With Me.Data_Entry2_subform.Form
If .FilterOn Then
strSql = strSql & " WHERE" & .Filter
End If
End With
strSql = strSql & " ORDER BY [Drawing Ref];"
CurrentDb.QueryDefs("qryExport").SQL = strSql
DoCmd.TransferSpreadsheet acImport, 8, "qryExport", _
"D:\Database\Export Folders\EXCEL\"

can you help how to fix this........

 

[Allen Browne]

That imples you (or someone else) has the file or record in use.

Go to Tools | Options | Advanced.
Make sure you are opening your database in shared mode, and that you are
using optimistic locking.

Make sure no other forms are open that use the table(s) you need.

Make sure you don't have multiple instances of the database open.

(I'm assuming it is a JET table, not an external data type such as a
spreadsheet.)