VBA Code doesn't review all subform records

[csh2013]

I'm having some problems with VBA code I'm writing that references a subform. I'm pretty new to VBA and am not sure what the problem could be. The parent form (TrackingForm) tracks dates different tasks were completed. The subform (Child2) tracks exceptions that also need to be marked completed. I need ALL dates to be marked as completed before the entire tracking record can be marked as completed.

I have the following code entered into the OnFocus event of the tracking records Me.Completed_Date field:

Private Sub Completed_Date_GotFocus()​

If IsNull(Closing_Date) Or IsNull(Package_Received) _
Or IsNull(Upload_By) Or IsNull(Initial_Review_Date) _
Or IsNull(Me.Child2.Form![Date Exception Completed]) Then
Me.Completed_Date.Locked = True
MsgBox "Completed Date cannot be entered - outstanding items.", vbOKOnly, "Warning!"
Else: Me.Completed_Date.Locked = False
End If
End Sub​

What ends up happening is that, if there is more than one exception record on Child2, the code only seems to care if one of the records (usually the first record) has something entered in the [Date Exception Completed] field. I can't seem to make it look at any successive records in Child2 to ensure they are also completed prior to unlocking the Me.Date_Completed field. I have googled this extensively over the last day and the only response that seems to recur involves using a Recordset function that I have never used and am unsure how to. Can anyone shed any light? I would be extremely grateful! THANKS!

 

[MikeLeBen]

this is happening because the IsNull condition will always be false as long as even only one record is not null.

I think you would have to open a recordset via VBA and test the condition on all records, if you are going to have more than one to check.

If you are not familiar with recordsets, I strongly recommend giving a read to Allen Browne's website, specifically here: http://allenbrowne.com/ser-29.html

 

[jdraw]

You did say -- any light, so I'll repeat back what I'm reading just to clarify.

You have a number of Tasks, each with a number of Conditions.
If any Condition is not Satisfied/Not Completed, then the Task can not be marked Completed.

So generally you have a tblTask and a tblCondition in a 1 : M relationship

So Task1 may have Conditions 1,2,3,4 where - 4 is not satisfied.
So Task1 can not be marked completed because of outstanding condition 4.

You have set these up in Form(Task)/sub form(Condition) where the linkfield is TaskID.

Have you tried using some debug.print statements to see what values are really in the data?
For testing, you may want to remove the "locked" to verify the data.

Just some thoughts.

 

[DavidAtWork]

You could try using DCount on a query that retrieves all the exceptions that are not completed for that parent record and if > 0 then lock the completed date field.
You can then use this as before but replace the part
Or IsNull(Me.Child2.Form![Date Exception Completed])
with DCount("*", "qryGetExceptions") > 0 Then
Me.Completed_Date.Locked = True
Else
Me.Completed_Date.Locked = False
End If

You'd have to create your query with the criteria in the [Date Exception Completed] field IsNull and use the wizard to set the parentID of the record to ID on the Tracking form

Hope that makes sense

David

 

[csh2013]

All -

Thank you so much for your assistance. Mike, apparently I still have a way to go with learning about Recordsets. I attempted working with that function initially after your response and was having no success, but I will certainly do further research so I can use it effectively going forward. jdraw and David, I used your suggestions in conjunction with one another. I already had a query for the Exceptions that weren't completed because it was necessary for a report AND I already had a uniquely identifying field that is applied automatically for each record and its corresponding exceptions. So, the DCount seemed to me the easiest way to accomplish my goal at my current skill level in working with VBA. I debugged the code so I knew what results it was calculating off of and made adjustments as necessary. End result, which seems to be working, is this:


Private Sub Completed_Date_GotFocus()
Dim I As Integer
I = DCount("*", "Exceptions Query", "[ID Number] ='" & Forms![TrackingForm]![ID Hidden Field] & "'")
If I > 0 Then
Me.Completed_Date.Locked = True
MsgBox "Record cannot be marked complete - outstanding items.", vbOKOnly, "Warning!"
Else:
If IsNull(Closing_Date) Or IsNull(Package_Received) _
Or IsNull(Upload_By) Or IsNull(Initial_Review_Date) Then Me.Completed_Date.Locked = True
MsgBox "Record cannot be marked complete - oustanding items.", vbOKOnly, "Warning!"
Else
Me.Completed_Date.Locked = False
End If
End If
End Sub

It's probably not written in the most eloquent fashion on the planet, but, until I learn more, all I'm looking for is functional. Thanks again, all!