How to Create Dynamic Report with Changing View?

[kestefon]

Hi,



I have a master recordset containing sales data for numerous products. I'd like to create a dynamic "dashboard" (i.e. a single report or form that the user can interact with dynamically) where the user can click a product name from a ListBox and see a summary report generate in the space below. If they click a different product name from the ListBox, I'd like the report space to automatically refresh and show the next summary report.

Could someone outline the steps involved with this? I've created my desired report and dragged it into the form but I'm having trouble embedding it dynamically into the form: I tried using the "Link Child Fields"/"Link Master Fields" properties, but get an error message saying I can't build a link between unbound forms. Here's a screenshot to give you an idea of what I'm trying to accomplish. Thanks!

[ranman256]

In the list box AFTER UPDATE event, requery the subform.
me.child1.requery
(if you have no links, dont embedd the report, just open it)

[kestefon]

Thanks. What do you mean when you say "don't embed the report, just open it"? I'd like the report to be embedded within the overall form, and then requeried whenever a new ListBox item is selected...

[ranman256]

It would not be a box on the form,
it would be a button to run the command : docmd.openreport "rMyReport",acViewPreview
(the query in the report is looking at the list box on the form)

[kestefon]

Cool. I was able to add a button and generate a report preview, but I seem to have the requery syntax wrong. I'm supposed to add VBA code to the ListBox and use the requery method? Should the requery method refer to the name of the button?

[ranman256]

No, the requery was for the sub form. dont need it now.

[kestefon]

Gotcha. I added a DoCmd.Close line to the query on the button so that it would close and re-open the preview if someone selects a new product.

One more question: While this more or less gets me where I want to go, is there anyway to have the preview integrated within the actual form, instead of as a pop-up?

[ranman256]

The subform/report as you originally had. (no child / no master links)
The rpt query is linked to the form list box.
The child.requery will be needed when they select a diffenent item in the list.

But they are stuck in your little window provided.