Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
297 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 85
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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

772 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