Making a custom checklist that saves to a table

[The Rev]

Good evening all! I have a need to build a checklist form. We have 19 steps that our leads have to do every engagement. Items 1-4 are pre-engagement, 5- 8 are mid-engagement, and 9- 19 are post-engagememt. I need a checklist form that saves checked items for the next time the DB is opened and I need the checklist line items broken up into the 3 areas with headers over each area. I need the checklist to look like this:

Pre-Engagement
■ Checklist item 1
■ Checklist item 2
■ Checklist item 3
■ Checklist item 4

Mid-Engagement
■ Checklist item 5
■ Checklist item 6
■ Checklist item 7
■ Checklist item 8

Post-Engagement
■ Checklist item 9

Etc....

Any ideas on how to do this where the lead checks items 1-3, closes the form, and reopens it to 1-3 still checked?

 

[MajP]

See solution.

In the subform you need code to write to a table when you add or remove a selection

Private Sub Selected_Click()
  If Selected Then
    AddSelection
  Else
    RemoveSelection
  End If
End Sub

Public Sub AddSelection()
   Dim strSql As String
   Dim UserID As Long
   Dim SelectionID As Long
   UserID = Me.Parent.UserID
   SelectionID = Me.SelectionID
   strSql = "Insert into tblUserSelections (UserID_FK, SelectionID_FK) VALUES (" & UserID & ", " & SelectionID & ")"
   CurrentDb.Execute strSql
End Sub
Public Sub RemoveSelection()
   Dim strSql As String
   Dim UserID As Long
   Dim SelectionID As Long
   UserID = Me.Parent.UserID
   SelectionID = Me.SelectionID
   strSql = "Delete * from tblUserSelections WHERE USERID_FK = " & UserID & " AND SelectionID_FK = " & SelectionID
   CurrentDb.Execute strSql
End Sub

On the main form you need to load those previous selections

Public Sub LoadChecks()
  Dim strSql As String
  Dim rs As DAO.Recordset
  'First uncheck everything in tblSelections
  strSql = "Update tblSelections Set Selected = FALSE"
  CurrentDb.Execute strSql
  'Add the users selections
  strSql = "Select * from tblEngagementSelections where UserID_FK = " & Me.UserID
  Set rs = CurrentDb.OpenRecordset(strSql)
  Do While Not rs.EOF
    strSql = "Update tblSelections set Selected = true where SelectionID = " & rs!SelectionID_FK
    CurrentDb.Execute strSql
    rs.MoveNext
  Loop
  Me.Refresh
End Sub

No matter how you design it using an unbound form, an option group, or a subform the methods are similar. I saved myself a little work by using the same subform 3 times. I just changed the recordsource on the subforms.

 

[MajP]

I'm going to make a simple change to the spec since I am the user and users are notoriously unreliable regarding certain details Please add a 5th pre-engagement step.

Oh, and I also need to break mid-engagement into two parts. Sorry.

Since I designed it well, those type of changes would be trivial to implement without any code change. And your point?