Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Query content disappears when docmd.outputto code is run

Posted on 2008-10-08
11
Medium Priority
?
614 Views
Last Modified: 2012-05-05
I have a code module that loops through a recordset to retrieve the names of queries to be run and the output files to be created.

I then use docmd.outputto passing the retrieved query and file name.


The problem is that the at some point the query contect is disappearing. I then receive "A query must have at least on destination field"  error 3066.


The queries do not have ~ in their name and I have not compacted the database.
Using Access2007




Code is as follows:
 
Dim RSLocation As Recordset
Dim location As String
Dim sqlstring As String
Dim RSReports As ADODB.Recordset
Dim query As String
Dim file As String
 
DoCmd.Hourglass True
DoCmd.SetWarnings False
 
 
Set RSLocation = CurrentDb.OpenRecordset("ReportLocation")
 
If RSLocation.RecordCount = 0 Then
    MsgBox ("No location is present in the Report Location table")
    Exit Function
End If
 
RSLocation.MoveFirst
location = RSLocation.Fields(1).Value
RSLocation.Close
Set RSLocation = Nothing
 
If Right(location, 1) <> "\" Then
    location = location + "\"
End If
 
 
'2 Move through the OutputReport table and select records with print flag set
 
sqlstring = "select QueryName, ReportName from OutputReport where Print= -1"
 
Set RSReports = New ADODB.Recordset
RSReports.CursorLocation = adUseClient
RSReports.Open sqlstring, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
 
If Not (RSReports.EOF) And Not (RSReports.BOF) Then
 
RSReports.MoveFirst
While RSReports.EOF = False And RSReports.BOF = False
    
    query = RSReports.Fields(0).Value
    file = location + RSReports.Fields(1).Value
 
    On Error GoTo PrintError
'3     Check to see if the file exists - if so remove (kill it)
    If Dir(file) <> "" Then
        Kill file
    End If
'4    Run the Report
 
    DoCmd.OutputTo acOutputQuery, query, acFormatXLS, file, False
    RSReports.MoveNext
Wend
 
'5 Tidy up
 
RSReports.Close
Set RSReports = Nothing
DoCmd.Hourglass False
DoCmd.SetWarnings True
 
MsgBox "Report out Completed", vbInformation
 
Exit Function
 
PrintError:
 
If Err.Number = 53 Then Resume Next
Else: MsgBox (Err.Description)
End If
 
DoCmd.Hourglass False
DoCmd.SetWarnings True
End Function

Open in new window

0
Comment
Question by:-Juddy-
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 26

Expert Comment

by:dannywareham
ID: 22693936
Change the variable "file" to "myFile". I believe that "file" is a reserved word in Access.

Next, putthe word STOP in your recordset, just above the " query = RSReports.Fields(0).Value" lne.
Run your code and it will stop.
Press F8 to step through teh code.
Hover your mouse over your variables to ensure that the expected values are being passed to them.
0
 
LVL 3

Author Comment

by:-Juddy-
ID: 22700797
Hi dannywareham;

Thanks for the reponse. I have traced the code extensively and the expected values are getting to the variables.

When tracing I track down the problem to the docmd.outputto statement.
before running this the queries are intact. after running this the queries disappear.

Any other advice would be gratefully recieved.
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 22701128
Is this line getting a value from the recordset:

RSReports.Fields(0).Value

Do you have permission to write to teh file location?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Author Comment

by:-Juddy-
ID: 22701174
hi dannywareham.

The file location is writeable. The first time the code runs all files are correctly written to this location. the 2nd time through it fails as some of the queries design has disappeared (i.e. the queries are there but when you look at them the sql consists "select ;" and in design mode the query grid is empty.
0
 
LVL 3

Author Comment

by:-Juddy-
ID: 22701177
The RSReports.Fields(0).value line is populated correctly
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 22701194
But you're not writing to or removing a query object.
How would you see the design grid view with just SELECT;?

If you mean the recordset gets the query correctly first time, and then doesn't, it's a problem with the recordset.
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 22701206
Try this:
Code is as follows:
 
Dim RSLocation As Recordset
Dim location As String
Dim sqlstring As String
Dim RSReports As ADODB.Recordset
Dim query As String
Dim file As String
 
DoCmd.Hourglass True
DoCmd.SetWarnings False
 
 
Set RSLocation = CurrentDb.OpenRecordset("ReportLocation")
 
If RSLocation.RecordCount = 0 Then
    MsgBox ("No location is present in the Report Location table")
    Exit Function
End If
 
RSLocation.MoveFirst
location = RSLocation.Fields(1).Value
RSLocation.Close
Set RSLocation = Nothing
 
If Right(location, 1) <> "\" Then
    location = location + "\"
End If
 
 
'2 Move through the OutputReport table and select records with print flag set
 
sqlstring = "select QueryName, ReportName from OutputReport where Print= -1"
 
 
RSReports.Open sqlstring, cnn  'uses last known connection
 
Do Until RSReports.EOF
    query = RSReports.Fields(0).Value
    file = location + RSReports.Fields(1).Value
 
    On Error GoTo PrintError
'3     Check to see if the file exists - if so remove (kill it)
    If Dir(file) <> "" Then
        Kill file
    End If
'4    Run the Report
 
    DoCmd.OutputTo acOutputQuery, query, acFormatXLS, file, False
    RSReports.MoveNext
Loop
 
'5 Tidy up
 
RSReports.Close
Set RSReports = Nothing
DoCmd.Hourglass False
DoCmd.SetWarnings True
 
MsgBox "Report out Completed", vbInformation
 
Exit Function
 
PrintError:
 
If Err.Number = 53 Then Resume Next
Else: MsgBox (Err.Description)
End If
 
DoCmd.Hourglass False
DoCmd.SetWarnings True
End Function

Open in new window

0
 
LVL 3

Author Comment

by:-Juddy-
ID: 22701221
Hi Dannywareham;

This is what I cannot understand - why is the query design disappearing?

As you say - I am not writing or removing the query object. before running the code the query is there after running the code - i go to sql view and I see "Select ;". if I go to the query grid I see no fields in the grid for the queries that have disappeared.

The queries are still there in name alone - but the sql and /or design has gone!

When tracing the code I have narrowed it down to the docmd.outputto statement - but cannot see why this is happening or what I need to do to fix it.

Hope this helps to clarify.
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 22701235
It may be this line:

file = location + RSReports.Fields(1).Value

as "file" gets killed later on.

Change the number to the name of the query field:

file = location + RSReports.Fields("ReportName")

See if that works
0
 
LVL 3

Accepted Solution

by:
-Juddy- earned 0 total points
ID: 22719165
Hi Dannywareham;

thanks for your continued input

I have now tried changing how the recordset is access as you suggested.
unfortunately there is no change in the behaviour.

any other suggestions are gratefully received.
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 22719299
COuld you post your database?
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

660 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question