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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 05:49 PM |