Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 619
  • Last Modified:

Query content disappears when docmd.outputto code is run

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
-Juddy-
Asked:
-Juddy-
  • 6
  • 5
1 Solution
 
dannywarehamCommented:
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
 
-Juddy-Author Commented:
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
 
dannywarehamCommented:
Is this line getting a value from the recordset:

RSReports.Fields(0).Value

Do you have permission to write to teh file location?
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
-Juddy-Author Commented:
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
 
-Juddy-Author Commented:
The RSReports.Fields(0).value line is populated correctly
0
 
dannywarehamCommented:
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
 
dannywarehamCommented:
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
 
-Juddy-Author Commented:
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
 
dannywarehamCommented:
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
 
-Juddy-Author Commented:
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
 
dannywarehamCommented:
COuld you post your database?
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now