?
Solved

OutputTo acFormatSNP using a filter - Open Report Works - OutputTo has no Filter Option

Posted on 2005-03-04
15
Medium Priority
?
1,081 Views
Last Modified: 2008-01-09
Experts

I filter my report using Combo Values from a Form

When I use Docmd.OpenReport repName, repmode, repName, s (where s is the Filter) - Code below - it filters accordingly

But there is not option in Docmd.OutputTo to filter the report with


Code****************************************************************************
Public Sub RunWeeklyFigureReportCompanySnap(ByVal repName As String, ByVal repMode As Integer)
Dim s As String
    s = "[NAM] like '" + Nz(Forms![frmReportFilters]![cmbNAM], "*") + "'"
    s = s + " and [Discount] " + Nz(Forms![frmReportFilters]![cmbDiscount], "like '*'")
 
    DoCmd.OpenReport repName, repMode, repName, s
   
End Sub
CodeEnd**************************************************************************



Calledby**************************************************************************
Sub CompanyReportSnap()
    If DoesTableExist("tblCompanyWeekEnding") Then
        CurrentDb.Execute "DROP TABLE tblCompanyWeekEnding"
    End If
        RunWeeklyFigureReportCompanySnap "rptCompany", acViewPreview
End Sub
CalledbyEnd***********************************************************************


Instead of:

     DoCmd.OpenReport repName, repMode, repName, s

I want to Use Something Like:

DoCmd.SelectObject acReport, repName, True
DoCmd.OutputTo acOutputReport, repName, acFormatSNP, "S:\TurnOverReports\" & Forms!frmReportFilters!cmbPeriod.Value & "\" & _
Forms!frmReportFilters!cmbNAM.Value & " - Speedy Hire Direct - Chronological Detailed Turnover Report - Period " &  _
Forms!frmReportFilters!cmbPeriod.Value & ".snp"

But this doesn't give me the option to Filter Using "s"

Any Help would be very much appreciated - If any other info is needed, please ask!!!

Thanks in Advance

Speedy
0
Comment
Question by:SpeedyDirect
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +2
15 Comments
 
LVL 26

Expert Comment

by:dannywareham
ID: 13457658
How about creating the filter in the SQL statement that the report is based on...?

Public mySQL as String   '(put this in a module)

In the form to add the filter:

Select Case myComboBox

Case "Value 1"
mySQL = "SELECT blah blah"
Case "Value 2"
mySQL = "SELECT blah blah2"
End Select

Then on the OnLOad of the report:

Me.Recordsource = mySQL
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 2000 total points
ID: 13457692
You can manipulate the query from code before the report is activated or pass the selection using a public variable.

For the public variable solution use a module and define:
Public pbWhere as string

In the code use the normal build of the sCriteria.

In the report's OnOpen event use:

IF Len(pbWhere) > 0 then
   me.filter = pbWhere
   me.filterOn = True
endif

Clear ?

Nic;o)
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13457716
Alternatively, you can base the report on a query that takes parameters from a form's controls instead of passing a filter to it. You can just set the form's controls to the values you want for the filter, and the report will operate correctly for both printing and outputting.
0
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 

Author Comment

by:SpeedyDirect
ID: 13457734
Sorry Nico

>>>In the code use the normal build of the sCriteria.

How does pbWhere know what the sCriteria is?

by the way...these reports have dynamic labels on them & also use (at Report_open) a dynamic crosstab to create a temp table to query from...

Cheers

0
 
LVL 54

Expert Comment

by:nico5038
ID: 13457752
Oops, too cryptical read for:
In the code use the normal build of the sCriteria.
In the code build the criteria and put that into pbWhere before starting the report ro empty pbWhere when not needed.

Nic;o)
0
 
LVL 54

Expert Comment

by:nico5038
ID: 13457770
Shane's form-parameter option is also possible, but I prefer a mechanism with a public variable as that will not be dependent on a "hard coded" formfieldnames. Thus renaming a form and/or field won't have an effect on the report and the Where string build will be just as flexible as you use the passing of criteria to a report using the Filter option on the docmd.openreport.

Nic;o)
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13457791
True, but I find that form/field names do tend to remain largely static. In general, I only usually allow reports to be opened from a particular form in any case - users never have direct access to report objects, so it's not an issue for me but its a case of using whichever technique is most suitable for your application.
0
 

Author Comment

by:SpeedyDirect
ID: 13458292
OK

All Points go to Nico as his solution is perfect for my Application

Code Below as used in Application (well bits of it) - Hope this helps others who view this Q   -


Global Declaration***********************************************************************

Public pbWhere As String
Public SnapFileName As String

Global Declaration End***********************************************************************



sCriteria Build****************************************************************************

Public Sub RunWeeklyFigureReportCompanySnap(ByVal repName As String, ByVal repMode As Integer)
Dim s As String
    s = "[NAM] like '" + Nz(Forms![frmReportFilters]![cmbNAM], "*") + "'"
    s = s + " and [Discount] " + Nz(Forms![frmReportFilters]![cmbDiscount], "like '*'")
   
    pbWhere = s
    DoCmd.OutputTo acOutputReport, repName, acFormatSNP, SnapFileName
 
End Sub

sCriteria Build End***********************************************************************



Report_Open Event**********************************************************************

If Len(pbWhere) > 0 Then
        Me.Filter = pbWhere
        Me.FilterOn = True
End If

Report_Open Event End*******************************************************************


Example Run Report Command**************************************************************

Sub CompanyReportSnap()
    If DoesTableExist("tblCompanyWeekEnding") Then
        CurrentDb.Execute "DROP TABLE tblCompanyWeekEnding"
    End If
        RunWeeklyFigureReportCompanySnap "rptCompany", acViewPreview
End Sub

Example Run Report Command End***********************************************************



NOW FOR THE HORRIBLE BIT


On Click Event Code**********************************************************************

Sub OutputSnapshotPack()
    If IsNull([Forms]![frmReportFilters].[cmbPeriod].Value) Then
        MsgBox "You haven't Selected a Period", vbOKOnly, "Sales Figure Database"
        cmbPeriod.SetFocus
    Else
   
        SnapFileName = "S:\TurnOverReports\Period "
        SnapFileName = SnapFileName & Forms!frmReportFilters!cmbPeriod.Value & " - "
        SnapFileName = SnapFileName & Forms!frmReportFilters!cmbNAM.Value
        SnapFileName = SnapFileName & " - Speedy Hire Direct Chronological Detailed Turnover Report"
        SnapFileName = SnapFileName & ".snp"
       
        CompanyReportSnap
       
        SnapFileName = "S:\TurnOverReports\Period "
        SnapFileName = SnapFileName & Forms!frmReportFilters!cmbPeriod.Value & " - "
        SnapFileName = SnapFileName & Forms!frmReportFilters!cmbNAM.Value
        SnapFileName = SnapFileName & " - Speedy Hire Direct Alphabetical Detailed Turnover Report"
        SnapFileName = SnapFileName & ".snp"
       
        AlphaCompanyReportSnap
       
        SnapFileName = "S:\TurnOverReports\Period "
        SnapFileName = SnapFileName & Forms!frmReportFilters!cmbPeriod.Value & " - "
        SnapFileName = SnapFileName & Forms!frmReportFilters!cmbNAM.Value
        SnapFileName = SnapFileName & " - Speedy Hire Direct Chronological Variance Turnover Report"
        SnapFileName = SnapFileName & ".snp"
               
        CompanyVarianceReportSnap
       
        SnapFileName = "S:\TurnOverReports\Period "
        SnapFileName = SnapFileName & Forms!frmReportFilters!cmbPeriod.Value & " - "
        SnapFileName = SnapFileName & Forms!frmReportFilters!cmbNAM.Value
        SnapFileName = SnapFileName & " - Speedy Hire Direct Alphabetical Variance Turnover Report"
        SnapFileName = SnapFileName & ".snp"
       
        AlphaVarianceReportSnap
       
       
    End If
   
        SnapFileName = "S:\TurnOverReports\Period "
        SnapFileName = SnapFileName & Forms!frmReportFilters!cmbPeriod.Value & " - "
        SnapFileName = SnapFileName & Forms!frmReportFilters!cmbNAM.Value
        SnapFileName = SnapFileName & " - Speedy Hire Direct 2003 Turnover Report"
        SnapFileName = SnapFileName & ".snp"
       
        RunYearlyReportCompanySnap "rpt2003Turnovers_Crosstab", acViewPreview
       
        SnapFileName = "S:\TurnOverReports\Period "
        SnapFileName = SnapFileName & Forms!frmReportFilters!cmbPeriod.Value & " - "
        SnapFileName = SnapFileName & Forms!frmReportFilters!cmbNAM.Value
        SnapFileName = SnapFileName & " - Speedy Hire Direct 2004 Turnover Report"
        SnapFileName = SnapFileName & ".snp"
       
        RunYearlyReportCompanySnap "rpt2004Turnovers_Crosstab", acViewPreview
   
End Sub

On Click Event Code End******************************************************************


Many Thanks to shanesuebsahakarn & dannywareham  for your valued input BUT:

Nico got it

Thankyou very much for the quick responses

Speedy
0
 
LVL 54

Expert Comment

by:nico5038
ID: 13458382
Glad I could help Speedy, but wondering how you can run RunYearlyReportCompanySnap "rpt2003Turnovers_Crosstab", acViewPreview and RunYearlyReportCompanySnap "rpt2004Turnovers_Crosstab", acViewPreview with the same criteria...

Nic;o)
0
 

Author Comment

by:SpeedyDirect
ID: 13458488
Sorry Nico...bit of a bad explaination from me

>>>>>>>>>>Code Below as used in Application (well bits of it) - Hope this helps others who view this Q   -

WELL BITS OF IT - being the keyword ;-)....only showed an example of the code....

So here is the rest of the criteria build:


Public Sub RunYearlyReportCompanySnap(ByVal repName As String, ByVal repMode As Integer)
Dim s As String
    s = "[NAM] like '" + Nz(Forms![frmReportFilters]![cmbNAM], "*") + "'"
   
    pbWhere = s
    DoCmd.OutputTo acOutputReport, repName, acFormatSNP, SnapFileName
   
End Sub


Speedy (Dave)
0
 
LVL 54

Expert Comment

by:nico5038
ID: 13458531
You can shorten that to:

Public Sub RunYearlyReportCompanySnap(ByVal repName As String, ByVal repMode As Integer)

    pbWhere = "[NAM] like '" + Nz(Forms![frmReportFilters]![cmbNAM], "*") + "'"
    DoCmd.OutputTo acOutputReport, repName, acFormatSNP, SnapFileName
   
End Sub  

But I see two reports, one for 2003 and 2004. I would have had a comboYear defined and one report with a Year selection so I would have just one report for all years :-)

Nic;o)
0
 

Author Comment

by:SpeedyDirect
ID: 13458700
OIC - I can see what you mean....

Friday blues - trouble is....I hardcoded the Fieldnames on the report to be the same as the year....which then runs off a crosstab erm...called the same as the year...lol

Sounds like a job for next week....(or shall I just leave it as this only needs changing once a year) ;-0

Many thanks Nico

Dave
0
 

Expert Comment

by:ernestoariel
ID: 13593899
Guys, are we assuming here that the report open event actually triggers when you export the report through the DoCmd.Outputto action?.  I have done as recommended here (I'm using adp), but my the export contains all records, not the filtered ones.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13593927
If you have an additional question regarding the solution presented here, please post a new question of your own with a link back to this one.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

777 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