Multiple checkboxes

[Guest]

Hi -

Let's assume your form has a field txtCompany with the
company name in it.
Let's assume the checkboxes are named chkCategory1,
chkCategory2, etc.

For each chkCategory checkbox, I'd write something like
the following:

Private Sub chkCategory1_AfterUpdate()
If me.chkCategory1 = True Then
WriteNewRecord Me.txtCompany, "Category1"
Else
DelRecord Me.txtComapny, "Category1"
End if
End Sub

I'm assuming that if a checkbox is unchecked, you'll want
to eliminate that category record, if there is one.

Elsewhere in the module, create the WriteNewRecord and
DelRecord subroutines, perhaps something like this:

Private WriteNewRecord(Company As String, Category As
String)

Dim qdf as DAO.QueryDef 'You may prefer to use ADODB
Dim stSQL as String
stSQL "INSERT INTO CompanyCategories ([Company],
[Category]) " _
& "VALUES (" & Company & ", " & Category & ")"
Set qdf = CurrentDb.CreateQueryDef("",stSQL)
qdf.Execute
Set qdf = Nothing

End Sub

This may not be the table structure you are planning. If
you have a CompanyCategories table apart from a Companies
table--probably a good idea for normalization--then you'll
probably be using a CompanyID rather than a CompanyName.

As it stands, it would take some extra work to load the
record in the form (you'd have to check for the existence
of every possible category record for that company to set
your form's checkboxes correctly). But perhaps this will
at least get you going.

I'm only moderately experienced, so there may be a better
way to do this, but the above approach has worked for me
in the past.

Phil Freihofner
Oakland

-----Original Message-----
I need some help creating a form with multiple checkboxes

on it. Iâ?Td like to create a form that shows a company
with a list of categories. The categories would be a list
of checkboxes and a company can have multiple categories.

The problem is, how do I make it so when a checkbox is

checked based on its corresponding category it creates a
record in the â?otblresponseâ? table with the
corresponding company?

Iâ?Tm assuming there will be some sort of programming

involved for each textbox, but Iâ?Tm up for the task.
Examples would be greatly appreciated! I know it can be
done by making the categories as the fields with a data
type of yes/no, but this isnâ?Tt conducive in the long
term especially with the other tables Iâ?Tll be adding.

Hereâ?Ts my table setup:

tblCompany
-CompanyID (PK)
-CompanyName
-etcâ?¦..

tblCategories
-CategoryID (PK)
-CategoryName

tblResponse
-ResponseID (PK)
-CompanyID (FK)
-CategoryID (FK)
-Response (yes/no field)

Hereâ?Ts is an ex. Of the form Iâ?Td like to make:

Company Name:
Address:
Etcâ?¦..

Category 1 [ ]
Category 2 [ ]
Category 3 [ ]
Etcâ?¦.


Thanks!!

Josiah

.