consolidating report input info (1 Viewer)

[amiscus]

I have a report I created to that gives lots of different information on sales. The only problem is that it is made up of a bunch of subreports derived from queries and each of these need its own input. However, it is all the same input (a string that gives the year and month of the records desired such as yyyy-mm). Is there any way that all the reports input can be consolidated so instead of having to enter this month string multiple times a single input will work for tall the queries or is there just a better way to build this report in general? Any help is greatly appreciated.
PS.- The reason its written as a bunch of different queries is because there are marginally complex calculations going on that access doesn't like having in the same query. There doesn't seem to be a way to get it all in one.

 

[Kryst51]

The way I have handled this sort of thing is to have a form with date fields on it. and a button that opens the report. In the query, in the date field have the criteria point to the loaded form where you are inputting the date or dates (if you have start and end dates) then the queries will use the dates you input on that form, so when you click the button on the "date" form, your query report will pop up. See example attached.

I hope this helps.

 

[amiscus]

So I tried to create a form with a command button that opens up a report and a text box. I want to have the report accept the text from the text as input 12 times over. What would I ahve to add to the VBA code?

Private Sub report_Click()
On Error GoTo Err_report_Click
Dim stDocName As String
stDocName = "VERT"
DoCmd.OpenReport stDocName, acPreview
Exit_report_Click:
Exit Sub
Err_report_Click:
MsgBox Err.Description
Resume Exit_report_Click

End Sub

I'm sorry if this is a really basic question but I have no experience in VBA.
Also other methods of accomplishing the same thing are welcomed as well.

 

[Kryst51]

In the underlying query for each subreport you would want the date field to point to the form. I'm sorry, I don't no much about vba either, so I can't answer that question.

The button should only open the report. The form the button is on should already have a text box on it that you would fill in before you click the button.

The subreports queries would point to that text box on that open form (That form must stay open for this to work.)

 

[amiscus]

Thanks a ton man. It took me a minute to figure out what you meant but I got my queries fully updated and a working form. Thanks again its a big help.

 

[Kryst51]

Your welcome! I am glad it works.