A message box is required if all tasks completed

[aymanmb]

Hi,

I have a form with a field "EndDate" and a subform holding multiple tasks and each of these tasks have a checkbox field "Completed".

I want to write a code that will prompt the user to enter "EndDate" in the main form when all tasks in the subform has been checked (Completed).

appreciate any tips

 

[Uncle Gizmo]

One thing you could try is some code to count the number of check boxes you have something like this:

Dim ctlChk As Control
Dim intCounter as integer

    For Each ctlChk In Me.Controls
        If ctlChk.ControlType = acCheckBox Then     'Find check boxes only
         intCounter = intCounter + 1
    Next ctlChk

Modify the above code so that instead of counting the check boxes it looks to see if the value of the check boxes are true, you could have a counter called intCountTrue, then just check the value of the intCounter against the value of intCountTrue, when they are equal all of your check boxes are checked and you can issue your message!

I would guess the best place to call this code from would be the afterupdate event of each check box.

 

[aymanmb]

thanks, nice idea. I did as per your advise but no success. the code was:

Private Sub Completed_AfterUpdate()
Dim ctlChk As Control
Dim ctlChkT As Control
Dim intCounter As Integer
Dim intCounterTrue As Integer

For Each ctlChk In Me.Controls
If ctlChk.ControlType = acCheckBox Then 'Find check boxes only
intCounter = intCounter + 1
End If
Next ctlChk

For Each ctlChkT In Me.Controls
If ctlChkT.ControlType = acCheckBox = True Then 'Find check boxes only
intCounterTrue = intCounterTrue + 1
End If
Next ctlChkT
If intCounter = intCounterTrue Then
MsgBox ("all tasks are completed, please enter the EndDate above")
End If

End Sub

what happens is that the message box appears after each task checkboc is checked regardless of whether all tasks are checked.

anything wrong with code above

 

[Uncle Gizmo]

It would be nice if you could do this:

If ctlChkT.ControlType = acCheckBox = True Then

but that won't work!

Change it to:

If ctlChkT.ControlType = acCheckBox Then
     If ctlChkT.Value = True Then intCounterTrue = intCounterTrue + 1
End If

 

[aymanmb]

I did, still same thing, the message box appears regardless if all tasks check or not. hereis the updated code:

Private Sub Completed_AfterUpdate()
Dim ctlChk As Control
Dim ctlChkT As Control
Dim intCounter As Integer
Dim intCounterTrue As Integer

For Each ctlChk In Me.Controls
If ctlChk.ControlType = acCheckBox Then 'Find check boxes only
intCounter = intCounter + 1
End If
Next ctlChk

For Each ctlChkT In Me.Controls
If ctlChkT.ControlType = acCheckBox Then 'Find check boxes checked only
If ctlChkT.Value = True Then
intCounterTrue = intCounterTrue + 1
End If
End If
Next ctlChkT
If intCounter = intCounterTrue Then
MsgBox ("all tasks are completed, please enter the EndDate above")
End If

End Sub

 

[boblarson]

Try this instead:

Dim ctl As Control
Dim intFalse As Integer
For Each ctl In Me.Controls
    If ctl.ControlType = acCheckBox Then
        If ctl = False Then
            intFalse = intFalse + 1
        End If
    End If
Next ctl

If intFalse = 0 Then
    MsgBox ("all tasks are completed, please enter the EndDate above")
End If

It worked for me.

 

[aymanmb]

I tried it did not work (see attached photo)

thanks guy's for your interest to support

 

[boblarson]

So, where did you put the code? Can you post the database or email it to me? I know it worked on my test, so I'm very, very curious to find out why it won't work on yours.

 

[aymanmb]

I put it under the afterupdate event of the checkbox.

the database file compressed exceeds the forum limit (~500 KB). Can I send it to your private email.

regards

 

[boblarson]

Have you run Compact and Repair and then zipped it? It needs to be zipped and under 393Kb to post here. Let me know, otherwise I will PM you my email.

 

[aymanmb]

I always do comapct and repair, still it is bigger than the limit, please send my your email

mine is aymanmb@tedata.net.eg

 

[boblarson]

What is happening is that it is only seeing one checkbox as being there as it is the same checkbox (just different records) so we'll have to come up with another method. I may not get to it right away, so hopefully someone else might be able to. It will require iterating through the recordset, I'm pretty sure.

 

[aymanmb]

what about the structure, any mistakes, recommendations (please do the analyze and see what it recommends)

thanks

 

[boblarson]

I'll try to take a closer look this weekend and give you some feedback.

 

[KKilfoil]

1) Create a summary query that groups by the records that you have on your subform, and COUNTS them in a new field.
2) Copy this into another query (with a different name!) and add a 'where' option for 'completed=true'
3) Create another query that contains all of the relevant fields (or use the one you may already have), and add in the two count fields from (1) and (2) above. You will have to define relationships to join your new queries' fields properly. If you want, define another field to be the difference between the two counts.
4) In the AFTERUPDATE of the subform control, requery the above query and refresh your main form.
5) Compare the counts. You are done when the two counts are the same. Trigger your messagebox

 

[aymanmb]

appreciate it

 

[Uncle Gizmo]

I did, still same thing, the message box appears regardless if all tasks check or not. hereis the updated code:

Private Sub Completed_AfterUpdate()
Dim ctlChk As Control
Dim ctlChkT As Control
Dim intCounter As Integer
Dim intCounterTrue As Integer

For Each ctlChk In Me.Controls
If ctlChk.ControlType = acCheckBox Then 'Find check boxes only
intCounter = intCounter + 1
End If
Next ctlChk

For Each ctlChkT In Me.Controls
If ctlChkT.ControlType = acCheckBox Then 'Find check boxes checked only
If ctlChkT.Value = True Then
intCounterTrue = intCounterTrue + 1
End If
End If
Next ctlChkT
If intCounter = intCounterTrue Then
MsgBox ("all tasks are completed, please enter the EndDate above")
End If

End Sub

I added three check boxes to a blank form, a command button and ran the above code from the command button and it worked fine.

Cheers Tony

 

[boblarson]

Tony:

The problem lies in that it isn't just 3 checkboxes on a form. It is a single checkbox (in Datasheet mode) on a subform within multiple records.

 

[Uncle Gizmo]

Okay cheers Bob.....

 

[boblarson]

Okay - got it. Oh, I renamed your checkbox to chkCompleted too so it didn't have the same name as the field. And then I put this in the AfterUpdate event of the checkbox:

    Dim rst As ADODB.Recordset
    Dim strSQL As String
    Dim intFalse As Integer
    strSQL = "SELECT tbl_OrderDetails.OrderID, tbl_OrderDetails.Completed " & _
            "FROM tbl_OrderDetails " & _
            "WHERE (((tbl_OrderDetails.OrderID)=" & [Forms]![Frm_Orders]![sFrm_OrderDetails].[Form]![OrderID] & "));"

    Set rst = New ADODB.Recordset

    rst.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
    DoCmd.RunCommand acCmdSaveRecord

    Do Until rst.EOF
        If rst("Completed").Value = 0 Then
            intFalse = intFalse + 1
        End If
        rst.MoveNext
    Loop

    If intFalse = 0 Then
        MsgBox ("all tasks are completed, please enter the EndDate above")
    End If
rst.Close
Set rst = Nothing

As an additional suggestion I would suggest setting your keys properly though. It would appear that in the orders you don't have a primary key set and in the orders details table you don't have a primary key for that table at all (just the foreign key for the orders table).