Variable Report

[Kapellu2]

Hey everyone, I have yet another access question.

Right now i have a bunch of items entered into a table, each having about 50 columns of information describing them. What I would like to do is be able to print a report that will let me pick an item from a combo box (that works as a filter, such as all of the square items) and a second item from a second combo box such as color or density. I then need the report to print all of the square items, and their color or whatever else was selected in the second drop down box..

I figured out a way to have the first combo box filter them items but cannot figure out how to get the items from the second table to print in the report.

 

[pbaldy]

What method did you use for the first, so we can adapt it for the second?

 

[Kapellu2]

I actually wound up scrapping the first idea as it was too messy. Is there any way I could use a drop down box to select a table on the form, and then print the contents of that table on the report? What would the code look like?

 

[pbaldy]

One report that would change which table it was based on? That would imply a normalization problem, but in the open event of the report:

Me.RecordSource = Forms!FormName.ComboName

presuming the combo contains the name of a table.

 

[Kapellu2]

Awesome, that did the trick. Thanks Pbaldy, I appreciate it.

I have one last question. How would make that same combo box the row source in a search query? I want to make one column in a seperate combo box change depending on the table selected in the box.

 

[pbaldy]

If I'm understanding correctly, that would require building the SQL in code, as in this example (but you'd have to add your bit):

http://www.baldyweb.com/CascadingCombo.htm

 

[Kapellu2]

I was actually able to figure out the cascading combo box (thanks to these forums) but what I was trying to do was a little different.

Right now I have a combo box that has 9 columns with stuff like product type, engine type, ect. I would like to have a combo box that allows the user to pick a table, and have that be used as the row source in the tenth column. That way the user can compare a specific aspect of each cooler listed in the combo box.

The data in the combo box is based on a search query. Right now I have [Forms]![Customer Info Search].[Combo117] in the 'field' box of the tenth column in the search query. Instead of returning the value of each item in that selected table, it just returns the name of that table for all 10 items.

 

[pbaldy]

I guess I'm not clear on the goal. A combo box can only have one source. You can't select a row from the combo and then have further choices in the 10th column. You'd have to have another combo. Again, I'm probably not visualizing what you're trying to do.

 

[Kapellu2]

Sorry i'm doing a bad job of explaining. So I have a form with a list box in the center of it. The row source of this list box is a search query. There are about 50 items (50 rows) in the list box. Each item is described using 9 columns that display information such as product type, part number, engine type, ect

It looks kind of like this

______Part # Product Type Engine Type ect
Item 1
Item 2
Item 3

I would like to add a 10th column to the search query (and therefor to the list box). I would like the data in this column to be pulled from a table. I have a combo box on the form that lets the user select any of the 100 tables of information in the program. So basically, I want the 10th row in the list box to use the table selected in the combo box as a row source.

This way the user can compare say, the weight, of each of the items in the box by selecting the weight table from the combo box.

This should let users easily compare different aspects of the product

 

[pbaldy]

What do the 100 tables contain? It is almost certainly a mistake to have multiple tables holding similar data (a table for each product/month/customer/etc). To get unique info into the 10th column, you'd need to join your table into the row source of the listbox somehow. Hard to say exactly how without knowing the data.

 

[Kapellu2]

The other tables of information are just more descriptive information such as height, length, width. It would look messy to put 100 columns of information in one report, so instead we would like to always have the original 9 columns (the most important information) and then have one column that the user can change to single out the part of the coolers they would like to compare.

Was just curious as to how i could go about setting the chosen table as the row source for the main list box.