Update textbox after looping through table

[bar891]

I have a project with a task table and a task datasheet view. The task status column is a combo box with "Not Started", "In Progress", "Deferred", "Completed" in which the user changes as the tasks are completed.
I also have a Project Form that has a Status text box on it.
I need to be able to loop through the task status column and see if all the tasks are completed and then update the Project status text box with "Completed" or if all the tasks are not completed then to update the Project status with "Not Completed".
I just don't know what or where the code should be. I have tried many combinations including macro but i am not experienced enough to work it out. Can someone please help me?

It is a basic Project Management setup with a Project Table, Tasks Table, Employee Table. It has a Project form that has subforms of Tasks and Employees Assigned to it. You can have many tasks to a single project that have to be completed by a certain date. It has a summary page that lists all the projects that are current and the status that they are at. so i want to see on the summary page that if all the tasks are completed or not.

 

[arnelgp]

basically you can do the updating on the Task subform's after update event or current event:

private sub form_current()
dim strTaskStatus as string
dim rs as dao.recordset
strTaskStatus="Completed"
set rs = me.recordsetclone
with rs
if not (.bof and .eof) then .movefirst
do while not .eof
if !status <> "Completed" Then 'this is the field in your Task status
strTaskStatus = "Not Completed"
exit do
end if
.movenext
loop
.close
end with
set rs=nothing
me.parent!status = strTaskStatus
me.parent.Dirty = False
end sub

 

[bar891]

When it gets to "me.parent!status = strTaskStatus" i get "Object doesn't support this object or method"

 

[arnelgp]

then replaced it with the textbox name of your status field in the main form.

me.parent!txtboxNameOfStatus = strTaskStatus
me.parent.dirty = False

 

[bar891]

now i get "You can't assign a value to this object"

 

[arnelgp]

what is the controlsource of the textbox?
to make it short... post your db, and ill fix it.

 

[bar891]

will this work if i have some tasks completed and some not completed?

 

[arnelgp]

if some task are not completed the for a project, status is Not Completed. if all task is completed for that Project, status will be "Completed". thats what the code will do.

 

[bar891]

the control source of the form is the Project Table "ProjectStatus"
the form name is Project Details
the textbox name is ProjectStatus
this is what i have:-

Private Sub Form_Load()
Dim strTaskStatus As String
Dim rs As dao.Recordset
strTaskStatus = "Completed"
Set rs = Me.RecordsetClone
With rs
If Not (.BOF And .EOF) Then .MoveFirst
Do While Not .EOF
If !Status <> "Completed" Then 'this is the field in your Task status
strTaskStatus = "Not Completed"
Exit Do
End If
.MoveNext
Loop
.Close
End With
Set rs = Nothing
Me.Parent!ProjectStatus = strTaskStatus
Me.Parent!Dirty = False
End Sub

 

[arnelgp]

you should put the code to the Task SUBFORM. not on the main form.
and use CURRENT event of the subform.

 

[bar891]

The code is in the Tasks Subform & is now under Current.
I still have the same problem "You can't assign a value to this object"

 

[bar891]

Is it because the form isn't open yet?

 

[arnelgp]

ok i got it. the main form's control are not yet initialized when subform loads.
put On Error Resume Next as the first statement on the subform's current event.

 

[bar891]

OK working now, That did the trick
Many Thanks for your help

 

[stopher]

Any reason why you are not using DCount for example?

Something like:

DCount("taskID","qryProjectTasks","projectID=" & forms!myForm!projectID & " AND status<>'Completed')

Wrap the above in an iif statement e.g.

IIF(DCount("taskID","qryProjectTasks","projectID=" & forms!myForm!projectID & " AND status<>'Completed')>0, "Not Completed","Completed")

Using recordsets here just seems like overkill.