A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Multiple Checkboxes in Search by Form



 
 
Thread Tools Display Modes
  #1  
Old October 6th, 2005, 07:35 PM
Leslie
external usenet poster
 
Posts: n/a
Default Multiple Checkboxes in Search by Form

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).
  #2  
Old October 7th, 2005, 05:48 AM
Allen Browne
external usenet poster
 
Posts: n/a
Default

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" wrote in message
...
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).



  #3  
Old October 7th, 2005, 03:20 PM
Leslie
external usenet poster
 
Posts: n/a
Default

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" wrote in message
...
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).




  #4  
Old October 7th, 2005, 03:46 PM
Allen Browne
external usenet poster
 
Posts: n/a
Default

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.

--
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" wrote in message
...
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

"Leslie" wrote in message
...
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).



  #5  
Old October 7th, 2005, 04:07 PM
Leslie
external usenet poster
 
Posts: n/a
Default

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.

"Allen Browne" wrote:

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.

--
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" wrote in message
...
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

"Leslie" wrote in message
...
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).




  #6  
Old October 7th, 2005, 04:14 PM
Allen Browne
external usenet poster
 
Posts: n/a
Default

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

--
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" wrote in message
...
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.

"Allen Browne" wrote:

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.

--
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" wrote in message
...
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

"Leslie" wrote in message
...
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).






 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I create a search form in Access? Tatakau Using Forms 0 August 26th, 2005 04:39 PM
Search fields in form spacerocket Using Forms 0 August 25th, 2005 10:25 AM
strategy for data entry in multiple tables LAF Using Forms 18 April 25th, 2005 04:04 AM
Requerying a pop up form to display in the main form Jennifer P Using Forms 13 April 5th, 2005 06:59 PM
Search form using multiple tables and Union Fysh Running & Setting Up Queries 1 February 15th, 2005 06:49 PM


All times are GMT +1. The time now is 01:15 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.