Need Input on Form Design - Dynamic Field Names?

[Madmartigan]

For simplification, I'll use example data. Let's say I have a table that has, Year, Article, and Sales as columns. Articles could include any number of items (Shoes, Shirts, Coats, Hats, etc).

I'm trying to make a simple form that will allow the user to select Year in a combo box and it will show Sales ($) for that year by article. The kicker is that certain articles may or may not have been sold in any year.
When a year is selected in the combo box I want only those articles which had sales to display along with the sum of sales by article (for that year). How that is displayed doesn't matter terribly.

With my first attempt, everything worked as I wanted but only the first article would display in the field along with its sales. So then I was thinking of maybe doing a datasheet sub-form with Article and Sales as columns and somehow use the Year Combobox as a control, but I couldn't figure out how to get a combo box as a separate item when the subform is datasheet.



It'd be nice if I could have a multivalue field for the Articles, but I don't know how to have another multivalue field for Sales that corresponds with each Article item.

Any help/suggestion is greatly appreciated.

[June7]

Sounds like dependent (cascading) comboboxes/listboxes http://www.datapigtechnologies.com/f...combobox2.html

Or set form Filter and FilterOn properties.

Or open a report filtered to the selected year.

What was your first attempt? What did you try?

[Madmartigan]

On my first attempt I just used the Form Wizard (noob) and selected Justified. Then I turned the Year field to a combo box.

[Madmartigan]

Sounds like dependent (cascading) comboboxes/listboxes http://www.datapigtechnologies.com/f...combobox2.html

The Year is the only thing that would be selected by the user. I always want all articles displayed for a given year.


Your suggestion to use reports isn't bad. I've never used reports before so I might go that route.

Is it possible to nest a report in a form (instead of having it open in a new window)?

[June7]

Yes. The report nested on form will appear as report opened in ReportView, not PrintPreview.

[Madmartigan]

Hmm. When I try to add the report to the form, it says it's not allowed. I tried searching and many say that functionality is only available with Access 2010?

[June7]

Could be. I never even tried with 2007.

[Madmartigan]

So I think I have a solution that is as close as I think I can be.

I created a datasheet subform based on my query. I also created a combobox for year and put [Forms]![fMain Menu]![Year] as criteria for the Year within the query itself.

It works great but the problem is that I've only been able to get the subform to refresh when I press F5 after making a combobox selection. I've tried quite a few variations on the AfterUpdate event on the combobox but can't get anything to work.

So how do I get my subform to update after a selection is made in the combobox? If I can get that to work I think I'll have exactly what I was looking for.

My subform and combo box is on a tab within a main form BTW. I tried putting this in the combobox after update event:

[Forms]![fMain Menu].[Form]![Customer Balances by Reason/Year subform].Requery

I get the Access can't find the object error.

[June7]

I always give subform container control a name different from the object it holds, like ctrDetails.

Code in combobox AfterUpdate event:

Me.ctrDetails.Requery