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?

 

[Uncle Gizmo]

I think you will find the blog on my website about creating checklists of interest:-

Add a Check List to your MS Access Database – Nifty Access

www.niftyaccess.com

 

[Uncle Gizmo]

I did another blog, this one about multiple checklists here:-

Add a Multiple Check List to your MS Access Database – Nifty Access

www.niftyaccess.com

But be aware, it's based on the single checklist Blog. In other words you need to understand how to create the single checklist before you try and create multiple checklists. Absorb the single checklist blog before you delve into the multiple checklist blog.

 

[Uncle Gizmo]

Here I show user case examples where I applied the ideas from the two blogs above:-

Multiple Checklist User Case Examples - Nifty Access

Multiple Checklist User Case Examples I have set this page up to record user case examples using the the Nifty Multiple Checklist System. Demonstration of another product from Nifty Access... The Nifty Multiple Check-List System. Create a Form with Many Check-Boxes Create a Form with Many...

www.niftyaccess.com

 

[Uncle Gizmo]

And finally!

Here are some links to Access World Forum threads about checklists:-

Checklist Discussion – Nifty Access

www.niftyaccess.com

 

[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.

 

[Pat Hartman]

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.

 

[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?