Checkbox Form

[Rob]

I have a lookup table of all the states "tblStates". Another table for
people "tblPeople". I need to create a form that tracks the states in
which people have lived. Therefore one person can have many states in
the residence table "tblHomes". Here is the catch. I need to create a
form that has checkboxes for all the states showing at the same time.
When the user checks off states in which the person has lived I want it
to add each of the checked states as a new record in tblHomes for that
person. Any ideas on how to accomplish this?

[pietlinden]

Rob wrote:

I have a lookup table of all the states "tblStates". Another table for
people "tblPeople". I need to create a form that tracks the states in
which people have lived. Therefore one person can have many states in
the residence table "tblHomes". Here is the catch. I need to create a
form that has checkboxes for all the states showing at the same time.
When the user checks off states in which the person has lived I want it
to add each of the checked states as a new record in tblHomes for that
person. Any ideas on how to accomplish this?

okay, I actually tested this one...
I have a form, which is *not* bound to a table.
On it are
a textbox, "txtPersonID", which is formatted as a Fixed number, 0
decimal places.
a series of checkboxes for some states. (This is a test - I only made
4, but that's not really relevant.)
I changed the .Tag property of each checkbox to the 2-letter
abbreviation for the state it represented. (Sorry, that's going to be
tedious)
Then I added a button with this code attached:

Private Sub Command8_Click()

Dim rs As DAO.Recordset
Set rs = DBEngine(0)(0).OpenRecordset("tblHomes", dbOpenTable,
dbAppendOnly)

Dim ctl As Control

For Each ctl In Me.Controls
If TypeOf ctl Is CheckBox Then
If ctl = True Then
rs.AddNew
rs.Fields("PersonID") = Val(Me.txtPersonID)
rs.Fields("State") = ctl.Tag
rs.Update
End If
End If
Next ctl

rs.Close
Set rs = Nothing

End Sub

The only thing you should have to tweak is the PersonID part