Solved

Check to make sure all data is filled in subform before allowing to close.  Need ignore check if there are no records in subform.

Posted on 2009-05-04
5
282 Views
Last Modified: 2013-11-28
I have built an Action Item database that has a form where you can close out the Action Item by putting a date complete.  Before you are allowed to put in the Action Item date complete, I have the code below to check to make sure all subtasks are completed.  If they are not completed you get a message box telling you to close out all subtasks before closing out the Action Item.  

The problem  is that if I have a subform that does not have any records (i.e., there are no subtask assigned to the Action Item), I need to ignore the code (i.e., do not check to make sure all subtask are closed) and allow a date to be entered into the Aciton Item completed field.

Currently I get an error becasue the subtasks (subform) are not all closed becasue there are no records in the subform.

Below is the code I currently have in my form.  Thanks in advance for the help.

Carl

__________ Code in Mainform's Selection Change - Event Property Sheet _____
________________________________________________________________
 
Private Sub Form_SelectionChange()
 
Function AllCompleted() As Boolean
AllCompleted = True
 
With Me.[Subtask - Subform - Continous Form (2)].Form.RecordsetClone
     .MoveFirst
      Do Until .EOF
          If VarType(![Actual Complete Date]) <> 7 Then
             AllCompleted = False
             Exit Function
          End If
 
        .MoveNext
      Loop
End With
End Function
 
______________________________________________________________________
___ Code in Mainform's text box for completed date - Before Update Property Sheet ____
_______________________________________________________________________
 
Private Sub Actual_Complete_Date_BeforeUpdate(Cancel As Integer)
 
 If Me.[Actual Complete Date] = "" Or IsNull([Actual Complete Date]) Then
    Me.Completed = False
    Exit Sub
    End If
 
Me.Completed = True
 
If Not AllCompleted Then
MsgBox "There are Subtasks that are not completed.  All Subtasks must be completed before an Action Item can be completed."
Cancel = True
Exit Sub
End If
 
End Sub

Open in new window

0
Comment
Question by:csall
5 Comments
 
LVL 84
ID: 24300805
You might consider simply opening a Recordset that checks whether or not your tasks are complete:

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

rst.Open "SELECT * FROM YourTable WHERE YourRelatedField=" & Me.YourIDField & " AND [Actual complete Date] IS NULL", CurrentProject.Connection

If Not (rst.EOF and Rst.BOF) Then
  '/there are still records for the task that are not completed
Else
  '/all tasks are completed
End If

You'd have to change YourRelatedField and me.YourIDField to match your project, of course ... YourRelatedField would be the field in your "Tasks" table that associate a Task with it's Parent record ...
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24300810


Function AllCompleted() As Boolean
AllCompleted = True

'Add these lines
if Me.[Subtask - Subform - Continous Form (2)].Form.Recordset.eof then
  AllCompleted = True
  exit function
end if


With Me.[Subtask - Subform - Continous Form (2)].Form.RecordsetClone
     .MoveFirst
      Do Until .EOF
          If VarType(![Actual Complete Date]) <> 7 Then
             AllCompleted = False
             Exit Function
          End If
 
        .MoveNext
      Loop
End With
End Function
 
0
 
LVL 4

Accepted Solution

by:
jruhe earned 500 total points
ID: 24300922
wrap the code in this:

if Me.[Subtask - Subform - Continous Form (2)].Form.RecordsetClone.RecordCount>0

(your code)

end if

JR
0
 

Author Closing Comment

by:csall
ID: 31577837
Very simple but affective.  Thank you.

This was my final code.

Private Sub Form_SelectionChange()
 
Function AllCompleted() As Boolean
AllCompleted = True
 If Me.[Subtask - Subform - Continous Form Then(2)].Form.RecordsetClone.RecordCount>0
With Me.[Subtask - Subform - Continous Form (2)].Form.RecordsetClone
     .MoveFirst
      Do Until .EOF
          If VarType(![Actual Complete Date]) <> 7 Then
             AllCompleted = False
             Exit Function
          End If
 
        .MoveNext
      Loop
End With
End If
End Function
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24321658
csall,

did you even try the codes i posted?
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question