Query Help

[Qwert0000]

Hi,

I'm not sure if what I am tring to do is possible but I would like to create a single report running off a query where one of the fields in the query is changed by user input.

I have a Table called Student with all the student info as well as fields for required training classes.

The query [ReportGen] will call for LastName FirstName TrainingPhase and the field that is chosen by the user.

I have a form that uses a combobox that gets its info from a seperate table with all the required classes I need reports on. This combobox places the required field name in a textbox [ReportName] on the form. The form will remain open while generating this query and printing the report. I plan on using a command button to run this report if possible.

I know I can generate the 40 reports I need seperatly but this seems to be a cleaner way to accomplish what I want.

I am using some VB to accomplish some other simple tasks
and was hoping there is some code that could be run here.

I am a novice user so please be kind.

Thanks to all who respond to posts here. Lurking has helped me tremendously.

 

[RickSpr]

First a way using just the query:

Set up the combo box with 2 columns, the first being just a number from 1 to 40 and the second being the field name (or any other text to be displayed). To keep the number from being shown, set the Column Widths property to 0. The Bound Column should be the one that contains the number.

In the query grid, set its 4th column Field to:
SelField: Choose(Forms![your form name]![combo box name], field1, field2, etc.)
This will select one of the 40 fields based on the combo box value, and output it with the name SelField. You should then include a SelField text box in the report.

Now a method using VBA:

Start by defining a variable SelFieldName As String in a standard module. In the report button's Click event, copy the ReportName field to the variable.

In the report's Open event, create a string that contains a SQL SELECT statement retrieving the three predetermined fields plus the field named in SelFieldName. Assign this string to the report's RecordSource property. For the report control that displays the variable field, you also need to set its Control Source property to the name in SelFieldName. (The query will not be needed for this solution.)

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Qwert0000]

Rick,

The Query method did exactly what I wanted. Thank you for your prompt response.

Eric.

 

[Qwert0000]

Hi Again,

Ran in to a little trouble when I got all the field names in to the query. It worked great when I tested it with 4 fields, but I get a "The expression you entered is to complex" error when I entered the 34 I wanted. After some trial and error I was able to get it to take 28. The way we classify our training makes it pretty easy to seperate this to 2 different comboboxs to achieve the results I wanted and this is still much cleaner then 34 seperate reports. Thanks again for your help.

Eric.