Hello,

I think I'm having a problem with a basic concept, and I'd appreciate some help as I feel a bit stuck in the development of my database.

Background: My users need to be able to choose which fields (columns) will be in the report (their output).

Translation into my "need to do" list for the development: I need to create a form that will take whatever fields the users choose and place them inside a select query, and build a report out of the query.

Problem: As I understand it, reports in MS ACCESS (as opposed to queries) are not flexible – In order to build a report, I have to specify the exact fields that will participate always in the report and can't put field1 only part of the time, and field2 only at other times.

Am I wrong? If so, can anyone explain to me how to build a "flexible" report or better yet, give me an example database?

Thanks.

"Easiest" way to do this is to have as many text boxes as you could ever need on your report. Set the visibility to false. On open, iterate the columns selected by the user, assign to the control source of each text box in turn and make them visible. For extra headaches you could size and position them too.

There isn't enough information to be sure this would be for you, but whenever I have provided users the option to effectively build their own queries I exported the data to Excel rather than used a report. Often users wanted to interrogate the data rather than have a read only report. If that sounds like an option then there are examples in the code bank.

I was asked specifically for a access report, so the export to excel part isn't relevent. Thanks.

Pardon me for jumping in ...

See if the attached example helps

Hitech,

It didn't help, as it didn't have a connection with what I asked (but I guess it's my fault for not explaining well, Thanks anyway for trying).

The basic question (rephrased) is:

Can a Access report hold variables or only permanent existing fields? As I understand, a report has to relay on exisiting fields from a table and cannot be flexible to have changing number of columns.

For example,
If I have a table with fields named: name, telphone, address, inventory1, inventory2,
A report has to hold fields like " name, telephone, address, inventory1" no matter what they hold, and cannot hold " " name, telephone, address, inventory" with the inventory being a variable consisting of iventory 1 or inventory2, submitted to the user's choice.

No - you understand wrong. You can do what you say. Go back to my first post - the Excel suggestion was the second suggestion. Note that I suggested the easiest method - you can also dynamically create textboxes but it is easier to have them created at design time and set their properties (control source, visibility etc) at run time.

As an aside, if that is a genuine set of table columns then it sounds like you have a design issue and have violated first normal form. Hacky stuff like this is one of the consequences of a non-normalised database.
Have a look here and particularly pay attention to repeating groups (ignore the qualifications about this - at the highest levels there is some controversy about repeating groups but unless you are a very experienced data modeller just assume they are bad): First normal form - Wikipedia, the free encyclopedia

[QUOTE=pootle flump;6461892]No - you understand wrong. You can do what you say.

Ok, thanks, pootle. I was about to go mad, as I knew it is possible but couldn't find examples...

Do you have an example of the use of text boxes in a report? It would help me a lot as the few trys I had with the entering of combo boxes and text boxes into reports were catastrophic, to say the least, and I have nobody else to consult.

As for the 1NF, the above was only an example as my database is a lot bigger. I read the description and it seems that my database fullfils 1NF (at least partly), but not 2NF. Anyways, I have a conceptual problem with it, as I understand it, but I don't understand how to translate 1NF/2NF databases into the simple form my users insist on:

-------------------------------------------------
| |
| lblInventory1 : txtInventory1 |
| |
| lblInventory2 : txtInventory2 |
|__________________________________________|

(and so on). The txt's are supposed to be textboxes with amounts.

Can you refer me to a example database that has a 1NF/2NF databases and does this simple thing? If not, do you know a site that has instructions how to do it? As my database is huge and was handed down to me by the previous employee (I didn't design it) I'm a bit nervous to do expirements on my own...

Ok - let's take this a step at a time.

Since a report is read only (it's kind of like a PDF - it is only meant for reading) a combo box is not something you would ever add. textboxes and labels are the two controls you will use the most.

Unless someone else can cut in then I'll have to leave this till lunch since it is likely to take a while. In the meantime, check out HTCs database. In particular look at the report in design view. You are especially interested in the Record Source property of the report and the Control Source properties of the text boxes. That is how to display data in a textbox. Note however we can change these properties in code which is how we change a report from static to dynamic.

OK, Cool.

I've read about the ControlSource Property and trialed with it a bit
(the enclosed mdb has 2 examples: the first report replaces one column with anothor's data, and the second one does it based on a combobox value.
the fisrt worked like a beuty but the second one didn't (and I can't figure out why, but I know that it's not the ControlSource's fault)).

Is there more to it than the simple controlsource?

Change the extension to mdb.

Play around - shout if you don't understand anything. I only changed code, nothing else.

I agree that it is possible.

I have one report that I use for printing many different crosstab queries. In A crosstab query, then columns and the count can be different every time the report is run.

I have this example that might help:

Dynamic Report based on a Crosstab query

Pootle,

Thanks a million, you solved a mystery I've been wondering about a lot. I have to give it a thought as to how to implement it into my database (and maybe check the control source behavior with multiple textboxes and labels), but I can already say it's a huge help(thanks for fixing the example code as well).

As for the 1NF/2NF, do you have a example that I can learn, as I didn't understand it fully

HighTech,

Thanks you as well - I only peeked at your database but it seems very promising and I hope I'll have the time soon to study it thoroughly...

This is an excellent introduction to relational database design:
The Relational Data Model, Normalisation and effective Database Design

Access tempts you to think of database design in terms of forms and reports. Understand that this is wrong. Databases should be designed according to the business entities they represent. Report and form formats are simply known as presentation issues - your presentation layer and data layer should be as decoupled as the technology allows. In other words - design your table correctly according to database design principles and then worry about how your reports look.

Anyway, in short I suggest you look at sub-reports.

You may take a look at the following link for an example of Cross-Tab Queries and Dynamic Report:

LEARN MS-ACCESS TIPS AND TRICKS Blog Archive Dynamic Report