Looking for a design suggestion...

[pfm721]

I have a database that tracks projects. One project may have several
appointments related to it. I have a check box to mark a project as being
complete. All of the appointments have a date field to enter the date the
appointment was completed. I would like to prevent the user from completing a
project unless all of the appointments have a completed date. Any ideas on
how I would go about this?

tblProject
ProjectNumber text (PK)
Completed yes/no

tblAppointment
AppointmentID autonumber (PK)
ProjectNumber text
CompletedDate date/time

Thanks

 

[Klatuu]

You could use the Before Update event of the checkbox on the form to see if
there are any appointments not completed:

Private Sub txtProjectComplete_BeforeUpdate(Cancel As Integer)
dim varIncompleteProject As Variant

varIncompleteProject = (DLookup("[AppointmentID]", "tblAppointment", _
"[ProjectNumber] = """ & Me.txtProjectNumber & _
""" And[CompletedDate] Is Null")
If Not IsNull(varIncompleteProject) Then
MsgBox "Appointment " & varIncompleteProject & " Is Not Competed"
Cancel = True
End If

 

[pfm721]

Dave,

As always you were spot on! Thanks for the quick reply it worked great.

Patrick

You could use the Before Update event of the checkbox on the form to see if
there are any appointments not completed:

Private Sub txtProjectComplete_BeforeUpdate(Cancel As Integer)
dim varIncompleteProject As Variant

varIncompleteProject = (DLookup("[AppointmentID]", "tblAppointment", _
"[ProjectNumber] = """ & Me.txtProjectNumber & _
""" And[CompletedDate] Is Null")
If Not IsNull(varIncompleteProject) Then
MsgBox "Appointment " & varIncompleteProject & " Is Not Competed"
Cancel = True
End If
--
Dave Hargis, Microsoft Access MVP

I have a database that tracks projects. One project may have several
appointments related to it. I have a check box to mark a project as being
complete. All of the appointments have a date field to enter the date the
appointment was completed. I would like to prevent the user from completing a
project unless all of the appointments have a completed date. Any ideas on
how I would go about this?

tblProject
ProjectNumber text (PK)
Completed yes/no

tblAppointment
AppointmentID autonumber (PK)
ProjectNumber text
CompletedDate date/time

Thanks

 

[Klatuu]

Glad I could help.
--
Dave Hargis, Microsoft Access MVP

Dave,

As always you were spot on! Thanks for the quick reply it worked great.

Patrick

You could use the Before Update event of the checkbox on the form to see if
there are any appointments not completed:

Private Sub txtProjectComplete_BeforeUpdate(Cancel As Integer)
dim varIncompleteProject As Variant

varIncompleteProject = (DLookup("[AppointmentID]", "tblAppointment", _
"[ProjectNumber] = """ & Me.txtProjectNumber & _
""" And[CompletedDate] Is Null")
If Not IsNull(varIncompleteProject) Then
MsgBox "Appointment " & varIncompleteProject & " Is Not Competed"
Cancel = True
End If
--
Dave Hargis, Microsoft Access MVP

I have a database that tracks projects. One project may have several
appointments related to it. I have a check box to mark a project as being
complete. All of the appointments have a date field to enter the date the
appointment was completed. I would like to prevent the user from completing a
project unless all of the appointments have a completed date. Any ideas on
how I would go about this?

tblProject
ProjectNumber text (PK)
Completed yes/no

tblAppointment
AppointmentID autonumber (PK)
ProjectNumber text
CompletedDate date/time

Thanks

 

[John... Visio MVP]

I guess you like to show your ineptitude. There may be other criteria for
marking a project as complete. The OP is only asking about making sure all
appointments have a completion date. If all the completion dates are
entered, it does not mean that the project is complete.

John... Visio MVP

 

[pfm721]

Dave

I don't know if you are watching this thread still or not but I'm having a
problem with the code.

As posted I'm getting error 2001 you canceled the previous operation.

code looks like this

Private Sub Complete_BeforeUpdate(Cancel As Integer)
Dim varIncompleteProject As Variant
varIncompleteProject = (DLookup("[AppID]", "tblAppointment", _
"[ConsultNumber]= """ & Me.ConsultNumber & _
""" And[CompletedDate] Is Null"))
If Not IsNull(varIncompleteProject) Then
MsgBox "Appointment " & varIncompleteProject & " Is Not Competed"
Cancel = True
End If

End Sub

Glad I could help.
--
Dave Hargis, Microsoft Access MVP

Dave,

As always you were spot on! Thanks for the quick reply it worked great.

Patrick

You could use the Before Update event of the checkbox on the form to see if
there are any appointments not completed:

Private Sub txtProjectComplete_BeforeUpdate(Cancel As Integer)
dim varIncompleteProject As Variant

varIncompleteProject = (DLookup("[AppointmentID]", "tblAppointment", _
"[ProjectNumber] = """ & Me.txtProjectNumber & _
""" And[CompletedDate] Is Null")
If Not IsNull(varIncompleteProject) Then
MsgBox "Appointment " & varIncompleteProject & " Is Not Competed"
Cancel = True
End If
--
Dave Hargis, Microsoft Access MVP


:

I have a database that tracks projects. One project may have several
appointments related to it. I have a check box to mark a project as being
complete. All of the appointments have a date field to enter the date the
appointment was completed. I would like to prevent the user from completing a
project unless all of the appointments have a completed date. Any ideas on
how I would go about this?

tblProject
ProjectNumber text (PK)
Completed yes/no

tblAppointment
AppointmentID autonumber (PK)
ProjectNumber text
CompletedDate date/time

Thanks

 

[Jeff Boyce]

Uhmmmm, John, are you sure you want to do that?

I mean, if you educate him, won't he get better at it?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John... Visio MVP]

Uhmmmm, John, are you sure you want to do that?

I mean, if you educate him, won't he get better at it?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am not worried in the least, over the years, steve has proved he is unable
to learn.

Besides, isn't helping the reason why we hang around the newsgroups?

John...