Solved

Query content disappears when docmd.outputto code is run

Posted on 2008-10-08
11
593 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-
  • 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now