Making selections to populate a report (1 Viewer)

[tmyers]

This is more of a general question then a report specific one. I would almost call this a brainstorm rather than a question. Almost.
First to those who have been helping, as always thanks for all your help. I am taking a break from my crosstab report mess.

I am now thinking about the quote unquote final stage of my applications use, which is to cobble together all info previously entered into a nicely structured report (the quote our business would put out to our contractors) based on the users selection.

All reports/quotes would have their data populated via my table [tblProduct]. I have queries for each specific [VendorID] within the table to show my users each vendors quote that was imported broken down per vendor.

In a perfect world, my users would be able to pick and choose each individual item to put onto the report. I am thinking a check box would be the simplest method. So each product would have a checkbox to be able to pick it. I would probably also use a "master" checkbox to select everything within that vendors quote to choose all items.

So what would be the best method/approach to this? Use checkboxes then a query to copy the items to a temporary table specifically structured for the quote then base the report off that? That would keep the report consistent as far as structure, but would allow its contents to change.

If that would be the best method, how would I structure the query to manage that? The query would be run by a button click then open the report ideally. I dont think an append query is right, because I want to leave the data in the products table for later reporting purposes.

Thoughts? I am mostly just thinking out loud.

 

[theDBguy]

Hi. When posting an abstract question, you would get an abstract answer. So, saying that something is the "best" approach would probably be impossible to do. The only thing I can say is you could give all the options a try; and once you have evaluated them, you should be able to decide which is the best.

As for the possible options, you mentioned one of them, using checkboxes. You also mentioned using a temporary table, which is another option. You probably don't need it, but it is an option. Another option is to use a multi-select Listbox and then use a query to pull the selected data.

Just a thought...

 

[tmyers]

From my stand point, I like the table idea the most since it allows me to structure it to be exactly what I want it to be. That would save me the time of possibly restructuring my products table. Making the table exactly how I want would then allow making the report to be a much simpler process. Still use checkboxes to allow the selection.

With that in mind, how would I make a query to only show the items check and then COPY the selected into a temp table? I wouldnt want to remove them from my products table all together.

 

[theDBguy]

From my stand point, I like the table idea the most since it allows me to structure it to be exactly what I want it to be. That would save me the time of possibly restructuring my products table. Making the table exactly how I want would then allow making the report to be a much simpler process. Still use checkboxes to allow the selection.

With that in mind, how would I make a query to only show the items check and then COPY the selected into a temp table? I wouldnt want to remove them from my products table all together.

Maybe with something like:

SELECT * INTO TempTable FROM YourTable WHERE FieldName=True

 

[Pat Hartman]

The normal method would be to use a query with selection criteria. If there is no common value, then the user would have to select individual items. Here is a couple of examples of the different ways to use a multi-select listbox for this purpose.

 

[tmyers]

Here is a fun feature I wouldn't mind.

Have a message box pop up when my code to select all of a vendors item runs and tells the users the total value of the product. How complex would that be? I have two calculate fields on my form. One combines all pricing for an item (such as freight and such) to show its true cost, then another field takes that total and multiples it by quantity.

How could I then code to total the entire value of items check and display it in a message box?

 

[mike60smart]

Hi

I would imagine you should use the standard practice of Main Form with Subform(s)

This would allow you to select all values required for the Report.

You would then just need a Command button to Open the Report to show the current values on your Form

 

[Pat Hartman]

Using a dSum() on the query you used to do the selections would do the trick.