programming control source help..

[annd1968]

Hi!
Can a control source for a report be dynamic and programmed based on a form selection?

I have a form where a user selects one of 5 cities in a combo box. This should then display a report displaying the records (3 fields) based on the city chosen.

The 3 fields are currently in a query which has 15 fields again based on the city.

As an example, the query has fields: Toronto, Toronto1, Toronto2, Montreal, Montreal1, Montreal2, Vancouver, Vancouver1, Vancouver2, etc...

If the user selects Montreal on the combo box, I want the report to display the fields Montreal, Montreal1 and Montreal2 in the detail.

Is this possible?
Thx.

[beacon]

Hi ann,

Just to make sure I'm understanding you correctly...you want the user to be able to select a value from a combo box and then have the report generated based on the value selected, right?

I don't think you actually need to modify the control source of any control. You just need to setup your parameters for your query based on the combo box on your form and then have your form call the report. When you do this, your report will use the query you assign as the record source, which will then use the parameter on your form as the selection criteria.

Check out this link for details (with pictures!): http://www.fontstuff.com/access/acctut08.htm

The only thing to keep in mind that I would do differently than the web site is to replace

DoCmd.OpenQuery

with

DoCmd.OpenReport

and make sure that the underlying query is the record source for the report.

If this doesn't make sense, let me know and I can go into further detail.

Ciao

[OldBirdman]

http://bytes.com/topic/access/insigh...ble-structures might also be a good place to start.
I looks like there is a table (or query) with the following fields:

Toronto
Toronto1
Toronto2
Montreal
Montreal1
Montreal2
Vancouver
Vancouver1
Vancouver2
....
 

If nothing else, this design lacks flexibility, and may cause future problems. The query being requested will have to be built dynamically using VBA coding to change the fields being selected. Not the same as selecting records for a city.

[NeoPa]

Hi. Welcome to Bytes!

I'm a little unsure what you actually mean in your question (As OldBirdman says the record structure appears bizarre, if that's what you really mean).

Check out Example Filtering on a Form and let us know if you still feel you need more help.