Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Oct 2002
    Location
    Saskatoon, Saskatchewan, Canada
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multiple check boxes flowing into a table (Access 2003)

    I'm building a form for data entry of a paper skills inventory sheet. What I'd like to do is "replicate" the paper sheet in a form so that the data entry person can merely click on check boxes beside the skill name.

    My problem is that I want it to flow into a table with the individual's ID in column 1 and the skill ID in column 2. I can't get my head around how to attach those check boxes to the table in a way that each check will create a new line in the table.

    In case there are better ways to approach this problem, here is the scenario. The form has 12 groups of 15 skills, ranging from knitting to welding to Access programming. Participants check off all that apply to them I want to end up with a system where I can do a query for everyone who can do Access programming and get a list of names and contact info. I've got the name part built. I'm just struggling with getting the many individual items into the tblSkills table with doing it by hand or with a drop down of 150 items or 12 drop downs of 15 items.

    Thanks for any help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multiple check boxes flowing into a table (Access 2003)

    One option is to use an unbound form with lots of check boxes and a "Save" command button that writes the results into the table. Lots of work, but you can make the form look the way you want.

    Another option is to use a table with three columns: individualID, skillID and a Yes/No field for the check box.
    For each participant, create as many records as there are skills in this table,
    Use a continuous form based on this table (filtered to show only the records for the current participant).
    You'll get lots of records, but the form is much easier to design and maintain.
    For analysis, you'd start with a query that selects only records with Yes in the Yes/No field.

  3. #3
    New Lounger
    Join Date
    Oct 2002
    Location
    Saskatoon, Saskatchewan, Canada
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple check boxes flowing into a table (Acc

    I think I'm going after that first option "an unbound form with lots of check boxes and a "Save" command button that writes the results into the table."

    So, I can get the form to look how I want, I just don't know how to begin to get the checked boxes to be 'written to a table.' I have a bad feeling the next step is VBA and I'm not very knowledgable or adept at that.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multiple check boxes flowing into a table (Acc

    Yes, it would involve VBA. If you name the check boxes chkSkill1 to chkSkill50, where the number corresponds to the SkillID, you could use code like this:

    Private Sub cmdSave_Click()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim i As Integer

    On Error GoTo ErrHandler

    Set dbs = CurrentDb

    ' Delete existing records for employee
    strSQL = "DELETE * FROM tblSkills WHERE EmployeeID=" & Me.EmployeeID
    dbs.Execute strSQL, dbFailOnError

    ' Add new records
    Set rst = dbs.OpenRecordset("tblSkills", dbOpenDynaset)
    For i = 1 To 50 ' adjust as needed
    If Me.Controls("chkSkill" & i) = True Then
    rst.AddNew
    rst.EmployeeID = Me.EmployeeID
    rst.SkillID = i
    rst.Update
    End If
    Next i

    ExitHandler:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    You must substitute the appropriate table, field and control names.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •