DoCmd.OutputTo parameters (1 Viewer)

Tupacmoche · Mar 12, 2019

Hi Report Masters,

I have an Access report with a long text/Memo field column that keeps getting cut off. After doing research I found out that reports that are exported using the Database Tools -> Export ->Excel get truncated since the only export to older versions of Excel. This is true and clear as the only chooses are older versions. Is there any way around this? Some patch or something that will give access to the most current version of Excel? I also learned that if I run a query and export it I will be able to export to the latest version of Excel. I added a button to run the existing query as follows:

DoCmd.OutputTo acOutputQuery, "qry_Financial_Data", acFormatXLSX, , True

While this runs it does no filtering as when the report is selected from the menu. My question is does DoCmd.OutuptTo accept parameters to filter which report to run and any other filters that may be needed?

June7 · Mar 12, 2019

So does the field truncate if you use acOutputReport?
First open the report with DoCmd.OpenReport
then run OutputTo.

I don't use dynamic parameters in queries. I use VBA to set report filter in the OpenReport method.

If you must use dynamic parameterized query, then I expect have to use QueryDefs to pass values to the parameters. Requires use of PARAMETERS clause in query. https://stackoverflow.com/questions...that-i-can-import-an-access/55063945#55063945

Uncle Gizmo · Mar 12, 2019

I suspect you want to grab the filters from a report? Not sure if that's possible, haven't tried it. I know for a fact it wouldn't have been possible a few years ago! But these days reports are a slightly different animal so it might well be now.

I would be more inclined to use a sub-form instead of a report. There's an example on my website along with some excellent code provided by BTAB Developments, there's a link to BTAB on my website here:-

Show/Hide & Export to Excel

Tupacmoche · Mar 13, 2019

Follow-up:
This is the code that I added to the on Click event of my 'Export to Excel' button.

Code:

DoCmd.OutputTo acOutputQuery, "qry_Financial_Data", acFormatXLSX, , True

As, I mentioned there are no parameter but I need them. I have attached a screen shot of the dialog box that collects the parameters and here is the code behind it:

Code:

Private Sub cmd_run_report_Click()

 Dim str_condition As String
 Dim str_resp As Integer 
 Dim str_sql As String
  
  str_resp = 0
 
 'The condition will be set based on the Report Name
 
If Me.Combo4.Value Like "*contact*" Then
   str_condition = "Event_Name='" & Me.Combo2.Value & "' and Contact_list Like '*" & IIf(Len(Me.txt_contact.Value) < 1, "", Me.txt_contact.Value) & "*'"
Else
   str_condition = "Event_Name='" & Me.Combo2.Value & "'"
End If

DoCmd.OpenReport "rpt_" & Me.Combo4.Value, acViewReport, , str_condition
   
'DoCmd.OpenReport "rpt_" & Me.Combo4.Value, acViewReport, , "Event_Name='" & Me.Combo2.Value & "'"
'DoCmd.OpenReport "rpt_" & Me.Combo4.Value

 End Sub

As, you can see the first report parameter is just the name along with the event ID to filter the query.

I saw the information about the qryParameter but need some help to impliment them.

June7 · Mar 13, 2019

Exactly what do you not understand about instructions in link using PARAMETERS? What attempt did you make to use the approach?

Tupacmoche · Mar 15, 2019

Public Sub ExecQryWPara()

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rptName As String
QryName = "qry_Financial_Data"

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qry_Financial_Data")

'Set the value of the QueryDef's parameter
qdf.Parameters("").Value =

'Execute the query
qdf.Execute dbFailOnError

'Clean up
qdf.Close
Set qdf = Nothing
Set dbs = Nothing

End Sub

Tupacmoche · Mar 15, 2019

Code:

Public Sub ExecQryWPara()

    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rptName As String
    QryName =  "qry_Financial_Data"

    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("qry_Financial_Data")

    'Set the value of the QueryDef's parameter
    qdf.Parameters("").Value =

    'Execute the query
        qdf.Execute dbFailOnError
    
        'Clean up
        qdf.Close
        Set qdf = Nothing
        Set dbs = Nothing 

End Sub

June7 · Mar 15, 2019

Well, you aren't actually setting any parameter to anything. And there is no Value property of the parameter used in example code.

Why would you need to Execute query?

Post the SQL statement of qry_Financial_Data.

You should read the accepted answer provided in the referenced thread. Look at item 3.

Tupacmoche · Mar 22, 2019

June7

Sorry but I was posting and put this code here by mistake.

MajP · Mar 22, 2019

Although a parameterized query is the cleaner way to go, if you open the filtered report first then output it the filter is applied to the output. Here is a generic function I use for PDFs.

Public Sub OpenReportInPDF(ReportName As String, Optional ReportFilter As String = "None")
On Error GoTo err_export
If ReportFilter = "None" Then
DoCmd.OutputTo acOutputReport, ReportName, acFormatPDF, , True
Else
DoCmd.OpenReport ReportName, acViewPreview, , ReportFilter, acHidden
DoCmd.OutputTo acOutputReport, ReportName, acFormatPDF, , True
End If
If repIsLoaded(ReportName) Then DoCmd.Close acReport, ReportName
Exit Sub
err_export:
MsgBox Err.Description
Forms("frmReportsMenu").Visible = True
End Sub

Note the else portion where there is a filter.

MajP · Mar 22, 2019

Never mind, I see now it is a query. I am guessing the same approach to open it first may work. But untried.

Tupacmoche · Mar 28, 2019

Hi June7,

Here is my revised code:

Code:

Private Sub btnQryExport_Click()
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qry_Financial_data")

'Set the value of the QuerDef's parameter
qdf.Parameters("EventID").Value = "151"

'Execute the query
qdf.Execute dbFailOnError

'Clear up
qdf.Close
rst.Close

Set qdf = Nothing
Set rst = Nothing
Set dbs = Nothing

 End Sub

I get the message item not found in this collection. Can you please give me direction.

Tupacmoche · Mar 28, 2019

June7

The error is on this line:

Code:

qdf.Parameters("EventID").Value = "151"

June7 · Mar 28, 2019

Remove .Value

Post the SQL statement of qry_Financial_data

Tupacmoche · Mar 28, 2019

Here is the query code. Please note the genesis of this post. Since an exported Access report can only export text data with a limitation, I opted to export using a query which dose not have the same limitation of 256 characters. My goal is to 'grab' the parameters entered into the report selection box and reuse them in the query to simplify the process for the user and circumvent the character limit. Please see thred #4 for parameter selection box along with code.

Code:

SELECT 
Event_Participant.ParticipantID, 
Event_Participant.EventID, 
Event_Participant.Contact, 
Event_Participant.Note, 
qry_participant_fee_amt.[Fee Amount], 
get_giftype_list([Event_Participant]![EventID],
[Event_Participant]![ParticipantID]) AS [Level], 
qry_participant_fee_amt.[Number of Ticket], 
qry_amt_event_participant.SumOfAmount, 
qry_amt_event_participant.[Last Payment], 
Participant.InformalSal, 
Participant.Name1, 
Participant.LastName, 
Event.Event_Name, 
qry_participant_fee_amt.[JourUndwrtg Amount], 
[Fee Amount]-[SumOfAmount] AS Balance, 
Event_Participant.Contact2, 
nz([qry_participant_fee_amt]![ProgramName],
UCase([Participant]![LastName]) & IIf([Participant]![LastName] Is Null,'',
IIf([Participant]![FirstName] Is Null,'',', ' & [Participant]![FirstName]))) AS Report_Name, 
[Event_Participant]![Contact] & 
IIf(Len(nz([Event_Participant]![Contact2],'X'))=1,""," / " & [Event_Participant]![Contact2]) AS Contact_List, 
([Participant]![LastName]) & nz([Participant]![FirstName],'') & CStr([Participant]![ID]) AS Last_ID, 
qry_participant_fee_amt.[Contribution Amount], 
IIf([Address]![AddressID] Is Null, [Participant]![Company],[Address]![Company]) AS Company, 
IIf([Address]![AddressID] Is Null,[Participant]![Address1],[Address]![Address1]) AS Address1, 
IIf([Address]![AddressID] Is Null,[Participant]![Address2],[Address]![Address2]) AS Address2, 
IIf([Address]![AddressID] Is Null,[Participant]![Address3],[Address]![Address3]) AS Address3, 
IIf([Address]![AddressID] Is Null,[Participant]![City],[Address]![City]) AS City, 
IIf([Address]![AddressID] Is Null,[Participant]![State],[Address]![State]) AS State, 
IIf([Address]![AddressID] Is Null,[Participant]![Zip],[Address]![Zip]) AS Zip, 
IIf([Address]![AddressID] Is Null,[Participant]![Fax],[Address]![Fax]) AS Fax, 
IIf([Address]![AddressID] Is Null,[Participant]![Tel],[Address]![Phone]) AS Phone, 
IIf([Address]![AddressID] Is Null,[Participant]![Email],[Address]![Email]) AS Email, Participant.FirstName

FROM ((((Event_Participant 
INNER JOIN qry_participant_fee_amt 
ON (Event_Participant.ParticipantID=qry_participant_fee_amt.ParticipantID) AND 
(Event_Participant.EventID=qry_participant_fee_amt.EventID)) 
LEFT JOIN qry_amt_event_participant 
ON (Event_Participant.ParticipantID=qry_amt_event_participant.ParticipantID) AND 
(Event_Participant.EventID=qry_amt_event_participant.EventID)) 
INNER JOIN Participant 
ON Event_Participant.ParticipantID=Participant.ID) 
INNER JOIN Event 
ON Event_Participant.EventID=Event.EventID) 
LEFT JOIN Address 
ON (Event_Participant.ParticipantID=Address.ParticipantID) AND 
(Event_Participant.AddressID=Address.AddressID)

WHERE (((qry_participant_fee_amt.[Fee Amount])>0));

Tupacmoche · Mar 28, 2019

Please note that the query is completely vetted and is the Data -> Record Source for the report that runs perfectly fine with the exception that text fields are cut off because of the character limitation.

June7 · Mar 28, 2019

Okay, does removing .Value make a difference?

Tupacmoche · Mar 28, 2019

No it did not. Sorry, I should have mentioned that I tried that.

Code:

qdf.Parameters("EventID") = "151"

Tupacmoche · Mar 28, 2019

I have revised the code once again and tested it with a MsgBox(). It give the correct record count but leaves me still trying to implement the parameter portion of the code:

Code:

Private Sub btnQryExport_Click()
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Set dbs = CurrentDb

'Set the value of the QuerDef's parameter
Set qdf = dbs.QueryDefs("qry_Financial_data")

    'Set parameter 1
    'Set parameter 2

'Attempt to create the recordset.
Set rst = qdf.OpenRecordset()
rst.MoveLast
MsgBox "Recordset created with " & rst.RecordCount & " records.", vbOKOnly + vbInformation, "?"

'Clear up
'qdf.Close
'rst.Close

Set qdf = Nothing
Set rst = Nothing
Set dbs = Nothing

 End Sub

June7 · Mar 28, 2019

Okay, cannot "Execute" a SELECT query - only SQL actions (UPDATE, INSERT, DELETE). As far as I can tell, your query is just a SELECT.

Finally got around to testing code. This is not working to export a query object. The original example in the link I provided was to open a recordset and needed because code was behind Excel, not Access.

Turns out all you need is query WHERE clause to reference controls on form for criteria. No QueryDef code needed at all. And the PARAMETERS clause is not even required in my test. My bad for misdirection. So simply the OutputTo code you show in original post. Just adjust the query. Or build another query that pulls from the other and has the dynamic parameters referencing form controls.

Need to include a file path/name in the OutputFile argument.

DoCmd.OutputTo parameters (1 Viewer)

Registered User.

AWF VIP

Nifty Access Guy

Registered User.

Attachments

AWF VIP

Registered User.

Registered User.

AWF VIP

Registered User.

You've got your good things, and you've got mine.

You've got your good things, and you've got mine.

Registered User.

Registered User.

AWF VIP

Registered User.

Registered User.

AWF VIP

Registered User.

Registered User.

AWF VIP

Similar threads

Users who are viewing this thread