How to loop through all checkboxes on form?

[archaeofreak]

Can anyone share a code example of how to loop through all checkboxes on a form, determine which are checked, and perform some function with those that are checked?

[June7]

Is this a single checkbox bound to field in which case must loop through records? Or is this multiple checkboxes for a single record?

More than one way to accomplish each.

For the latter, if checkboxes have names like chk1, chk2, chk3, etc, a very simplistic approach (replace x with the highest checkbox number):

For i = 1 to x
do something with Me("chk" & i)
Next

Otherwise, review http://www.tek-tips.com/faqs.cfm?fid=5010.

[mrojas]

Here's another option:

Dim ctl As Object
For Each ctl In Me.Controls
If TypeOf Ctl Is CheckBox Then
if ctl.Value = True then
' Do something
end if
End If
Next

[archaeofreak]

OK: I have this:

Private Sub btnGenerate_Click()

For i = 1 To 6
If Me("chk" & i) = True Then
CurrentDb.Execute "CREATE TABLE " & Me.txtProjectName & "(ID counter, VesselShape text, NeckHeight integer) "
End If
Next
End Sub

I am trying to create a new table with fields named the same as the checkbox labels in my form.

The new table would capture the checkboxes that are checked and the fields in the table would be from the labels of the checked boxes.

The code above creates a new table with ID, VesselShape and NeckHeight as fields.

How can this code be altered, so that when the user checks specific checkboxes on the form, a table is created with the labels of those checked boxes?

Thanks!

[archaeofreak]

Dim ctl As Object
For Each ctl In Me.Controls
If TypeOf Ctl Is CheckBox Then
if ctl.Value = True then
' Do something
end if
End If
Next


This might work too if the 'Do something = add the checkbox's label name as a field in the table being created!

How to do that part?

[mrojas]

I would do something like this:

Dim strFields as string

Dim ctl As Object

strFields="("

' Loop through all controls, regardless of how many (in case you need add or remove check boxes, the code will continue to run without modifications.)
For Each ctl In Me.Controls
If TypeOf Ctl Is CheckBox Then
if ctl.Value = True then
strFields=strFields & ctl.caption & " FieldType, "
end if
End If
Next
' Variable has the name of all the check boxes' labels
strFields=Left(stFields, (Len(strFields)-1)) ' Remove trailing comma
' Create table
CurrentDb.Execute "CREATE TABLE " & Me.txtProjectName & strFields & ")"

[archaeofreak]

I get the following error:

Run time error '438'
Object doesnt support this property or method...

Located here:

strFields = strFields & ctl.Caption & " FieldType, "

Any thoughts?

[mrojas]

Make sure that you are not literally using "FieldType". Instead, use "Integer" "Text" etc. without the quotation marks, and that you remove the trailing comma. The final strFields string should not have a comma at the end.

[ssanfu]

This is really a bad idea!

However, it is your dB.

You have to use DDL to create new tables/modify tables.

See these sites (I googled "access DDL create table"):

How to use common Data Definition Language (DDL) SQL statements for the Jet database engine
http://support.microsoft.com/kb/180841

CREATE TABLE Statement (Microsoft Access SQL)
http://msdn.microsoft.com/en-us/libr...ice.12%29.aspx

Allen Browne: DDL Code Examples
http://allenbrowne.com/func-DDL.html

[June7]

I agree with Steve. Why do you have code to create and modify tables? Smacks of poor design. Because the design edit doesn't stop with the new table. Have to create/modify queries, forms, resports, code each time table is created. This was also addressed starting in post 7 of https://www.accessforums.net/access/...ion-37866.html

We still have no understanding of your data structure. How does the statement in final post of other thread relate to this effort:
Not a new table every time an object is received, rather each project would be within a table and each project consists of thousands of objects.