Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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
?
293 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

715 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