ms access check box

Question

I created a form, displaying company. created combo-box in the form to list all products of that company with a check-box next to each item*(IN THE COMBO BOX). how do I create a report on only the items that were checked,

OR soloution2, I tried showing all products of that company on that form in sub-data-sheet, with a checked box field. how do I create a report on only the items that were checked,

not very proficient in access... thanks a MIL

Answer 1

In this article, Microsoft shows how to retrieve the values from a listbox as a string. This can then be used to create an SQL statement, for Openargs (depending on your version of Access) or as the WHERE argument for the report:

 DoCmd.OpenReport "ReportName",acViewPreview,,"ID IN (" & ListOfIDs & ")"

Note that you will need quotes for a list of strings:

 "A","List","Of","Strings"

But not for numbers:

 1,2,3,4

This would be similar for the subform, but the best bet would be to build the sql statement and to use that as the Record Source:

 strSQL="SELECT ID, SomeField FROM SomeTable WHERE ID IN (" & ListOfIDs & ")"
 Me.[NameOfSubformControl].Form.RecordSource=strSQL

You might like to use a command button to do this. Be sure to use the name of the subform control, not the the form contained. It would be easier with a simple listbox that allowed only one company to be selected, because you could then set the Link Child (to the company ID) and Link Master (name of the listbox) fields for the subform control.

In both cases, it is best that the listbox is setup with two columns, Company ID and Company Name, with Company ID as the hidden, bound column.