Create reports based upon combobox selections

[vanlanjl]

Ok I am new with reports and never fully understood them for my purposes. I have a database that I created for work where a user can enter in customer information like name number location etc. There is also a table for assets that keeps track of assets added and who these assets belong to.
My dilemma is that my boss wants to be run reports.
Example:
Select computer model type - show users that have this model
Select model type - how many of this model type in selected location

How many of selected model contain selected operating system

And so on and so forth.
My main problem in understanding this is, do I really need to create a query for every type of search (seems long and time consuming and data consuming) or can I create form where the user can select from combo boxes then hit a cmd button to open a report that will pull the info selected. I have tried this and it doesn't seem to work when opening the report.
Thanks

 

[FancyPrairie]

1. Create your report (based on a query that will display all records)
2. Copy the code from this FAQ faq181-5497 and open a new module and paste the code into the new module.
3. Create a form from which the user can select/enter the criteria they want to filter the results of the report.
4. Add a command button to the form and in the OnClick event of the command button add this line of code: Docmd.OpenReport "YourReportName",acViewPreview,,BuildWhere(Me)

The code found in the FAQ will scan through all of your controls on the form and return the Where clause for you based upon the selections/entries made by the user. The header within the code explains how it works. Basically, it's based on setting the Tag property of each control correctly.

To begin, just add one multi-select list box on your from that contains the list of possible computer model types. Make sure the Tag property of the ListBox is setup correctly (something like: Where=TableName.ComputerModel,string). Then select 0 to many items from the list box and open the report as specified above. The report should only list those items selected in the ListBox.

 

[vanlanjl]

Tried it, and it completely slaughtered my entire data base. Luckily I ran it on a backup copy and not the original.

Any one else have any ideas..
Thanks

 

[FancyPrairie]

What do you mean it "slaughtered" your database? What exactly did you do?

Many people have used BuildWhere without problems. I can't think of one example of how doing what I said, would cause a problem like that. I would speculate that you have other problems with your db. Have you tried compact and repair?

 

[dhookom]

If you really want a lot of flexibility, I would create a master query of the fields required by your boss. Then open Excel and create a pivot table based on the query from Access.

Duane
Hook'D on Access
MS Access MVP

 

[vanlanjl]

Well it wouldn't even let it open my database after it was input. But that is beside the point, Im not mad just trying to figure this out, so here is an in-depth look at what i have so far. If you need more info please let me know.

So here's the deal I have a form called "frmSearchModel"
it has two combo boxes titled
"cmbModel"
"cmbName"

there is one command button titled "cmdOpenReport1"

the report is named "rptSearchByModel"
"cmbModel" is connected to "tblModel" which consists of two fileds "Model_ID" (PK) and "Model"

"cmbName" is connected to "qrySystemPrepCHeckList" query and filed "File As" whish is a cobination of first name and mast name. (There is also a table name "tblContacts" which has seperate fields for first and last name)

Also there is a "tblOfficeVersion", "tblOSversion" and "tblAssets"

"tblAssets" and "tblContacts" are connected by one field names "username"

the database works great as for as inputting information and being able to link the assets to the appropriate user and then printing off customized documents for payroll and such.
But now I need to make it where you can have custom reports based on search criteria. I am not a sql guru or even a Access guru but feel confident that i can follow instructions and figure this out if someone could push or shove me in the right direction.

Right now I just need to know how to have a form with two combo boxes and the selections of the two combo boxes open a report based solely on those selections. Thanks for your help thus far

 

[FancyPrairie]

I'm assuming that your report is querying the table tblAssets. I also assume that tblAssets contains a field for computer model and person's name (first and last). So, I'm also assuming that the record source for you report is based on a query, something like this:

Select Model_ID1, UserName1... From tblAssets

I would change your 2 comboboxes to listboxes so the user can select more than 1 item within the list box. That is, the user can select 1 or more model numbers to be included in the report.

The row source for cmbModel should be "Select Model_ID, Model from tblModel Order by Model". The column count property should be 2 and the column width property should be, say, 0in;1in. The tag property, if using BuildWhere, should be Where=Model_ID1,string.

The row source property for cmbName should be "Select [File As] From qrySystemPrepCheckList Order By [File As]". The tag property, if using BuildWhere, should be Where=UserName1,string

Again, before beginning, make a backup copy of your database. Then compact and repair it.

Assuming you've copied and pasted the BuildWhere routine, found in the FAQ, into a new module, add the following code to the onclick event of cmdOpenReport1

msgbox BuildWhere(Me)

Your form should not consist of 2 list boxes whose tag properties are set and 1 command button whose onclick event simply displays the results of BuildWhere(Me).

Now, open your form select 1 or more items from either or both of the list boxes and then click on your command button. The messagebox will display the Where clause the report would use, without the word where.

Once you have it working, then, instead of the message box, open the report as "Docmd.OpenReport "YourReportName",acViewPreview,,BuildWhere(Me)

 

[jedraw]

Here is a link to a tutorial showing how to create a dynamic report.
I think this might help you understand the pieces involved.

http://www.fontstuff.com/access/acctut19.htm

 

[vanlanjl]

-jedraw-

Thank you very much! This is ecatly the type of info I was looking for. You rock!

 

[vanlanjl]

okay it still seems to not be working. First I will Provide names and such then I will explain what is happening.

"Form1"
combobox "cboModel"
Row Source

SELECT [tblModel].[ID], [tblModel].[Model] FROM tblModel ORDER BY [Model];

combobox "cboContactName"

SELECT [Query1].[Contact Name] FROM Query1 ORDER BY [Contact Name];

quick explanation: I have a table named tblCOntacts and in this table i have 3 fields "LastName" and "FirstName" and "Initial" becuase i have multiple employess that have the same last names and sometimes same first and last name. So I have a query titled "Quer1" with 2 feilds in it titled:
"File AS" and "Contact Name". Each of these fields combines the first and last name into one field. one does it first name last name and the other last name first name.

and then on form1 i have two cmdbuttons "cmdApplyFilter" and "cmdRemoveFilter"
Here is the code:

Option Compare Database
Option Explicit

Private Sub cmdApplyFilter_Click()
    Dim strModel As String
    Dim strContactName As String
    Dim strFilter As String
' Check that the report is open
    If SysCmd(acSysCmdGetObjectState, acReport, "rptContacts") <> acObjStateOpen Then
        MsgBox "You must open the report first."
        Exit Sub
    End If
' Build criteria string for Office field
    If IsNull(Me.cboModel.Value) Then
        strModel = "Like '*'"
    Else
        strModel = "='" & Me.cboModel.Value & "'"
    End If
' Build criteria string for Department field
    If IsNull(Me.cboContactName.Value) Then
        strContactName = "Like '*'"
    Else
        strContactName = "='" & Me.cboContactName.Value & "'"
    End If
' Combine criteria strings into a WHERE clause for the filter
    strFilter = "[Model] " & strModel & " AND [Query1].[ContactName] " & strContactName
' Apply the filter and switch it on
    With Reports![rptContacts]
        .Filter = strFilter
        .FilterOn = True
    End With
End Sub

Private Sub cmdRemoveFilter_Click()
    On Error Resume Next
' Switch the filter off
    Reports![rptContacts].FilterOn = False
End Sub

So this is what happens.
I first open the report "rptContacts" then open the "form1"
i choose a model from the model box and leave the name area blank and click the "apply filter" button. this causes a small form "Enter parameter value" opens and wants me to input a model and then will do the same thing again for the contact name. If I enter values into it, the report comes up blank. Aslo the refresh button is not refreshing the filter. Any Ideas? If you need more info let me know.
Thanks