Copy Check Boxes Values of a form to a table!

[TheTeaMan]

I have a form that has many check boxes (over 61). After the user finishes checking the necessary boxes, I would like to step through each check box and copy them all to a table. The fields in the table have the same names as the check boxes names but not necessary in the same order of entry.
How would you accomplish this in VBA code? Any idea? Can anyone tell me how to use a loop function so I don't have to write each name of each check box and each table filed.
Thanks a bundle…
TheTeaMan

 

[Crowley16]

it's possible to use a bound form to automatically do this. however if you wanted to use code and looping, then you can loop through the controls on a form by using the controlls collection, and loop through the fields in a table using a recordset and it's fields collection...

e.g.

dim rs as adodb.recordset
'open the recordset

dim chk as variant
for each chk in forms("FormName").Controls
if chk.Name like "chk*" then
rs.fields(chk.Name) = chk.value
endif
next chk

'remember to update the recordset once you've finished looping

--------------------
Procrastinate Now!

 

[TheTeaMan]

Thank you Crowley16. I am a little slow. I can see how you are looping from check box to another but how to I copy the value of the check box to the table?
Thanks again.

 

[Crowley16]

the recordset...

you open the recordset based on the table, and update the records in the record set, then when you update the whole recordset, the records will be filtered back to your table

--------------------
Procrastinate Now!

 

[TheTeaMan]

I m still having a problem. Here is the code I used but it does not work:

Private Sub Command697_Click()
Dim dbNewRec As DAO.Database
Dim rstNewRec As DAO.Recordset
Set dbNewRec = CurrentDb
Set rstNewRec = dbNewRec.OpenRecordset("table1")
With rstNewRec
.AddNew
Dim chk As Variant
For Each chk In Forms("form1").Controls
If chk.Name Like "chk*" Then
rstNewRec.Fields(chk.Name) = chk.Value
End If
Next chk
End With
dbNewRec.Close
Set dbNewRec = Nothing
Set rstNewRec = Nothing
End Sub

 

[Crowley16]

you need to run the .update method of a recordset to actually make the changes, do this before you close the recordset...

--------------------
Procrastinate Now!

 

[TheAceMan1]

How are ya TheTeaMan . . .

In the [blue]Tag[/blue] property of each checkbox enter a question mark [blue]?[/blue] (no quotations please). You can group select to do this.

And the code:

[blue]Private Sub Command697_Click()
   Dim db As DAO.Database, rst As DAO.Recordset, Ctl As Control
   
   Set db = CurrentDb
   Set rst = db.OpenRecordset("table1",dbOpenDynaset)
   
   rst.AddNew
   
   For Each Ctl In Me.Controls
      If Ctl.Tag = "?" Then
         rst(Ctl.Name) = Ctl
      End If
   Next

   rst.Update

   Set db = Nothing
   Set rst = Nothing
   
End Sub[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[TheTeaMan]

Thank you guys (TheAceMan1 and Crowley16). Both of your solutions work great.