I'd like to create a form with several checkboxes, but I'm not sure how to structure the tables to capture the checked items

[MajP]

1) Are you saying this is just a personal DB? You are not tracking multiple people in the database? If so you need to relate daily tasks to a person.
2) I doubt you need a creation date table. That is just a field in another table.
3) As far as tbl levels I think that is (min,plus, and elite). If there are not more fields in that table than a level name than that table is not necessary.
4) Again I think table tasks needs either the level name or the key relating to tblLevels. It needs the selected field if you want to use my approach.

 

[MajP]

Looks like the problem is going to be the form. Regardless of how the table data is structured, representing it on the form is going to be the hiccup, since one checkbox apparently equals one field and there's no way around that

Yes it will require code, but please look at the demo I posted. It is no problem.
Let me caveat, building a form that has checks would be relatively easy, building that form would be pretty complex unless it is done unbound. I assume within each group you can have a single choice. If you watch video for 90 minutes then you do not check the lesser choices.
If that is correct, do you see this form changing often? If almost never, then I would go unbound and it would be relatively easy to do. Understand that changing/adding/deleting a task requires form redesign.

 

[theDBguy]

I totally hear what you're saying and I did read up on 1NF, 2NF, 3NF, but when I tried to put that into practice it all made sense until I got to the form part. To explain, I knew that I'd need 3 tables: One that held all the tasks (in one field), a "completed" table (a way to associate all the completed tasks from the other table with a single record -- in this case a date), and a junction table to join them. The problem occurred at the form level -- there was no way to have the 27 checkboxes represented in the form if they were mere rows of records in a single field -- the access form only seems to accept fields, and since there was only one, there was literally no way to get anything but a single "task" textbox/checkbox onto the form. I could not figure out how to represent each of the 27 tasks listed in the field on the form in any way so they could accept inputs.

Hi. Just in case you didn't have enough examples, here's another one.

UtterAccess.com

www.utteraccess.com

PS. What you want to do may sound daunting now; but if you put in the effort, I promise it will be worth it in the end.

 

[Bettany]

1) Are you saying this is just a personal DB? You are not tracking multiple people in the database? If so you need to relate daily tasks to a person.
2) I doubt you need a creation date table. That is just a field in another table.
3) As far as tbl levels I think that is (min,plus, and elite). If there are not more fields in that table than a level name than that table is not necessary.
4) Again I think table tasks needs either the level name or the key relating to tblLevels. It needs the selected field if you want to use my approach.

1. yes, just one person, so no person was actually going to be in the database
2. i didn't want to have individual dates associated with each task, avoiding entering in the date over and over
3. was trying to avoid rows and rows of duplicate data (so much of this stuff seems to be a judgement call without any hard fast rules)
4. yes, realize the database is far from done, but just wanted to resolve the biggest issues first, because so much hinges on how the tables are finally setup.

 

[MajP]

I will see if I can demo this. I think you need to see it. There should not be any duplication, that is the beauty of a relational db. You only need a key to relate to the other tables.

The big question I have if those choices are singular within a group. Those look like option groups to me. If that is the case, I know of no way to do this but unbounded (or a lot of code). Again, if you can answer how often (if ever) those choices my change? If extremely rare then I can propose an unbound form. If not you will have to consider a different form design. Instead of clicking a task, I would see combo boxes where you choose a value. In truth the thing you are calling tasks, look more like task values.

 

[Uncle Gizmo]

Multiple Checklist Demo - Nifty Access

 

[Uncle Gizmo]

With 9 Subforms:-

 

[Bettany]

I will see if I can demo this. I think you need to see it. There should not be any duplication, that is the beauty of a relational db. You only need a key to relate to the other tables.

The big question I have if those choices are singular within a group. Those look like option groups to me. If that is the case, I know of no way to do this but unbounded (or a lot of code). Again, if you can answer how often (if ever) those choices my change? If extremely rare then I can propose an unbound form. If not you will have to consider a different form design. Instead of clicking a task, I would see combo boxes where you choose a value. In truth the thing you are calling tasks, look more like task values.

That would be amazing

With 9 Subforms:-

View attachment 80880

Oh my god! Thank you!!!!! That's EXACTLY what i'm looking for!!! can you share your table structure, and did you have to do a ton of code?

 

[MajP]

Those look like option groups to me. If that is the case, I know of no way to do this but unbounded (or a lot of code).

Actually I do not know what I was thinking. Even if you want to limit the choices to only one value per group, doing that with the subform is easier than I was thinking. In that case I would do it just like Uncle Gizmo did with the 9 subforms and code it to allow one choice per group (if that is a requirement). If you look at my first example the amount of code to do this is very very little. It would be the same regardless of how many copies of the subform. FYI Uncle Gizmo is in UK time, so probably cannot get an upload until the AM.

 

[Uncle Gizmo]

That's EXACTLY what i'm looking for!!! can you share your table structure, and did you have to do a ton of code?

Hi Bettany, and a belated Welcome to AWF! This is a project I've been working on for a number of years. Your question has spurred me on to advance this project, and I've taken the opportunity to make some improvements, made it more generic... I'm currently constructing a sample based on your post. If you could indicate the information you want to extract, then I can demonstrate how you can utilise the data collected.

 

[MajP]

Here is my attempt. The amount of code is not a lot, however it is not the simplest code because you are working with queries and recordset updates.

I left the last subforms for you. You need to do two things. Fill in the task table and then set the tag property of the subform to be the group name.

FYI, if you want to add a date not in the cmbobox then you can just type it in. If it is todays date then hit the today button. It will move or add the current person and date records.

 

[MajP]

Subform code

Private Sub Selected_Click()
  If Selected Then
    AddSelection
  Else
    RemoveSelection
  End If
End Sub
Public Sub AddSelection()
   Dim strSql As String
   Dim DailyDate As Date
   Dim SelectionID As Long
   Dim PersonID As Long
   If IsNull(Me.Parent.CmboPerson) Or IsNull(Me.Parent.cmboDate) Then
     MsgBox "Ensure a date and person are selected", vbInformation, "Ensure Data"
   Else
    PersonID = Me.Parent.CmboPerson
    SelectionID = Me.TaskSelection_ID
    DailyDate = Me.Parent.cmboDate
    strSql = "Insert into tblPersonDailyTasks (Person_ID_FK, TaskSelection_ID_FK, TaskDate) VALUES (" & PersonID & ", " & SelectionID & ", #" & Format(DailyDate, "MM/DD/YYYY") & "#)"
    Debug.Print strSql
    CurrentDb.Execute strSql
  End If
End Sub
Public Sub RemoveSelection()
   Dim strSql As String
   Dim DailyDate As Date
   Dim SelectionID As Long
   Dim PersonID As Long
   If IsNull(Me.Parent.CmboPerson) Or IsNull(Me.Parent.cmboDate) Then
     MsgBox "Ensure a date and person are selected", vbInformation, "Ensure Data"
   Else
     PersonID = Me.Parent.CmboPerson
     SelectionID = Me.TaskSelection_ID
     DailyDate = Me.Parent.cmboDate
     strSql = "Delete * from tblPersonDailyTasks WHERE Person_ID_FK = " & PersonID & " AND TaskSelection_ID_FK = " & SelectionID & " AND TaskDate = #" & Format(DailyDate, "MM/DD/YYYY") & "#"
     Debug.Print strSql
     CurrentDb.Execute strSql
  End If
End Sub

mainform code

Option Compare Database
Option Explicit

Private Sub CmboDate_AfterUpdate()
  LoadChecks
  Me.cmboDate = cmboDate
End Sub

Private Sub cmboDate_Enter()
 Dim strSql
 strSql = "SELECT DISTINCT tblPersonDailyTasks.TaskDate FROM tblPersonDailyTasks where PERSON_ID_FK = " & Me.CmboPerson & " ORDER BY TaskDate DESC"
 cmboDate.RowSource = strSql
End Sub

Private Sub CmboPerson_AfterUpdate()
  LoadChecks
End Sub
Private Sub cmdToday_Click()
  Me.cmboDate = Date
  LoadChecks
End Sub

Private Sub Form_Current()
  LoadChecks
  Me.CmboPerson = Me.CmboPerson.ItemData(0)
  Me.cmboDate = Date
End Sub

Private Sub Form_Load()
  Dim frm As Access.Form
  Dim sFrm As Access.SubForm
  Dim ctrl As Access.Control
  'set filter for each subform
  For Each ctrl In Me.Controls
    If ctrl.ControlType = acSubform Then
      Set frm = ctrl.Form
      frm.Filter = "Group = '" & ctrl.Tag & "'"
      frm.FilterOn = True
      frm.Requery
    End If
  Next ctrl
  Me.CmboPerson = Me.CmboPerson.ItemData(0)
  Me.cmboDate = Date
  LoadChecks
End Sub
Public Sub LoadChecks()
  Dim strSql As String
  Dim rs As DAO.Recordset
  'First uncheck everything in tblSelections
  strSql = "Update tblTaskSelections Set Selected = FALSE"
  CurrentDb.Execute strSql
  'Add the users selections
  strSql = "Select * from tblPersonDailyTasks where Person_ID_FK = " & Me.CmboPerson & " AND TaskDate = #" & Format(Me.cmboDate, "MM/DD/YYYY") & "#"
  Set rs = CurrentDb.OpenRecordset(strSql)
  Do While Not rs.EOF
    strSql = "Update tblTaskSelections set Selected = true where TaskSelection_ID = " & rs!TaskSelection_ID_FK
    CurrentDb.Execute strSql
    rs.MoveNext
  Loop
  Me.Refresh
End Sub
Private Sub ClearChecks()
   Dim strSql As String
  'First uncheck everything in tblSelections
  strSql = "Update tblTaskSelections Set Selected = FALSE"
  CurrentDb.Execute strSql
End Sub

 

[MajP]

Also you never answered if they can have multiple checks in a group. Does not make sense to me to do more than one. If they bike 12 minutes no need to save that they did less than that as well. If you want only one that is a pretty simple code.

If you want only one per group use this in subform instead

Private Sub Selected_Click()
  If Selected Then
    AddSelection
  Else
    RemoveSelection
  End If
End Sub
Public Sub AddSelection()
   Dim strSql As String
   Dim DailyDate As Date
   Dim SelectionID As Long
   Dim PersonID As Long
   ClearGroup
   If IsNull(Me.Parent.CmboPerson) Or IsNull(Me.Parent.cmboDate) Then
     MsgBox "Ensure a date and person are selected", vbInformation, "Ensure Data"
   Else
    PersonID = Me.Parent.CmboPerson
    SelectionID = Me.TaskSelection_ID
    DailyDate = Me.Parent.cmboDate
    strSql = "Insert into tblPersonDailyTasks (Person_ID_FK, TaskSelection_ID_FK, TaskDate) VALUES (" & PersonID & ", " & SelectionID & ", #" & Format(DailyDate, "MM/DD/YYYY") & "#)"
   ' Debug.Print strSql
    CurrentDb.Execute strSql
  End If
End Sub
Public Sub RemoveSelection()
   Dim strSql As String
   Dim DailyDate As Date
   Dim SelectionID As Long
   Dim PersonID As Long
   If IsNull(Me.Parent.CmboPerson) Or IsNull(Me.Parent.cmboDate) Then
     MsgBox "Ensure a date and person are selected", vbInformation, "Ensure Data"
   Else
     PersonID = Me.Parent.CmboPerson
     SelectionID = Me.TaskSelection_ID
     DailyDate = Me.Parent.cmboDate
     strSql = "Delete * from tblPersonDailyTasks WHERE Person_ID_FK = " & PersonID & " AND TaskSelection_ID_FK = " & SelectionID & " AND TaskDate = #" & Format(DailyDate, "MM/DD/YYYY") & "#"
     Debug.Print strSql
     CurrentDb.Execute strSql
  End If
End Sub
Private Sub ClearGroup()
  Dim strSql As String
  strSql = "UPDATE TblTaskSelections SET Selected = False WHERE TaskSelection_ID <> " & Me.TaskSelection_ID & " AND [Group] = '" & Me.Group & "'"
  'Debug.Print strSql
  CurrentDb.Execute strSql
  Me.Refresh
End Sub

 

[Bettany]

Subform code

Private Sub Selected_Click()
  If Selected Then
    AddSelection
  Else
    RemoveSelection
  End If
End Sub
Public Sub AddSelection()
   Dim strSql As String
   Dim DailyDate As Date
   Dim SelectionID As Long
   Dim PersonID As Long
   If IsNull(Me.Parent.CmboPerson) Or IsNull(Me.Parent.cmboDate) Then
     MsgBox "Ensure a date and person are selected", vbInformation, "Ensure Data"
   Else
    PersonID = Me.Parent.CmboPerson
    SelectionID = Me.TaskSelection_ID
    DailyDate = Me.Parent.cmboDate
    strSql = "Insert into tblPersonDailyTasks (Person_ID_FK, TaskSelection_ID_FK, TaskDate) VALUES (" & PersonID & ", " & SelectionID & ", #" & Format(DailyDate, "MM/DD/YYYY") & "#)"
    Debug.Print strSql
    CurrentDb.Execute strSql
  End If
End Sub
Public Sub RemoveSelection()
   Dim strSql As String
   Dim DailyDate As Date
   Dim SelectionID As Long
   Dim PersonID As Long
   If IsNull(Me.Parent.CmboPerson) Or IsNull(Me.Parent.cmboDate) Then
     MsgBox "Ensure a date and person are selected", vbInformation, "Ensure Data"
   Else
     PersonID = Me.Parent.CmboPerson
     SelectionID = Me.TaskSelection_ID
     DailyDate = Me.Parent.cmboDate
     strSql = "Delete * from tblPersonDailyTasks WHERE Person_ID_FK = " & PersonID & " AND TaskSelection_ID_FK = " & SelectionID & " AND TaskDate = #" & Format(DailyDate, "MM/DD/YYYY") & "#"
     Debug.Print strSql
     CurrentDb.Execute strSql
  End If
End Sub

mainform code

Option Compare Database
Option Explicit

Private Sub CmboDate_AfterUpdate()
  LoadChecks
  Me.cmboDate = cmboDate
End Sub

Private Sub cmboDate_Enter()
Dim strSql
strSql = "SELECT DISTINCT tblPersonDailyTasks.TaskDate FROM tblPersonDailyTasks where PERSON_ID_FK = " & Me.CmboPerson & " ORDER BY TaskDate DESC"
cmboDate.RowSource = strSql
End Sub

Private Sub CmboPerson_AfterUpdate()
  LoadChecks
End Sub
Private Sub cmdToday_Click()
  Me.cmboDate = Date
  LoadChecks
End Sub

Private Sub Form_Current()
  LoadChecks
  Me.CmboPerson = Me.CmboPerson.ItemData(0)
  Me.cmboDate = Date
End Sub

Private Sub Form_Load()
  Dim frm As Access.Form
  Dim sFrm As Access.SubForm
  Dim ctrl As Access.Control
  'set filter for each subform
  For Each ctrl In Me.Controls
    If ctrl.ControlType = acSubform Then
      Set frm = ctrl.Form
      frm.Filter = "Group = '" & ctrl.Tag & "'"
      frm.FilterOn = True
      frm.Requery
    End If
  Next ctrl
  Me.CmboPerson = Me.CmboPerson.ItemData(0)
  Me.cmboDate = Date
  LoadChecks
End Sub
Public Sub LoadChecks()
  Dim strSql As String
  Dim rs As DAO.Recordset
  'First uncheck everything in tblSelections
  strSql = "Update tblTaskSelections Set Selected = FALSE"
  CurrentDb.Execute strSql
  'Add the users selections
  strSql = "Select * from tblPersonDailyTasks where Person_ID_FK = " & Me.CmboPerson & " AND TaskDate = #" & Format(Me.cmboDate, "MM/DD/YYYY") & "#"
  Set rs = CurrentDb.OpenRecordset(strSql)
  Do While Not rs.EOF
    strSql = "Update tblTaskSelections set Selected = true where TaskSelection_ID = " & rs!TaskSelection_ID_FK
    CurrentDb.Execute strSql
    rs.MoveNext
  Loop
  Me.Refresh
End Sub
Private Sub ClearChecks()
   Dim strSql As String
  'First uncheck everything in tblSelections
  strSql = "Update tblTaskSelections Set Selected = FALSE"
  CurrentDb.Execute strSql
End Sub

Wow, I cannot thank you enough for this. I'm really floored by the generosity of this community, and that people go to these lengths to help others. I know this was a lot of work and I'm extremely grateful! I'm sorry about the delay in replying, dealing with sick family members and trying to help out so things have been extremely hectic.

 

[MajP]

No problem. A lot of us here have done it before or have a library of code to reuse, so not as much work as it looks.
However, I have to caution you here. You have created a nice user interface, but it is not the traditional out of the box approach and is more complicated than. So you might have trouble changing this. However, it is pretty well designed. If you want to add more groups, change the text or add choices that should no problem. To add just fill in the table just like was done. Add an additional subform control and tag the control with the proper group name.

However, with this table design you will be able to make reports with much more detail of tasks accomplished. So detailed daily logs with numbers, and breakouts of tasks will be easy to do. Good luck.

 

[Bettany]

Hi Bettany, and a belated Welcome to AWF! This is a project I've been working on for a number of years. Your question has spurred me on to advance this project, and I've taken the opportunity to make some improvements, made it more generic... I'm currently constructing a sample based on your post. If you could indicate the information you want to extract, then I can demonstrate how you can utilise the data collected.

Thanks so much for your time and effort on this, I really appreciate it. Did you have to use any code to make that form?

 

[Uncle Gizmo]

Thanks so much for your time and effort on this, I really appreciate it. Did you have to use any code to make that form?

Good morning Bettany. My hobby is converting complicated code into simple drop-in components that anybody can use. The "Nifty Multiple Checklist" does indeed include VBA code, however, so that it meets my goal of making it a drop-in component I am making the VBA "hidden", in the sense that it's not necessary for the user to modify or interact with the VBA code. If it is necessary for the user to to modify the VBA code, then I create simple one line changes here and there, and provide a video explanation of the process. I'm not sure when I will have time to finish this process as I have work commitments, and this is basically a hobby, although I hope to make it the business someday...

I have attached a version which answers your specific question, however I haven't yet been able to make it user-friendly for want of a better description. But it might get you going in the right direction...

A Look at the Multiple Checklist Code - Nifty Access

Download Sample HERE:-

Nifty Multiple Checklists - MS Access

This is I believe, a unique system for displaying Checklists - (check boxes), on an MS Access form.The original development of this code was for check-lists, however I was able to add more functionality by adding text boxes (questionnaire) and combo boxes ( multi-choice).There's a free version...

gumroad.com

 

[MajP]

That seems pretty complicated. If you take advantage of the form filter approach on the subforms, I think you would save a lot of code. This was the entire code to set the form up, and does not change regardless of the amount of subforms. No need to create sql strings or recordsets. Just tag each subform control with the group to filter and let the subforms do the work.

Private Sub Form_Load()
Dim frm As Access.Form
Dim sFrm As Access.SubForm
Dim ctrl As Access.Control
'set filter for each subform
For Each ctrl In Me.Controls
If ctrl.ControlType = acSubform Then
Set frm = ctrl.Form
frm.Filter = "Group = '" & ctrl.Tag & "'"
frm.FilterOn = True
frm.Requery
End If
Next ctrl
Me.CmboPerson = Me.CmboPerson.ItemData(0)
Me.cmboDate = Date
LoadChecks
End Sub

 

[Uncle Gizmo]

That seems pretty complicated.

I'm assuming you're referring to my code. Actually I can't see as it's much different from your version, other than we are both using completely different approaches. I think we have different experiences and different goals. My goal is to produce a drop-in component that anyone can use in their database, a system that is easy to setup and use in the creation of multiple checklists.

So far, I've failed to attain this perfection, but this interaction with Bettany has focused my attention on it again and I think I'm getting somewhere with it. However I'm not quite ready to post my latest version, but it will be very "simple" in the sense that people who have a disdain for using VBA should be able to set it up easily. Your observation that the code is more complicated than yours is probably correct, your assumption that it is unnecessarily more complicated from the basic functionality point of view, again correct, but to achieve my goal of a drop-in component, I think it's about right.

I'm being a bit long winded because last time I said it was "simple" you took umbrage with statement, so I thought I would be perfectly clear, so as to avoid any conflict...