Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1089
  • Last Modified:

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

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
SpeedyDirect
Asked:
SpeedyDirect
  • 5
  • 4
  • 3
  • +2
1 Solution
 
dannywarehamCommented:
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
 
nico5038Commented:
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
 
shanesuebsahakarnCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
SpeedyDirectAuthor Commented:
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
 
nico5038Commented:
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
 
nico5038Commented:
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
 
shanesuebsahakarnCommented:
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
 
SpeedyDirectAuthor Commented:
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
 
nico5038Commented:
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
 
SpeedyDirectAuthor Commented:
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
 
nico5038Commented:
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
 
SpeedyDirectAuthor Commented:
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
 
ernestoarielCommented:
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
 
shanesuebsahakarnCommented:
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
 
ernestoarielCommented:
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now