Re: Multiple Checkboxes in Search by Form

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?

"Allen Browne" wrote:

> 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
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Leslie" <Leslie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:887A7CD2-589B-4D39-8621-67AC3ED0206B@xxxxxxxxxxxxxxxx
> >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).
>
>
>
.

Relevant Pages

  • Re: Different data shown based on user input
    ... my advice changes with the info that there are over 200 checkboxes. ... I suggest you use combo boxes. ... Dim strStub As String ...
    (microsoft.public.access.queries)
  • Re: Different data shown based on user input
    ... A form that knows which check boxes have been checked can be created like ... --check only one check box and observe that the other checkboxes are ... The basic process is to build the query using the fields that have been ... Dim strStub as String ...
    (microsoft.public.access.queries)
  • Re: Checkbox cannot be used on 2003 document
    ... If the check boxes are from the legacy controls (which they will be if the ... can insert and remove checks from the checkboxes in that document. ... I could not find teh checkbox tool in teh 2007 native ...
    (microsoft.public.word.docmanagement)
  • Re: Multiple Checkboxes in Search by Form
    ... If none of the boxes are checked, ... Use a multi-select list box to filter a report ... rather than allenbrowne at mvps dot org. ... I'm using checkboxes because of the large ...
    (microsoft.public.access.queries)
  • Re: checkboxes on a form and values in a table
    ... interfaced with 5 unbound check boxes. ... > I am very new to access, but I do have some VBA experience. ... > As the user checks the checkboxes, I will use the OnClick method to ... the form contains the record navigation controls at the bottom. ...
    (microsoft.public.access.modulesdaovba)