VB Code for Check box

[bnik]

Trying to find a example of code for a check box add the criteria for a query. Example:

If check box is checked for last name "Smith", then the query will only return all the smiths.

[ryand09]

Firstly, I think you would be better off with a ComboBox rather than a CheckBox. If you have 50 or 100 different names, then you don't really want 50-100 check boxes and each time there is a new name, you will have to add a new CheckBox control. If the combo box names are based on a query there is no need for this work. Whatever you decide CheckBox or ComboBox you supply the form and control names to the queries criteria. e.g.

select * from tblName where
LastName=Forms![FormName]![ControlName]

[bnik]

Actualy, I only have 15 check boxes that i am using. here is what i am trying to do.

In code, if the check box value is -1 then i would like to filter the qry, the field i am trying to filter is act_code field to "01-101". Then i would like to add an or statement and add another filter.....



Private Function activity_code()
If [Forms]![gen_report_frm]![rcv_101] = -1 Then
[gen_report_qry]![ACT_CODE]='01-101'

End Function

[bnik]

Currently, i have 15 "Checkbox" controls on a form. Each control represents a certian activity code in the database. What i would like to happen, is that when any of the check boxes are checked, ex. "1" , "2", "4", and "10" are checked (true), these values are applied to the criteria for a qruery,
so the results only returns the codes that are check, and i would like for this to happen via code....

Here is what the sql would look like,

select * from tablename
where act_code = '1' or
act_code= '2' or
act_code= '4' or
act_code='10'
but i don't know how to build the code for this......

[xordevoreaux]

ryand09 is right. Stop using checkboxes for this. You probably have 15 fields corresponding to your 15 checkboxes on your form, and that's pretty nasty, too.

Sounds fundamentally like you first need to separate your entities in your tables because you're blurring one with any number of other entities, it sounds like.

An entity is a stand-alone, singularly identifiable piece of information. For example, just as an example, a person is separate from the clothes he's wearing. That means that the person is an entity and the clothing is an entity.

So instead of clicking the checkboxes for the "shirt" record for smith, taylor, johnson, etc., you create a person table (tblPerson) and a clothing table (tblClothing) and then a table.

Your person table's fields could look like this:

PersonID (autonumber) (key)
PersonFirstName (text) (required)
PersonLastName (text) (required) (indexed duplicates OK)

Your clothing table could look like this:
ClothingID (autonumber) (key)
ClothingName (text) (indexed no duplicates) (required)

The person table then acts as like a clipboard roster of everyone you're trying to deal with, and with a mathematical limit of slightly over 2 billion records, we're talking a bit more versaility than 15 checkboxes.

The clothing table has its own 2 billion records you can play with on its own clipboard.

Now you need to associate any number of people with any number of pieces of clothing. Two people can both have a shirt, and a shirt can be associated with more than one person.

So you have a third roster than marries the PersonID to the ClothingID, and that third roster is its own table, and we'll call it tblmrelPersonClothing and its fields look like this:

PersonClothingID (autonumber) (key)
PersonID (long integer) (required)
ClothingID (long integer) (required)

You then go into Tools, Relationships in MS Access and drag the PersonID field from the person table to the PersonID field in the PersonClothing table, and then drag the ClothingID field from the Clothing table to the ClothingID field in the PersonClothing table.

Now you're married your person entity to your clothing entity, and ryand09's combo box idea will work wonders for you now.

[bnik]

Thanks for the assistance, i have followed your suggestions and the database is functioning as plan...thanks again.

bnik