selecting Fields for report

[murlen]

I have a query with the main fields being Region, System1, System1Comments, System2, System2Comments, System3, System3Comments, …System6, System6Comments

The user previews a report based on two criteria on a form, the Region and the System
To select the region I’m using;
DoCmd.OpenReport stDocName, acPreview,, “[RegionID]= “ & Me.RegionID & “”
And that works ok, my question is if the user selects (for example) system2 from the form, how do I get just Fields for System2 and System2Comments to show up on the report?

 

[ColinEssex]

I suspect this is where your database structure needs to be reviewed.

Have you got a field for each "system" in your table? (System1, System2 etc etc)

If so, you should have one field for systemID and a comment field plus other related fields to that record like CommentDate or whatever.

That way your query will run as you wish (with the use of parameters) and therefore your report will too.

Col

 

[murlen]

Restructuring is not an option.
I have found that in my second combo box, if I set the Row Source Type to Field List and the Row Source to SELECT Maintest.System1,Maintest.System2,…. From Maintest;
I can select the Field for the desired System.
My biggest problem now is code, so that when the user selects System1, the Field for System1Comments is also selected.
Any ideas?