Export query to Excel code problems (1 Viewer)

[stpiepgr]

Hi all.......

I am having a problem that I am guessing has a simple solution. I am using some simple vb code on the "OnClick" event of a button to export a query to excel. Works great. Now I created a crosstab query based on the original query to summarize some info and want to export that to excel. However access has a problem with my ApplyFilter code and I can't figure out what the problem is. All the necessary fields appear to be present and all I did was change the query being exported. I tried to attach a sample database with 1 record (A2k)but at 135 kb it is still to big (any suggestions). The code I'm using is:

This one works:
==============
Private Sub exprtSSVeg_Click()
On Error GoTo exprtSSVeg_Err

DoCmd.Echo False, "The Application is Running. Be Patient!"
DoCmd.OpenQuery "qryGISExprtSSVeg", acNormal, acEdit
DoCmd.ApplyFilter "", "[qryGISExprtSSVEG]![DSSV-Project#]=[Forms]![Data Survey History]![DSH-Project#]"
DoCmd.OutputTo acQuery, "", "MicrosoftExcel(*.xls)", "", True, ""
DoCmd.Close acQuery, "qryGISExprtSSVeg"
DoCmd.Echo True, ""

exprtSSVeg_Exit:
Exit Sub

exprtSSVeg_Err:
MsgBox Error$
Resume exprtSSVeg_Exit

End Sub

This one doesn't: The bold are is what Access is choking on. Error message reads:The Microsoft Jet database engine doesn't recognize '[Forms]![Data Survey History]![DSH-Project#]' as a valid field name or expression.
===================
Private Sub exprtCrsTab_Click()
On Error GoTo exprtCrsTab_Err

DoCmd.Echo False, "The Application is Running. Be Patient!"
DoCmd.OpenQuery "qryGISExprtCT2", acNormal, acEdit
DoCmd.ApplyFilter "", "[qryGISExprtCT2]![DSSV-Project#]=[Forms]![Data Survey History]![DSH-Project#]"
DoCmd.OutputTo acQuery, "", "MicrosoftExcel(*.xls)", "", True, ""
DoCmd.Close acQuery, "qryGISExprtCT2"
DoCmd.Echo True, ""

exprtCrsTab_Exit:
Exit Sub

exprtCrsTab_Err:
MsgBox Error$
Resume exprtCrsTab_Exit
End Sub


Any help is greatly appreciated.

Thanks......Steve

 

[Mile-O]

The way you have it now, it reads as a literal string:

Rather than the value represented by [Forms]![Data Survey History]!DSH-Project#] you are effectively asking it to filter the field where the value in the field is literally [Forms]!etc

You need to break out of the text string...

I don't know whether your value is a numerical value or text so it's one of two solutions:


If Numeric:
DoCmd.ApplyFilter "", "[qryGISExprtCT2]![DSSV-Project#] = " & [Forms]![Data Survey History]![DSH-Project#]

If Text:
DoCmd.ApplyFilter "", "[qryGISExprtCT2]![DSSV-Project#] = """ & [Forms]![Data Survey History]![DSH-Project#] & """"

 

[stpiepgr]

Thanks Mile,

My field was numeric so I made the changes and it worked great. I did have to remove the " at the end.

As I am still learning this VB stuff, could you or some else explain to me why my first code example works and why I needed to make the changes to second example which only uses a different query?

Thanks again...Steve

 

[Mile-O]

I tried to attach a sample database with 1 record (A2k)but at 135 kb it is still to big (any suggestions).

Have you tried compacting it?

As to your other question I have no idea as I never use the ApplyFilter method. Maybe someone else can give you an answer.

 

[Amileaux]

I have found the "Quotation Marks in Strings" from the Access Help file useful in understanding when to use quotation marks. Search for "quotation_mark" under the index tab and then select "Quotation Marks in Strings" It is confusing for us novices. Marie

 

[stpiepgr]

Thanks Mile and Marie,

I will check out the help file. I did try compacting and zipping but to no avail.

For future reference is there a better way to open/export a query from a form using the selected record other than the ApplyFilter command?

Steve