By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,221 Members | 507 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,221 IT Pros & Developers. It's quick & easy.

Using DoCmd.OutputTo to Export Query of the Current Record Only

P: 3
I'm trying to use a few posts from this forum, including use DoCmd.OutputTo to export a PDF of the current record on a report? to send a single record displayed in a form of my potential client data to a text file to use with Outlook and QuickBooks. I have a button on the form and the following code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Export_Potential_Client_Data_Click()
  2.     On Error GoTo Err_Export_Potential_Client_Data_Click
  3.     Dim stDocName As String
  4.     Dim strOriginalRecordSource As String
  5.     Dim strNewRecordSource As String
  7.     stDocName = "Export Potential Client"
  8.     strOriginalRecordSource = Me.RecordSource
  9.     strNewRecordSource = "SELECT * FROM [Potential Clients] WHERE [Number] = " & Me![Number]
  10.     Me.RecordSource = strNewRecordSource
  11.     DoCmd.OutputTo ObjectType:=acOutputReport, ObjectName:="Export Potential Client Report", OutputFormat:=acFormatTXT, Outputfile:="C:\Clients\Client Data.csv", AutoStart:=True
  12.     Me.RecordSource = strOriginalRecordSource
I've spent hours playing with it, but at this point it runs and opens an Excel spreadsheet (I'll turn the AutoStart off once I get everything up and running) with ALL the records displayed. I cannot get it to export the current record only!

I'm sure its something simple, but I can't seem to figure it out. Any ideas?


Chip Stewart
Aug 23 '18 #1
Share this Question
Share on Google+
9 Replies

Expert Mod 15k+
P: 31,770
Hi Chip.

You're confusing the currently open version of your Report object with the one referred to in DoCmd.OutputTo().

One is open already. This is simply an instance, or copy, of the original design held as [Export Potential Client Report]. The other is a simple reference to the design itself - NOT your open instance.

So, what you see on the screen is affected by your changing its RecordSource. The one that you want DoCmd.OutputTo() to sned as a file is certainly not.

Let's get back to a simpler approach and remember that the Report object is based off a table or query and that's the data you want saved - not anything in the report itself - just the data. That's best done using the following template code :
Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.TransferText(TransferType:=acExportDelim _
  2.                       , TableName:={Name of table or query} _
  3.                       , FileName:={Name of file to hold data})
You'll have noticed that this doesn't filter the results for you any more than trying to do it by Report did.

I don't think there's an easy way to do that in code but you can create a Query (QueryDef) that filters based on a value stored in one of those database variables whose name I just can't dig up ATM.

You can also create a new QueryDef in code if you need to but I'll get to that another time.
Aug 23 '18 #2

Expert Mod 5K+
P: 5,397
I agree with NeoPa on the method for simple text-file export; however, it begs asking: is there something within the report that is important to have in the text-file?

A parameter based query using either the open form or a tempvars.collection might be another route to take as the underlying recordset for the report would be "filtered" based on the passed value.
Aug 24 '18 #3

Expert Mod 15k+
P: 31,770
That's the word I was looking for - TempVars!!!
Aug 24 '18 #4

Expert Mod 5K+
P: 5,397
TempVars - Wonderful little creatures!
The 2010-navigation-control has a few quirks, on of which is that clicking on one of the navigation-command-buttons (NCB)reloads the associated form from scratch into the navigation-subform-control and discards everything from the previous form so passing information becomes a bit more combersom. Even the WHERE property of the call NCB isn't always appropriate or robust enough - especially if the value needs to be passed several times - the tempvars collection works like a charm and is error-resistant which allows me to use my new/old favorite method err.raise().

However, me thinks to go farther would be off topic ;)
Aug 24 '18 #5

P: 3
I'm afraid I'm a bit lost here - could you dumb it down? I'm just starting out with this stuff. Any good resources you could recommend to get up to speed fast?

Aug 24 '18 #6

Expert Mod 15k+
P: 31,770
Hi Chip.

You may find TempVars Object (Access) helpful to work with. I'd say ignore any use of macros unless you like problems.

TempVars is essentially a collection of values that can be referenced by name from within your code as well as from within SQL and The Expression Service. IE. pretty well anywhere from within your project. Their scope is global so you needn't worry about where they're set and where used. Very simple.

Once you've set a value in there and given it a name you can reference it anywhere until you close your project or set it to a different value. A reference consists very simply of saying :
Expand|Select|Wrap|Line Numbers
  1. TempVars!{ValueName}
Where {ValueName} is the name you've assigned to that value.

It's very handy for just the sort of work you're doing where you don't have a good way of filtering a QueryDef, for instance.

PS. It's available only from Access 2007 and beyond as it was only introduced then.
Aug 24 '18 #7

P: 3
Okay . . . Here's what I have now:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Export_Client_Data_Click()
  3. On Error GoTo Err_Export_Client_Click
  5.     Dim stDocName As String
  6.     stDocName = "Export_Client_Data"
  7.     DoCmd.OpenQuery stDocName, acNormal, acEdit    
  8.     DoCmd.OutputTo ObjectType:=acOutputTable, ObjectName:="Client_Data", Outputfile:="c:\Clients\Client Data.xlsx", AutoStart:=True
  10. Exit_Export_Client_Click:
  11.     Exit Sub
  13. Err_Export_Client_Click:
  14.     MsgBox Err.Description
  15.     Resume Exit_Export_Client_Click
  17. End Sub

I have it opening the new table and exporting until I can get the exporting right. The new table is exactly what I want, but when it opens in Excel, all I get are the headers without the current record.

I'm still trying to figure out how to use TempVars here. Any suggestions?
Sep 18 '18 #8

Expert Mod 15k+
P: 31,770
I'm away for a few days. I'll check in again next week once I've caught up with my work backlog.
Sep 21 '18 #9

Expert Mod 15k+
P: 31,770
Hi Chip.

Holler if you're still interested in assistance on this one. My absence has been extended but I'll keep it in my list for when I'm back and more available if you LMK you're still active with this question.
Oct 8 '18 #10

Post your reply

Sign in to post your reply or Sign up for a free account.