By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,658 Members | 1,612 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,658 IT Pros & Developers. It's quick & easy.

programming control source help..

P: 1
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.
May 7 '09 #1
Share this Question
Share on Google+
3 Replies


beacon
100+
P: 579
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
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenQuery
with
Expand|Select|Wrap|Line Numbers
  1. 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
May 7 '09 #2

100+
P: 675
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:
Expand|Select|Wrap|Line Numbers
  1. Toronto
  2. Toronto1
  3. Toronto2
  4. Montreal
  5. Montreal1
  6. Montreal2
  7. Vancouver
  8. Vancouver1
  9. Vancouver2
  10. ....
  11.  
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.
May 7 '09 #3

NeoPa
Expert Mod 15k+
P: 31,712
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.
May 8 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.