How to use two multi-select list boxes to query table and make report.

[Rivian111]

I am using Access 2007 and I have one table with numerous fields. Two of these fields allow multiple values that represent selected criteria by the user. These criteria are entered via a form with combo boxes where the criteria are hard coded in the ROW source. The user is limited to the options presented (non editable list). By way of example, one field is called Color and another field is called Shape.

Color options are: Red, Green, Blue, Orange, Yellow, Black

Shape options are: Circle, Square, Triangle, Hexagon, Pentagon

The user may select one or more (or none) of these options so the content of these two fields in may have one, multiple or null entries.

For reporting purposes I need to allow a user to generate a report that lists all records according to a query of these two fields. For example, out of 1000 records, I need to find all records where the field Color has red and/or yellow recorded and Shape has square, Triangle and pentagon recorded. Or the user may select some other combination from the two where one or more criteria are selected.

I have been trying to do this by using two list boxes in a form where each list box shows all possible options for each field and the user would select one or more of the options from each list boxes. Then the user clicks on a button that sends the corresponding records to a report.

I have successfully developed one list box in a form that allows for multiple selections of Color. When the user clicks on a view report button, the form closes and a report is opened listing all records that have red in the color field. I need to add a second list box for Shape so my users can select one or more colors and one or more shapes to generate a report showing records where the selected criteria apply.

The code shown below for my one working list box comes from another forum. I am not really a VB programmer, though I am comfortable working with VB if I have sufficient guidance.

My table is called Products.

My two fields in question are called Color and Shape.

My form is called frmProducts and I have my reference tag set to rptProducts.

My list box , called lsColorReport, has Row Source to define the colors that need to be listed.

My command button called cmdReport uses this sniped of VB code OnClick:

Private Sub cmdReport_Click()
  Call MultipleValueCriteria(Me, _
   Me!lsColorReport, "[Products.Color.Value]")
End Sub

The Call MultipleValueCriteria code is:

Function MultipleValueCriteria(pform As Form, _
 pcontrol As ListBox, pfield As String)
  'Launch rptCustomers using
  'Or criteria built on field
  'passed by pfield; report must be
  'passed via the form's Tag property.
  Dim var As Variant
  Dim strCriteria As String
  If pcontrol.ItemsSelected.Count = 0 Then
    MsgBox "Please select a city.", _
     vbOKOnly, "Error"
    Exit Function
  'Build a SQL statement using
  'selected cities.
  Else
    'Criteria expression uses literal string
    'values. If using numeric or date values,
    'update delimiter component.
    For Each var In pcontrol.ItemsSelected
      strCriteria = strCriteria & _
       pfield & " = '" & _
       pcontrol.ItemData(var) _
       & "' Or "
    Next var
  End If
  strCriteria = Left(strCriteria, _
   Len(strCriteria) - 4)
  Debug.Print strCriteria
  'Open filtered report and close form.
  DoCmd.OpenReport pform.Tag, _
   acViewPreview, , strCriteria
  DoCmd.Close acForm, pform.Name
  Set pcontrol = Nothing
End Function

Can anyone suggest how I need to modify this code and/or my form structure so that I can generate a filtered report based on the multi select criteria of two list boxes, one for Color and one for Shape?

Thanks.

 

[Locoman]

The way I would do this is: in the OnOPen property of the report, set this to an event procedure which uses the strCcriteria you have built, e.g.

DoCmd.Apply Filter, "YourSQLstringCriteria"

 

[FancyPrairie]

Check out this FAQ faq181-5497. To make it work, simply copy the code and paste it in a new module. Documentation on how it works is included in the header of the code module.

Basically, you define the where clause in the tag property. For example, suppose your table contains a field call strColor. You might want to put a list box on your form that lists the colors from which the user can choose (single or multiple select). The tag property of the list box would look something like this: Where=strColor,string

Open your report like this:
Docmd.OpenReport "rptYourReport",acViewPreview,,BuildWhere(Me)

(Note that in rare cases, depending on how your query is setup, you may need to set the Recordsource of your report equal to BuildWhere(Me("yourCrieriaFormName")) rather then doing it via the OpenReport method). You do this in the OnOpen event of the report.

The BuildWhere routine will loop through all of the controls on your form to determine which ones are to be used for building the Where clause. If the tag property does not contain the clause "Where=", or it's disabled, or invisible, BuildWhere will ignore it. The BuildWhere returns the Where clause (without the word Where).

It should only take you a few minutes to test it by doing the following:
1. Copy the code from the FAQ and paste it into a new module.
2. Create a list box with your choices.
3. Set the tag property as stated above.
4. Add a command button on your form
5. In the code for the OnClick event of the command button, add the following code: Msgbox BuildWhere(Me)

The msgbox will show you the Where clause it built. If there is a problem in how you set your Tag property, the msgbox should aid you in seeing how it works.

BuildWhere works for single and multiple list boxes, comboboxes, text boxes, date ranges, etc.

 

[Rivian111]

FancyPrairie this worked like a charm! Interestingly enough, solving this issue was a first step to a more complicated form where I would include some text boxes and check boxes. Your code does the trick for everything I need.

I have one question however. I am unclear how the code in the module I added is being reference by the Docmd.OpenReport "rptYourReport",acViewPreview,,BuildWhere(Me) code. How does access know to reference the BuildWhere function? The only time i have used modules is where the module is called by name and knowing the name matters. Surely I'm showing my inexperience with Access with this question, but thought I would ask.

Thanks for your help!

 

[FancyPrairie]

Below the header section (which is the section where I document how to use the routine) you will find the declaration for the function. Which is "Function BuildWhere...". Since it is a public function, forms, reports, etc. can reference it from within your database. The function returns a string, which is the Where clause without the word Where. However, I just noticed that I failed to declare the function as a string. Not really a problem, but it should read:

Function BuildWhere(frm As Form, _
ParamArray varCtl() As Variant) As String

I'm really glad it worked for you.