Create queries and reports based on input from forms? 2

[tommyboyau]

Hi guys,
the problem that i'm facing is this:
We have an exisiting database in place. I would like to use forms with a gui to be able to generate some reports and filter some output etc.
For example, we have a table called "Documents" which is a list of documents.
I am looking to make a few forms to give the user easy options such as "Create a report from documents, filtered by company name". Then I would give the user the option of the company or companies they wish to filter by.
Do you get my drift? Basically I want to be able to create some sort of an Automatic Query builder which in some cases gives them more advanced options like the fields to appear in the report.
I am preety good at coding in Visual Basic as well, just not great when it comes to doing it in access. Any points? , Thanks.

 

[dhookom]

You can possibly drop in the Query By Form applet from

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

It is fairly flexible. I include it in almost every application that I create.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[FancyPrairie]

Check out this FAQ faq181-5497 It contains a function that will build the Where clause for you. It works for text boxes, ranges, list boxes, combo boxes, option groups, and check boxes.

In your case you might want to create a multiselect listbox that lists the company names from which the user may choose. Once the user selects the companies they want included on the report, simply issue this command: DoCmd.OpenReport "YourReport",acPreview,,BuildWhere(Me)

You only have to do 3 things to make it work.
1. Create a new module and copy and paste the code from the FAQ.
2. Set the tag properties of each of the controls as specified in the FAQ
3. Open the report

Note that this routine does not remove or add fields to the report (it can't be done at runtime, can only be done in design view).

 

[tommyboyau]

Thankyou to both of you.
Dhookom your little app was fantastic! I will be sure to use it in the future.
FancyPrairie, thanks for the link to the FAQ. It was exactly what I was after in the way of coding the stuff myself. No more help is required guys, Thanks heaps!