Results 1 to 10 of 10
  1. #1
    archaeofreak is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2013
    Posts
    10

    How to loop through all checkboxes on form?


    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?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,621
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    mrojas is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Sep 2013
    Location
    Concord California
    Posts
    72
    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

  4. #4
    archaeofreak is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2013
    Posts
    10
    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!

  5. #5
    archaeofreak is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2013
    Posts
    10
    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?

  6. #6
    mrojas is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Sep 2013
    Location
    Concord California
    Posts
    72
    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 & ")"

  7. #7
    archaeofreak is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2013
    Posts
    10
    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?

  8. #8
    mrojas is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Sep 2013
    Location
    Concord California
    Posts
    72
    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.

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    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

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,621
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Checkboxes in a form, how to save the data
    By alex423 in forum Forms
    Replies: 2
    Last Post: 08-12-2013, 12:28 PM
  2. Replies: 13
    Last Post: 07-03-2013, 03:08 PM
  3. Checkboxes not displaying properly in form
    By swavemeisterg in forum Forms
    Replies: 3
    Last Post: 06-01-2012, 01:33 PM
  4. Form queries. Multiple checkboxes.
    By radink in forum Queries
    Replies: 4
    Last Post: 04-27-2011, 07:34 AM
  5. Disable Checkboxes for a row in Form
    By seshan in forum Programming
    Replies: 1
    Last Post: 02-05-2010, 07:36 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums