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
284 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 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

737 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