Queried in Forms

[DarkProdigy]

Hey guys;

What I'm looking to do is setup up an invoicing-type system. I want to create a form wherein there will be several list/combo boxes to select various parameter (ie. product id, accessories, materials etc). As well on this form I want to put a "submit" button that will open up a report which will have the information from the various inputs(such as a breakdown of costs and such). I think I can figure the button bit on my own, but I was wondering how I would go about utilizing the boxes. I tried using a parameter-type query but this opens a dialogue box when the form is opened which I don't want. I want the selections to be stored until the submit button is pressed.

Any suggestions on how I might be able to accomplish this?

Thanks in advance

 

[carash77ash]

i would first make a 2 queires 1) that gives you all the information that you want un filtered.
Then 2) create a query that has filters based on the form.(create an unbound form with the drop down boxes that you are after to filter query).

Then down the bottom create a print button.(that will print query filter)

You will them need to create a report based on the query filter.

Ash

 

[SammyJ]

There are two good ways to do this, and a few others I can think of as well.

1) Have your report refer to the form controls in its record source. Get to the query builder behind the report by clicking on the builder box next to the report's Record Source. Add in any criteria you want by referring back to the form with something like this forms!YourForm!cboProductID

It gets messy though if the parameters are optional, and the report gets very confused if you don't run it from the form.

2) Add a WHERE clause when you call OpenReport. I think it is the fourth parameter and it needs to look something like this:

Docmd.OpenReport rptYourName,,,"ProductID = 4356 And MaterialType = 'Gold'"

So using this method you only need to include the filtering elements the user has filled in, and your report doesn't need to know about the form.

Remember that text fields in this filter need to be surrounded by quotes but numbers don't. Dates are a little finicky too, ask me if you need them. You'll actually want to build a string up and pass that through.

Sam.