Multiple Checkboxes in Search by Form

L

Leslie

I have 8 products chosen by checkbox in a search form, they check the box to
retreive all the items entered for a specific product or products, they
should be able to choose more than one product, i.e. I want product 1 and
product 4, but need to be able to choose up to all 8. There are multiple
search criteria on the form. I have all the other searches working, but the
checkboxes. These boxes are checked when the data is entered into the table
via a new product change form. I'm using checkboxes because of the large
number of cretiera that could be possible and even our own people can spell a
product name differently making it impossible to search and get all the
information on specific product(s).
 
A

Allen Browne

Where you are giving the user lots of search options, the most efficient way
is to build the SQL statement dynamically. You can build just the WHERE
clause and use it as the Filter for a form, or the WhereCondition for an
OpenReport action.

This example shows how to loop through check boxes named chk1, chk2 etc, and
build up the WhereCondition string to open a report, based on a numeric
field named ProductID. If none of the boxes are checked, then all products
are returned. If one or more boxes are checked, only those product(s) are
returned:
Dim strWhere As String
Dim lngLen As Long
If Me.chk1.Value Then
strWhere = strWhere & "1, "
End If
If Me.chk2.Value Then
strWhere = strWhere & "2, "
End If
'etc for other check boxes.
lngLen = Len(strWhere) - 2 'Without trailing comma and space.
If lngLen > 0 Then
strWhere = "(ProductID IN (" & Left$(strWhere, lngLen) & "))"
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

One disadvantage of the check boxes is that you have to redesign your form
every time you add another product to the database. You could avoid that by
using an unbound multi-select list box. For details, see:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html
 
L

Leslie

Thank you for the suggestion, but now I have a question on that. If I change
from checkboxes to a multi-select list box can I also make that change in my
table, and if I can is that set up in the design window for the table, I have
never done a multi-select box? Can the list box be a combo box instead?
 
A

Allen Browne

The multi-select list box needs to be unbound.

If you have multiple yes/no fields across your table, the design is
incorrect. To store that, you need a related table with a record for each
valid combination.
 
L

Leslie

A table with valid combinations was my first choice, but that was not
acceptable to the engineering manager, so I did checkboxes (yes/no) for each
product and they can select multiple products on the data entry form by
checking more than one checkbox.
 
A

Allen Browne

That's a really bad design. It means you have to redesign the table and all
affected queries, forms, report, macros, and code, every time you add a
product!!!

Instead, you need to use a junction table between the 2 main tables. For an
example, see:
Relationships between Tables (School Grades example)
at:
http://allenbrowne.com/casu-06.html
 
Top