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
275 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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
ID: 24321658
csall,

did you even try the codes i posted?
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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, when working with VBA, learn some techniques for writing readable and easily maintained code.

920 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now