Looking for a design suggestion...

  • Thread starter Thread starter pfm721
  • Start date Start date
P

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

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

Patrick

Klatuu said:
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


pfm721 said:
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
Click to expand...
Click to expand...
 
Glad I could help.
--
Dave Hargis, Microsoft Access MVP


pfm721 said:
Dave,

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

Patrick

Klatuu said:
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


pfm721 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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
 
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

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


pfm721 said:
Dave,

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

Patrick

Klatuu said:
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
Click to expand...
Click to expand...
Click to expand...
 
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
 
Jeff Boyce said:
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
Click to expand...

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...
 
Back
Top