Help Passing Parameter From Form to Report

[lmoseley7]

I am quite a novice at Access, although I'm fairly comfortable in Excel so I'm not unfamiliar with VBA and Office Products. We have a report that we run in Access that pulls data based upon a maturity date. One issue we always had was if we ran the report for a date where there was no activity, the date would not appear in the report. I decided to pass the date in from a Form that I built so that the date would appear even if there was no activity for that day. The problem I have now is that when I enter the date in the form the parameter from the report pops up as well. I realize this is because the query under the report uses a parameter on the maturity date field, but if I remove that parameter from the query, the report will display the date in the report, but the results are not filtered by the date.

I'm using the input value in the where clause of the DoCmd statement behind the OK button:

Private Sub Command2_Click()
    Dim strWhereCondition As String
    strWhereCondition = "[Form_Date_Input] ='" & Me.Form_Date_Input & "'"
    DoCmd.OpenReport "CP MATURING ON THIS DATE", acViewPreview, , strWhereCondition
    DoCmd.Close acForm, "Enter CP Maturity Date"
End Sub

The input box on the form is a text box named Form_Date_Input. In the query I have the following as criteria on the Maturity Date field:

[[Forms]![Enter CP Maturity Date]![Form_Date_Input]/CODE]

The name of the form is Enter CP Maturity Date.

One other issue, although the date passes to the print preview of the report, when I print the report that field prints a #Name? error.

So I have a few issues.  Hopefully someone can help me with some answers.

 

[xenou]

The problem I have now is that when I enter the date in the form the parameter from the report pops up as well.

Are saying as soon as you enter a value in the form, the report runs? That isn't clear at all. Is there code in the textbox?

One other issue, although the date passes to the print preview of the report, when I print the report that field prints a #Name? error.

Do you print directly from the print preview screen, or do you bypass the print preview screen when you go to print.

 

[lmoseley7]

Thanks for your questions and help.

Are saying as soon as you enter a value in the form, the report runs? That isn't clear at all. Is there code in the textbox?


Do you print directly from the print preview screen, or do you bypass the print preview screen when you go to print.

I have the text box that we enter the date into on the form and then I click a button that runs the Sub Command2_Click macro that I included in the OP. Originally the text box was not set to a date format, but I changed that since my first post because I was thinking that comparing a text entry to a field in a table that is in a date format might be my issue. I also changed the variable reference to be formatted like a date in the DoCommand code. Unfortunately, those changes had no effect on the way the report runs.

I was trying to print from the print preview screen. In print preview I see the date in the report header, but if you go to Report View or print from print preview the #Name? error appears.

Thanks for your help.

 

[lmoseley7]

One update, in looking at my Command2_Click code I noticed that when I was declaring my where condition variable in VBA, I was making [Form_Data_Input], the field on the form, equal to the Me.[Form_Date_Input]. It occurred to me that the first field needed to be the field in the query, not the form field in both parts of the variable declaration. I also changed the formatting of the variable in the DoCmd code to reflect that it is a date. This, along with removing the parameter from the query, has fixed the issue with having to enter the parameter twice (or at least it seems on my first few tests).

I'm still having an issue with the date printing as an error in the header. I can see the date in print preview, put not in report view or when it actually prints. The formula in the Text Box where I want the date to display is:

=[Forms]![Enter CP Maturity Date]![Form_Data_Input]

Again, I can see the date in print preview, but in report view or the printed version, there is an error.

Any help with that?

 

[xenou]

Again, I can see the date in print preview, but in report view or the printed version, there is an error.

Do you print the report from print preview? How do you print the report?

 

[xenou]

Private Sub Command2_Click()
    Dim strWhereCondition As String
    strWhereCondition = "[Form_Date_Input] ='" & Me.Form_Date_Input & "'"
    DoCmd.OpenReport "CP MATURING ON THIS DATE", acViewPreview, , strWhereCondition
    DoCmd.Close acForm, "Enter CP Maturity Date"
End Sub

your code close is closing the form?

Once you close the form you no longer have access to that date in the form (for printing or anything else). Still not sure how you are printing the form.

 

[Micron]

Either don't close the form until the report is closed (hide it if you need to) or pass the date as a parameter. The former would be easier.
Currently, when the report is printed, the value is missing because the reference source has been closed.

Private Sub Command2_Click()
    Dim strWhereCondition As String
    strWhereCondition = "[Form_Date_Input] ='" & Me.Form_Date_Input & "'"
    DoCmd.OpenReport "CP MATURING ON THIS DATE", acViewPreview, , strWhereCondition,, Me.Form_Date_Input 
    DoCmd.Close acForm, "Enter CP Maturity Date"
End Sub

In the report Load event, get the OpenArgs (the form date) that was passed to the report using the OpenArgs property:
expression .OpenReport(ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs)

 

[lmoseley7]

Thanks so much for all your help. Yes, I realized that when I closed the form that's what was causing the date to no longer be present. Xenou, I was still printing the report after a manual inspection of the results. I guess I should've checked back one more time on Friday because you came to the correct conclusion before I did.

Micron, I had seen reference to the OpenArgs property through my Googling of this issue, thanks for adding that option. Does the OpenArgs control the data returned by the report, or only allow what is entered to be referenced after the form is closed? I guess I can just research that on my own. Thanks for the direction.

 

[xenou]

Open args is just a way to provide some variables to the report. They would work like local variables. That's why you could use them after the first form is closed.

 

[Micron]

xenou is correct. As to your question
Does the OpenArgs control the data returned by the report, or only allow what is entered to be referenced after the form is closed?
one could also say possibly (for the first part) and yes to the second. In referencing the argument in the report open or load event, you could filter a report or alter its recordsource property. For example, you could filter a report based on the value of a form control containing a Dept value. However, if the user might want to run multiple reports from the form, you wouldn't use this method. You'd hide the form if you don't want it to be seen when the report opens, then unhide it on the report close. This would ensure that a user could not change the Dept value and click to run a new report. If the 1st report is still open, I'm pretty sure it will only display the original data, and not display new data because it was left open. I think the OpenArgs is more useful for controlling subsequent forms though.