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

[ChipStewart]

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:

Private Sub Export_Potential_Client_Data_Click()
    On Error GoTo Err_Export_Potential_Client_Data_Click
    Dim stDocName As String
    Dim strOriginalRecordSource As String
    Dim strNewRecordSource As String

    stDocName = "Export Potential Client"
    strOriginalRecordSource = Me.RecordSource
    strNewRecordSource = "SELECT * FROM [Potential Clients] WHERE [Number] = " & Me![Number]
    Me.RecordSource = strNewRecordSource
    DoCmd.OutputTo ObjectType:=acOutputReport, ObjectName:="Export Potential Client Report", OutputFormat:=acFormatTXT, Outputfile:="C:\Clients\Client Data.csv", AutoStart:=True
    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?

Thanks

Chip Stewart

[NeoPa]

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 :

Call DoCmd.TransferText(TransferType:=acExportDelim _
                      , TableName:={Name of table or query} _
                      , 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.

[zmbd]

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.collec tion might be another route to take as the underlying recordset for the report would be "filtered" based on the passed value.

[NeoPa]

That's the word I was looking for - TempVars!!!

[zmbd]

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 ;)

[ChipStewart]

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?

Thanks.

[NeoPa]

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 :

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.

[ChipStewart]

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

Private Sub Export_Client_Data_Click()

On Error GoTo Err_Export_Client_Click

    Dim stDocName As String
    stDocName = "Export_Client_Data"
    DoCmd.OpenQuery stDocName, acNormal, acEdit    
    DoCmd.OutputTo ObjectType:=acOutputTable, ObjectName:="Client_Data", Outputfile:="c:\Clients\Client Data.xlsx", AutoStart:=True

Exit_Export_Client_Click:
    Exit Sub

Err_Export_Client_Click:
    MsgBox Err.Description
    Resume Exit_Export_Client_Click
    
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?

[NeoPa]

I'm away for a few days. I'll check in again next week once I've caught up with my work backlog.

[NeoPa]

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.