Is it possible to take input data from criteria and use it to replace name of a pdf?

[Jeffrey Tan]

So i have a parameter query that accepts the value of a date. when i run the query, the input box or criteria pop up appears, asks me for a date, and then the results appear..

is it possible to take whatever i typed in the criteria and use it later when i want to send an email and take that date and use that as my pdf name?

1. i open the parameter query report
2. a popup box appears and asks to me put in a date
3. i put in a formatted date say 8/2011
4. the report opens
5. it takes the 8/2011 i typed and outputs the file using the following command, appends the strReportName and adds the 8/2011 i typed somewhere in the string
strReportName is "Tickets Closed for "

DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, strDestination & strReportName & ".pdf", False

6. when file has been outputted, the name should be the following: Tickets Closed for 8/2011.pdf

is that possible?

thanks!

[NeoPa]

No.

Parameter queries are for either :

1. The noddiest of work, that will never require any further development.
2. Being controlled by code so the parameters are set by the code rather than by the operator.

If you're using it for a report then you should reconsider your approach.

Typically, such filtering is done by getting the response from a control on your form, then using that to create a filter string and passing that in the OpenReport call. That way the code already has access to the value and further processing using that value is straightforward.

[Jeffrey Tan]

@NeoPa:

I'm a bit confused. Can you show me an example or some code that'll help me understand this problem?

You're saying that I need to include a control in a form and the value I type into the control will be used in the report.

So it's going to be a two step process?

Create a form with one text field and a button. when i enter the formatted date (mm/yyyy) and i click the button, the value from the textfield will be used as a filter sring for the report and then i can use that in the report name when using the output call for email.

[NeoPa]

@Jeffrey
A two step process is still simpler than what you're working with now. Your confusion is merely related to what you're familiar with. The suggested alternative is far simpler overall (not least because it can be made to work whereas the alternative cannot).

Let me see if I can paint a picture that's easier to follow :

Start with a Form frmA, a query qryB and a report rptC.
rptC is bound to qryB. frmA has a TextBox txtA1 which will be used for filtering qryB, and by association, rptC. frmA also has a CommandButton cmdA2 which triggers running rptC. tblD contains three fields D1, D2 and D3. qryB selects its data from tblD.

rptC has three TextBox controls (txtC1, txtC2 and txtC3) bound to the qryB fields (B1, B2 and B3). txtA1 is a filter for txtC1. It can work for different types of field but for simplicity I'm going with a numeric, ID type field. All Clear so far I hope. For now I will ignore error handling, to allow the basic logic to be more clearly shown.

There are two basic methods for handling this and I will illustrate them here :

1. My preferred method is to grab the data from txtA1 into the code and build a filter string. qryB, in this scenario, would be :
   

   SELECT [D1] AS [B1]
        , [D2] AS [B2]
        , [D3] AS [B3]
   FROM   [tblD]

   The code behind cmdA2, for building the filter string and opening the report, would be like :
   

   Private Sub cmdA2_Click()
       Dim strWhere As String
    
       strWhere = "[B1] = " & Me.txtA1
       Call DoCmd.OpenReport(ReportName:="rptC", WhereCondition:=strWhere)
   End Sub

2. The alternative puts the filter into the query itself. I don't like this approach as it relies on the form being open (which it's designed to be, but when it isn't it's a nuisance). The SQL for qryB in this case would be :
   

   SELECT [D1] AS [B1]
        , [D2] AS [B2]
        , [D3] AS [B3]
   FROM   [tblD]
   WHERE  [D1] = Forms!frmA.txtA1

   The code for cmdA2 in this scenario would be commensurately simpler of course :
   

   Private Sub cmdA2_Click()
       Call DoCmd.OpenReport(ReportName:="rptC")
   End Sub

[Jeffrey Tan]

can you briefly explain to me the filter on the report

the filter is the "WHERE" clause in the query?

I added this in the report form open

Dim result As String
 
result = InputBox("Enter Month and Year", "Tickets Opened", "m/yyyy")
 
Me.Filter = "Format([user_problem_log.date_closed],'m/yyyy')='" & result & "'"
 

[NeoPa]

This post doesn't seem to engage very much with the topic so far. It's as if you've jumped off the rails and rejoined the track somewhere up ahead (with little clue as to where you are now).

Let's assume you are going with the filter approach outlined in #1 from post #4. Let's also assume (because that's the closest idea I can come to of what you're talking about) that you want the code in the report to be aware of the value (txtA1) used to filter the report.

In that case (and let me know if I'm off-track myself here) then you should be aware that there are two approaches to this availabe :

1. The simplest would be to pass an OpenArgs parameter from the calling code, as well as the WhereCondition parameter. This needs no further explanation.
2. The alternative would be to get this information out of the Me.Filter string in you Form_Open() procedure :
   

   Private strDate As String
    
   Private Sub Form_Open()
       strDate = Split(Me.Filter, "'")(1)
   End Sub

[Jeffrey Tan]

@NeoPa:

I think I may have completely confused you with my goal to this problem.

Let me go back to square one.

I have a form "List of Reports" that contain the following fields
1. combobox - includes all my reports
2. command button open - opens the report
3. email button - emails the report, however, some have parameters.

There are two reports specifically that require the user to enter a parameter value and that is a date field that I've formatted to accept the month and year (m/yyyy).
Then the report opens and filters the records based on the month and year I entered.

Now when I want to email this, it works no problem. It's just that the "Subject" and the "Report Name" for the docmd output that I'm having a problem with.

I would like to set it so that somewhere in the Subject and Report Name is the m/yyyy I entered so that when the recipient opens up the email, he/she can easily see what month and year the report is.

[NeoPa]

Right. That explains where you're coming from, but not why you you still seem to have code using the InputBox() function and what it is you still don't understand from my answers so far (It seems to me they answer all of your questions).

I'm sure I can help further, but I need to know where you're confused. What don't you understand from the answers already given?