SQL erases after the query is ran

[WannaChevy03]

Hi,
I have built a form, with a "run" button that runs multiple queries...

DoCmd.OpenQuery ("Get1 Spec Revs")
DoCmd.OpenQuery ("Get2 Plant Forms")
DoCmd.OpenQuery ("Get3 More Data")
If Check89 = True Then DoCmd.OutputTo acOutputQuery, "Get4 No HSI Form",
acFormatXLS, "C:\Temp\Output.xls", True

The first 3 queries are make-table queries, and the fourth is not. All 4
queries run just fine, and I get the data that I need, however, I can't run
it a second time because the SQL erases itself in the fourth query only!!!
This is VERY frustrating because I have to constantly copy and paste the SQL
back into it. Any suggestions?

Thanks!
Jessica

[OfficeDev18 via AccessMonster.com]

Is "Get4 No HSI Form" a query residing on the Query tab of your database? How
about posting the query's SQL so we can get a look at it?

Sam

--
Sam

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200601/1

[WannaChevy03]

Yes, it does reside on the query tab of my database. Here is the SQL for #4...

SELECT More_Data.PITEM_ID AS Specification, More_Data.PITEM_REVISION_ID AS
Revision, More_Data.PCREATION_DATE AS Date_Revised, More_Data.POS_USERNAME AS
Who_revised, More_Data.INFODBA_PWORKSPACEOBJECT_POBJECT_TYPE AS Object_Type
FROM More_Data
WHERE (((More_Data.PCREATION_DATE) Between [Forms]![No HSI
PRF]![CreateStart] And [Forms]![No HSI PRF]![CreateEnd]) AND
((More_Data.INFODBA_PWORKSPACEOBJECT_POBJECT_TYPE) Like [Forms]![No HSI
PRF]![DocType] And
(More_Data.INFODBA_PWORKSPACEOBJECT_POBJECT_TYPE)<>"End_Item Revision Master"
And (More_Data.INFODBA_PWORKSPACEOBJECT_POBJECT_TYPE)<>"SalesOrder Form" And
(More_Data.INFODBA_PWORKSPACEOBJECT_POBJECT_TYPE)<>"Alloy_Item Revision
Master" And (More_Data.INFODBA_PWORKSPACEOBJECT_POBJECT_TYPE)<>"Commodity
Sales Form" And
(More_Data.INFODBA_PWORKSPACEOBJECT_POBJECT_TYPE)<>"PlantReview Form" And
(More_Data.INFODBA_PWORKSPACEOBJECT_POBJECT_TYPE)<>"Category Form") AND
(([More_Data].[PITEM_ID] & [More_Data].[PITEM_REVISION_ID]) Not In (SELECT
More_Data.PITEM_ID & More_Data.PITEM_REVISION_ID FROM More_Data WHERE
INFODBA_PWORKSPACEOBJECT_POBJECT_NAME="G1A.HSI-Plant Review")))
ORDER BY More_Data.PITEM_ID, More_Data.PITEM_REVISION_ID;

[OfficeDev18 via AccessMonster.com]

I dunno. Anybody?

Sam

--

[Paul B]

I'm having exactly the same problem as WannaChevy03 - running an OutputTo and
the SQL is being erased after the Query. Have found lots of references to
this problem on the net, but no solutions.

Anybody got any ideas?

[danemi...@gmail.com]

Same here. My query uses fields in a form to filter data, and I am trying to use a macro to export the query results as an Excel file, but the query just erases itself.

[grantl...@gmail.com]

If it's deleting your query, then maybe write to an instance of Excel using the Get4 query as a recordset:

Dim xl, get4 As Recordset
Set get4 = CurrentDb.CreateQueryDef("", CurrentDb.QueryDefs("Get4 No HSI Form").SQL).OpenRecordset
'If records are returned...
If get4.RecordCount > 0 Then
Set xl = CreateObject("Excel.Application").Workbooks.Add
get4.MoveFirst
''''Add fields to Excel file
For f = 0 to get4.Fields.Count - 1
xl.Worksheets(1).Cells(1, f+1).Value = get4.Fields(f).Name
Next
''''Add recordset contents to Excel file
Do Until get4.EOF
For f = 0 to get4.Fields.Count - 1
xl.Worksheets(1).Cells(get4.AbsolutePosition + 2, f+1).Value = get4.Fields(f).Value
Next
''''Move to next record
get4.MoveNext
Next
''''Show Excel file
xl.Application.Visible = True
End If