OpenArgs and OutputTo

P

PNGBill

Win10 Office Pro 2016
Local time
Tomorrow, 05:48
Joined
Jul 15, 2008
Messages
2,269
Hi Forum, access 2010.

I want to filter a report for a variable number of days and then email same.
This code is on the form command button and the operator enters W, F, M or X to declare the number of days.
Code: 
Dim Response As String
    Dim ReportPeriod As String
    
    Response = InputBox("Enter W for Week, F for Fortnight, or M for Month to select " & _
                        "what Repayment Report to send. Enter X to escape and not send a report", vbInformation)
        If Response = "W" Then
            ReportPeriod = "Week"
        ElseIf Response = "F" Then
            ReportPeriod = "Fortnight"
        ElseIf Response = "M" Then
            ReportPeriod = "Month"
        ElseIf Response = "X" Then
            GoTo Exit_Procedure
        Else
            MsgBox "You must enter either W, F, M or X. Try again."
            GoTo Exit_Procedure
        End If
        'Produce and save report
    
    DoCmd.OpenReport "rptRepaymentsByStatementReport", acViewNormal, , , acWindowNormal, ReportPeriod
    DoCmd.OutputTo acOutputReport, "rptRepaymentsByStatementReport", acFormatPDF, "W:\Attachments\rptRepaymentsByStatementReport.pdf", 0, , , acExportQualityPrint

This code is on the report Open event
Code: 
Dim strOpenArg As String
    strOpenArg = Me.OpenArgs()
    
    Select Case strOpenArg
    Case "Week"
        Me.Filter = "(StatementDate) Between Date()-7 And Date()"
        Me.FilterOn = True
    Case "Fortnight"
        Me.Filter = "(tblBankStatements.StatementDate) Between Date()-14 And Date()"
        Me.FilterOn = True
    Case "Month"
        Me.Filter = "(tblBankStatements.StatementDate) Between Date()-31 And Date()"
        Me.FilterOn = True
    Case Else
        'No OpenArg so do nothing
    End Select

It appears to work in that a report for either 7, 14 or 31 days is produced.:)
But..
Then a full report for the last 11 years is also produced.:eek:

I suspect OutputTo is running the report instead of accepting the one produced by the code earlier ??

Appreciate any advice. Bill
 
Hint: OutputTo reopens your report so your OpenArgs becomes "" everytime OutputTo gets executed.

Make ReportPeriod a global variable and replace OpenArgs in the SELECT CASE statement with ReportPeriod. Also, get rid of your OpenReport line.
 
Thanks vbaInet:)
I used used TempVars in place of Global variables and with your advice, this works.

Command Button code
Code: 
Dim Response As String
    
    Response = InputBox("Enter W for Week, F for Fortnight, or M for Month to select " & _
                        "what Repayment Report to send. Enter X to escape and not send a report", vbInformation)
        If Response = "W" Then
            TempVars!ReportPeriod = "Week"
        ElseIf Response = "F" Then
            TempVars!ReportPeriod = "Fortnight"
        ElseIf Response = "M" Then
            TempVars!ReportPeriod = "Month"
        ElseIf Response = "X" Then
            GoTo Exit_Procedure
        Else
            MsgBox "You must enter either W, F, M or X. Try again."
            GoTo Exit_Procedure
        End If
        
        'Produce and save report
    DoCmd.OutputTo acOutputReport, "rptRepaymentsByStatementReport", acFormatPDF, "W:\Attachments\rptRepaymentsByStatementReport.pdf", 0, , , acExportQualityPrint

Report Open Event Code
Code: 
Select Case TempVars!ReportPeriod
    Case "Week"
        Me.Filter = "(StatementDate) Between Date()-7 And Date()"
        Me.FilterOn = True
    Case "Fortnight"
        Me.Filter = "(tblBankStatements.StatementDate) Between Date()-14 And Date()"
        Me.FilterOn = True
    Case "Month"
        Me.Filter = "(tblBankStatements.StatementDate) Between Date()-31 And Date()"
        Me.FilterOn = True
    Case Else
        'No TempVars so do nothing
    End Select
 
Thanks guys for this thread, it's been helpful to me today in solving a new problem that's arisen with making text boxes etc visible based on different variables when creating a PDF report and attaching it to an email without opening the report first! :)
 
Good to know Jeff. How did you accomplish it, with variables? If yes, then there's an easier way.
 
vbaInet said:
Good to know Jeff. How did you accomplish it, with variables? If yes, then there's an easier way.
Click to expand...

Hey vbaInet, thanks for the reply!

It's actually a bit more simple than I probably made out, but I used more or less the same code as PNGBill's:

Command Button code (with all the other fluff taken out):

Code: 
If Me.OrderType = "TUCO" Then
TempVars!OrderType = "TUCO"
End If

Report Open Event Code (minus fluff):
Code: 
Select Case TempVars!OrderType

Case "TUCO"
Me.lblTUCOInvoice.Visible = True

Case Else
Me.lblTUCOInvoice.Visible = False

End Select

:)
 
Ok, it was what I was thinking of anyway.

Here's what you can do instead:

1. Set the Can Grow and Can Shrink properties of the textbox to Yes
2. Change the Control Source of the textbox to:
Code: 
=IIF(TempVars!OrderType="Tuco", [FieldName], Null)
3. Comment out the code in the Open event and test it out.

What will happen is that the textbox will shrink (and become "invisible") when the condition is not met.
 
vbaInet said:
Ok, it was what I was thinking of anyway.

Here's what you can do instead:

1. Set the Can Grow and Can Shrink properties of the textbox to Yes
2. Change the Control Source of the textbox to:
Code: 
=IIF(TempVars!OrderType="Tuco", [FieldName], Null)
3. Comment out the code in the Open event and test it out.

What will happen is that the textbox will shrink (and become "invisible") when the condition is not met.
Click to expand...

Hey vbaInet, thanks for the tip...I'm going to try this out now!
 
vbaInet said:
Ok, it was what I was thinking of anyway.

Here's what you can do instead:

1. Set the Can Grow and Can Shrink properties of the textbox to Yes
2. Change the Control Source of the textbox to:
Code: 
=IIF(TempVars!OrderType="Tuco", [FieldName], Null)
3. Comment out the code in the Open event and test it out.

What will happen is that the textbox will shrink (and become "invisible") when the condition is not met.
Click to expand...

Hm, it's getting stuck on the Control Source code on the textbox.

I used this (as I presumed [FieldName] needed to be changed to the text that I needed to show):

Code: 
=IIF(TempVars!OrderType="Tuco", "DO NOT INVOICE", Null)

And I'm getting a MsgBox asking for the Parameter Value of "DO NOT INVOICE"...
 
That shouldn't happen. Upload a sample db let me see how you set it up.
 
Create a new db, create a form with one textbox, write the code and macro, zip and upload. I just need to the form, no data.
 
You must log in or register to reply here.

Similar threads

A
Filter Records
Replies
1
Views
783
June7
June7
HavingDatabaseRelations
Solved Combo Box will only work if Access is not minimized
Replies
10
Views
1,162
Mike Krailo
Mike Krailo
S
Solved Making POST and GET API calls to receive data
2
Replies
21
Views
15,490
sxschech
S
G
Need help coding a search box
Replies
4
Views
700
theDBguy
theDBguy
V
Trying to have a button on a form open a report with info from a list box
Replies
18
Views
2,204
moke123
moke123

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom