Field selection on a form for a report

[NC1977]

Hi!

I've got a form, where the user can select a division or Group or region and it runs my query "Pivot Table by Division" with these parameters.

Now I have 30 columns in my QRY and I want the user to be able to select up to 5 of these colums (the capital ones, I only put 3 as an example):

Division Group Region FINANCIALSIZE EMPLOYEESIZE EBIT
Dubuque Book USA 1 2 3
Corinth Retail USA 3 4 1

I have no idea how to do this, I've tried everything! Please help ;-)

Thank you very much

 

[BSman]

One way would be to place 5 combo boxes on your form. Each combo box would have a drop down list of the 30 columns that can be selected by the user. You might have the actual source to have two columns, one the actual name of the column and the second column (the one that you display to the user) the description of the column.

The user would select values for up to five columns using these combo boxes. When the user clicks your button to display the results, you can construct your SQL statement by concatenating it from the combo boxes that are not null values. By following the boxes in the order they are displayed on the form you can also let the user control which column will be displayed first, etc., and you can use them as sort parameters also.

Bob

 

[NC1977]

Hi Bob,

I've created a table with the list of all 30 columns. Then added 5 combo boxes to my form. I really don't know however how to formulate a sql statement? I know how to select fields etc, but only if I name them...how do I pass the argument of " take the field listed in each combo box"???

Geez am really sucky at this! lol

 

[BSman]

Since you're going to run a report, you need to construct the criteria for the report. Let's call the combo boxes cbo1, cbo2, cbo3, cbo4, and cbo5, and let's say the columns are col1, col2, col3, col4, and col5. You would do something like this:

dim strWhere as string
strWhere = "[col1] = " & me.cbo1 & " and [col2] = " & me.cbo2 & " and [col3] = " & me.cbo3 & " and [col4 = " &
me.cbo4 & " and [col5] = " & me.cbo5

docmd.OpenReport ("MyReport",acViewNormal,,strwhere)


If the user doesn't have to pick all 5 criteria, then you would set up a series of statements testing if each me.cbox is not null, and if it contains data then you would add that criteria to strWhere.

Bob

 

[NC1977]

Ok, this works except for one thing, I believe I need to create some kind of relationship between my field lists in my combo box and my table..when I run the report I get the columns I have selected but like this:

LEGAL ISSUES TREND SAFETY TEST UNION
LEGAL ISSUES TREND SAFETY TEST UNION
LEGAL ISSUES TREND SAFETY TEST UNION
LEGAL ISSUES TREND SAFETY TEST UNION

When the title should be those, and the records should be numbers....

But since I have 30 columns, I can't create 30 relationships to one table and one column???

 

[BSman]

I'm not sure what you are trying to get as your output. How about some sample rows of your data. Are the columns being selected by the user intended to be the only data output? And what kind of data output.

Bob

 

[NC1977]

I'm sorry, I'm not being very clear am i? ok here it is:

I have a table with about 45 columns. Not sure how to attach a spreadsheet to my email...I'll try to explain:

Col: Division - Group - Region - Trend - EBIT - Safety Trend - etc..(45 columns of this)
Rows: Fairlfield - Book - USA - 1 - 2 -3 - 4 - 5 etc...
Dubuque - book - USA - 1 - 1- 4 - 3 etc..


Now I have a form and I want the user to be able to pick which column they want (up to 5)in their report. I have created an additional table with the name of all these columns to choose from and put 5 combo boxes on my form.

I've come up with a little code for my "onclick" event but it doesn't work:

Dim SQL As String

SQL = "Select " & Forms("Pivot Table by Division").Controls("Combo359").Value & " From" "Matrix" & "Where" & Forms("Pivot Table by Division").Controls("Combo359").Value & " " & "Is not Null"

Thanks so much for your time Bob, I appreciate it!

 

[BSman]

Not sure that you need a pivot table. What about a SQL statement that would select the columns identified by the user and display in a datasheet (spreadsheet like) format?

For example, in your data source for your combo boxes you would have a column with the actual name of the column in the data table and another column with the descriptive name of the column. Each combo box would display the descriptive name, but the actual value of the combo box would be the actual name of the column in the data table.

You would then construct a select statement something like this:

strSQL = "SELECT " & me.cbo1 & ", " & me.cbo2 & ", " & me.cbo3 & ", " & me.cbo4 & ", " & me.cbo4 & " FROM MyTable ORDER BY " & me.cbo1 & ", " & me.cbo2

If you then open this SQL statement you should see the data in a spreadsheet format, or you could set this up as the data source for a form.

Bob

 

[NC1977]

Hello Again!

Ok, this worked...except for one thing, I've got my statement running and opening in a report. The field list for the report doesn't contain any of the fields requested??? The field list box's title is my sql statement???

I'm soooo close I can almost taste this! I'm very excited

 

[BSman]

In the On Load property of your report, place the following statement:

(after you have built the SQL statement in the string variable strSQL)

me.RecordSource=strSQL

You will probably have to place square brackets around each column name in your SQL statement when you build it.

Bob

 

[NC1977]

Ok, I did what you said except for the onload event as their isnt' one for reports so I put it in the onactive property.. I'm still getting the same error..I tried putting the results in a form so that I could try the onload but it doesn't work. Could it be the Me part of me.RecordSource=strSQL???

 

[BSman]

Reports absolutely have an "on load" property. Take another look at the properties list for your report.

Bob

 

[NC1977]

I've got OnOpen OnClose OnActivate OnDeactivate On No data OnPage and OnError...That's it. I do have the OnLoad for the form and I did put the statement in a form to test..

This is my code for the onclick event:

Dim strSQL As String

strSQL = "SELECT " & Me.Combo359 & ", " & Me.Combo361 & ", " & Me.Combo363 & ", " & Me.Combo365 & ", " & Me.Combo367 & " FROM MyTable "

DoCmd.OpenReport "Testing", acViewDesign
Reports("Testing").RecordSource = strSQL
DoCmd.SetWarnings Warningson

 

[BSman]

Are you trying to actually create a report with the selected data, or will a spreadsheet format work? I had assumed that a spreadsheet format will work. If you absolutely need it in a report format, then you have a problem because the different items of data might be different widths. If the same width will work for the different data items, you could design a report something like this:

Use txtData1, txtData2, txtData3,...(etc.) for the controls to contain the data and txtHeading1, txtHeading2, txtHeading3....(etc.) for the column headings for each data item.

Change your SQL statement so that it returns each column as "Data1", "Data2", "Data3", and so on, as well as an additional set of data for the labels as "Heading1", "Heading2", etc. Then your report will see a data source that will return the columns titled Data1, Data2, Date3, etc., so it will know what to put in each text box (and in the heading text boxes).

To get the correct headings you could add another column to the table with your list of columns. The new column would be the heading. The data source for each combo box would include the heading as one of the columns returned (but not displayed). To reference the value of the Heading (assuming the data source is "Select ColumnName, DisplayName, Heading from ColumnList") you would reference it as me.cboCombo1.Column(2). (Use 0 based array column numbering when referring to the combo box in the VBA code, even though you set the number of column which is the value of the combo box to 1. If you wanted to refer to that value in code it would be .Column(0).)

When I check for the properties I looked at a form by mistake. Use the report's on load property.

Bob