ms access creating report via check boxes

Question

Good day

Created Tables = Suppliers, Products

I created a form.I created a combo-box that displays all products of a particular company. I need that combo-box that displays all products to have a check-box or any other control next to each product SO that I will be able to select more than one product.

Then how do i create a report of only the products checked.

....newbie ms access 2007

Answer 1

Unfortunately that slick new multi-value drop down combo box with a check box included in the columns is not available from a programmer’s development point of view. (The ability to use this control for our own needs is rather becoming a frequent request these days - it is very slick contorl)

I’m not sure if others posters are confused or not aware of this new feature in access 2007. So it’s very natural that you’re asking for this ability to use this cool combo box feature to drive and feed parameters to a report.

Unfortunately without a lot of coding tricks and use a bound table for the selected values you can’t use that control. ( So it’s possible, but not easy).

The recommended approach in this case is to simply drop a list box on to your prompt form.

Make the first column of the listbo the primary key or so called product ID that’s used internally in your application. Make the 2nd second column the description that the user will see and select in the list box. (the wizard should make the first collumn lenght zero so that the product IDs are not seen by the user - if not you can set this manually after the wizard creates the list box for you).

You’ll then have to open up the form in design mode and change in the other tab of a list box to set and allow multi select=yes. This setting will allow the user to select more than one option in the list box.

You’ll then need to place a button on the form with the following code behind that button that takes the list of parameters or values from the list box and passes them to the report

Dim MySql         As String
Dim MySelection   As Variant

MySql = ""
   If Me.lstProducts.ItemsSelected.Count > 0 Then
      For Each MySelection In Me.lstProducts.ItemsSelected
         If MySql = "" Then
            MySql = "("
         Else
            MySql = MySql & " or "
         End If
         MySql = MySql & " ProductID  = " & Me.lstProducts.Column(0, MySelection)
      Next MySelection
      MySql = MySql & ") "
   End If
End If

Docmd.OpenReprot “nameOfRepor”,acViewPreview,,mySql

The above assumes your field for product id is ProductID