Generate report by passing citeria to query from subform

[maxmaggot]

Hi all,


This question is based on the work that June7 helped me solve in my previous post on date criteria.

I now need to to generate a report with the information that is displayed for when the user specifies a specific date criteria. These dates are stored in hidden text boxes on the same subform as the results from running the date criteria.

I've created a query with the following criteria in the DateReceived field

Between [Forms]![frmIncome]![sfrmIncome].[txtFundsStartDate] And [Forms]![frmIncome]![sfrmIncome].[txtFundsEndDate]

This query also displays the grouped amount for the date criteria specified in the textbox and the Event for that amount

It pops up with a dialog box which indicates its not finding the text fields. so if anyone can see where I'm wrong with this, I'd be very appreciated.


I hope this makes sense.

Maybe I can skip the query and just generate a report that can pull the date criteria directly into the report and display the totals from he subform.

I can pass the db onto someone to have a look at if that is required.

Thanks for your help in advance.

[June7]

Try:

Between [Forms]![frmIncome]![sfrmIncome].Form.[txtFundsStartDate] And [Forms]![frmIncome]![sfrmIncome].Form.[txtFundsEndDate]

I don't use parameterized queries. I set WHERE CONDITION of OpenReport

DoCmd.OpenReport "reportname", acViewPreview, , "DateReceived Between #" & Me.sfrmIncome.Form.txtFundsStartDate & "# And #" & Me.sfrmIncome.Form.txtFundsEndDate & "#"

This would be code behind a button on main form.

[maxmaggot]

Thanks June.
Just one more thing. Seeing as the report will display the same data as the subform based on the date criteria for roughly 10 different summarised values would I be better passing in the txtFundsstartDate and txtFundsEndDate as paramaters and then using textboxes with the same control source as the subform to generate the totals such as

=Nz(DSum("AmountPaid","PaymentsReceived","[EventType]=6 AND [DateReceived] BETWEEN #" & Nz([txtFundsStartDate],1/1/1900) & "# AND #" & Nz([txtFundsEndDate],12/31/2900) & "#"))

in the report.

I'm unsure of how to pass textbox data from a subform to a report or if this is possible. Each event would need to draw in its own eventtype data in the report. All I'd need is to know what date criteria the user had specified in the subform.

I've also found this

DoCmd.OpenReport "MyReport", View:=acViewPreview, OpenArgs:=(Me.MyTextBox )

Should I follow this method and is there anything I'm missing.

Thanks again for any help.

Thanks again for any help on this.

[June7]

My code is a suggestion that references subform controls to filter report.

I don't really understand the issue as described.

[maxmaggot]

Thanks June,
Sorry, went off on the wrong tangent there. I didn't fully understand what I was trying to do.

I put your code in and had to tweak it ever so slightly to

DoCmd.OpenReport "rptDonations - Total Donations For Each Category", , acViewPreview, "DateReceived Between #" & _
            Me.Form.txtFundsStartDate & "# And #" & Me.Form.txtFundsEndDate & "#"

The issue I'm getting now which is weird is that the WHERE clause causes OneNote to open the report but doesn't run the WHERE clause whereas if i remove the WHERE clause, I don't get the answers, but the report opens in Access.

Really ununusual.

[June7]

How is OneNote involved?

Your code is omitting the subform container reference.

[maxmaggot]

I have no idea why OneNote would open. I solved it by compacting and repairing the db. Really strange. When I try to run the openreport with your code it is giving me an error at this point Me.sfrmIncome in the WHERE clause. I'm going to keep working on this as it is totally new to me to do this and I'm making silly mistakes. I've the whole infrastructure set up at this point thanks to your advice and guidance so I'm going to keep trying to get this going.

Thanks June.

[maxmaggot]

Would the error at the Me.sfrmIncome point be popping up because the button to open the report is on the subform?

[June7]

Yes, I assumed the button was on the main form.

If the textboxes are also on subform, try just:

Me.txtFundsStartDate

[maxmaggot]

Yes that works for the WHERE clause now. Thank you. Still getting errors in the report generated e.g. #Name and #Error. Thanks again for your time. I'm going to keep tinkering

I don't now if its in your remit June but I've uploaded the db to a site. If it's not a problem for you could you throw your eye over it. I understand if you can't. I can send you the link.

Kind regards
Karl

[June7]

I had misplaced comma for the OpenReport arguments which you have replicated in your code. I have corrected the earlier post. Double check your code.

The expressions in report show #NAME? error because they reference textbox names not on the report. #ERROR results because missing quote marks around the alternate date in the Nz.

So how do you plan to get the date range ends into the textboxes on the report? By referencing textboxes on form?

Domain aggregates can be slow in queries and in textbox expressions. I am guessing you want to have every event type listed even if there is no data for the specified period.

Instead of DSum and ECount, why don't you bind report to PaymentsReceived table or a query and use report grouping and sorting features and aggregate calcs in footer sections? Expression like:

=Sum(IIf([EventType]=5,[AmountPaid],0))

The two-column design can even be achieved without creating 20 textboxes with expressions, although ensuring that every EventType is listed would be a little complicated.

[June7]

I should have mentioned this before, if not already in one of your threads. You are using navigation form. I find navigation forms frustrating, especially when trying to reference subforms. But since this is a web database and presumably you plan to publish to web, stuck with navigation form. I've never built a web db and therefore never used navigation form. Review https://www.accessforums.net/forms/a...tab-41032.html

The form you need to reference is third level (subsubform). This works:

=[Forms]![frmMainForm].[NavigationSubform]![sfrmIncome].[Form].[txtTotalFundRaisingEvent]

Why do Mini Marathon and Fund Raising Event reference same textbox?

Instead of referencing the subform, could do the same DSum expression on the report.

BTW, I always give subform container control a name different from the SourceObject, which you have done with two subforms, not sfrmIncome.

[maxmaggot]

Thanks June for the info. It was a web db but MS kept screwing us around. They would take a look at our SharePoint data periodically and mess the whole system up. So In January I removed SharePoint and made the database standalone. The only problem was that we had come so far down the road of designing it for web that it would take a enormous effort resolve. PS. This was the client changing her mind after 6 months of development and constant requirements changes to the initial spec.

Thanks again for looking into this

[June7]

I was testing and editing my previous post. Review it again.

[maxmaggot]

Thanks June for devoting so much time to this. Going to fix this up now.