Checklist How-to

[cable]

How-to: Checklist display/storage

I have a fixed checklist of steps, I want to store which of these steps have been completed. However on the form view I need to see all of the checklist steps.

I'll have my tblCheckListSteps and a link table with id's for the main id and which checklist items have been selected.

Which I think is the best way to store this information. But I can't think of how to display it. I suppose I could build the form dynamically or is there a checkbox listbox control in Acc97?

Any ideas?

 

[The_Doc_Man]

This sounds (to me) like a prime case for parent-child tables and parent-child forms.

Look up sub-forms in Help. You have what appears to be a many-to-one situation. Access handles THIS one in its sleep.

 

[Fizzio]

If I read you correctly, you want to display all steps and then allow the user to check off which of them have been completed.

This is actually quite difficult as a typical form/subform will only show records that you add, not records that are added as default and then shown.

How I have got around this is to keep the same structure - ie three tables eg

tblJob
------
JobID
JobNumber
StaffID
etc

tblCheckList
------------
CheckListID
ChecklistDescription

tblJobTasks
---------------
JobID
CheckListID
TaskCompleted

When a new job is created, I programically (using a recordset) add all the tasks from tblChecklist to the tblJobTasks and display them on a subform. The user then ticks off the task as it is completed.

I'm sure that this is not good design but I have yet to fathom out a better way of doing it.

 

[cable]

I did think of doing that...but as you say it seems inefficent.
I'm looking at using the listview control which can have checkboxes.

The idea being:
1) Fill the listbox from the tblChecklist
2) User ticks whatever boxes they want
3) Save the ticked items into the tblJobTasks (but you don't need the completed flag)

Obviously after 1, you'll need to loop through tbljobtasks and then tick the boxes in the checklist.

I took ages to find the checked list though...didn't think to try the listview.

 

[cable]

I think this is gonna work...bit hard to write the code though as intellisense is dead for the listview.

 

[Fizzio]

I'd like to see it working if you get the listview control bit sorted. Can you post if you get it working with the listview?

 

[cable]

Hopefully the attachement works! Just run form1 pick one and press the button.

If not then:

You need the reference to MS Common Controls (I used 6) and obviously a ListView with the checkboxes property set, and using report view (this is to make sure you can resize the columns so that the text fits).

You've got the following code to fill the listview and to check the saved boxes

'process open args
If IsNull(Me.OpenArgs) Or Me.OpenArgs = "" Then
    DoCmd.close
Else
    Me.txtPromoID = Me.OpenArgs
End If

Dim myDB As DAO.DATABASE
Dim myRS As DAO.Recordset
Dim CLRS As DAO.Recordset
Dim itmX As ListItem
Dim colX As ColumnHeader

Set myDB = CurrentDb
Set myRS = myDB.OpenRecordset("SELECT * FROM tblChecklists;")
Set CLRS = myDB.OpenRecordset("SELECT * FROM tblPromoCheckList WHERE [PromoID]=" & txtPromoID & ";")

'setup column
Set colX = lstCheckList.ColumnHeaders.Add(, , "CheckList", lstCheckList.Width)

Do Until myRS.EOF
    'add checklist items, using the key to store the ID
    'for some odd reason the key HAS to be an string, cstr(#) don't work either!
    Set itmX = lstCheckList.ListItems.Add(, "X" & myRS!CheckListID, myRS!CheckList)
    
    'is this item in the list for this promo (ie should we tick this item?)
    CLRS.FindFirst "[CheckListID]=" & myRS!CheckListID
    If CLRS.NoMatch = False Then itmX.Checked = True Else itmX.Checked = False
    
    myRS.MoveNext
Loop

then this code saves the state of the boxes:

Dim itmX As ListItem
Dim myDB As DAO.DATABASE
Dim myRS As DAO.Recordset

Set myDB = CurrentDb
'delete currently items
myDB.Execute "DELETE * FROM tblPromoCheckList WHERE [PromoID]=" & txtPromoID & ";"

'loop through the items adding to the table if they are checked
Set myRS = myDB.OpenRecordset("SELECT * FROM tblPromoCheckList WHERE [PromoID]=" & txtPromoID & ";")

For Each itmX In lstCheckList.ListItems
    If itmX.Checked Then
        myRS.addnew
        myRS!PromoID = txtPromoID
        myRS!CheckListID = Right(itmX.Key, Len(itmX.Key) - 1)
        myRS.Update
    End If
Next itmX

'close
DoCmd.close

Forgot to mention the tables needed for this to work:

tblChecklist (this has the items that appear on the checklist)
-----------
CheckListID
CheckList (text)
(anything else you want...maybe start and end dates?)

tblXXXXXX (this the table that you want to associate the checklist with)
----------
MainID
etc

tblXXXChecklist (this joins the checklist and your main table)
---------------
MainID
CheckListID

Obviously that last table only stores which items are ticked for that MainID

 

[cable]

arse, attachement fails...damn unintelligent security software.]

I'll have to upload the example from home tonight.

Try This

 

[Fizzio]

Thanks Cable,

Works well and a good alternative to forcing data into a table unneccessarily. My only concern is with the ActiveX control. I purposefully avoid them due to known problems (or maybe I am reading too many heresay testimonies)