Prev: Setting an autonumber back to zero
Next: How to user Dataset created by Visual Studio 2008
From: pfm721 on 2 Jul 2008 15:53
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

From: Klatuu on 2 Jul 2008 17:34
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" wrote:

> 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
>
From: pfm721 on 2 Jul 2008 17:36
Dave,

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

Patrick

"Klatuu" wrote:

> 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" wrote:
>
> > 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
> >
From: Klatuu on 2 Jul 2008 17:41
Glad I could help.
--
Dave Hargis, Microsoft Access MVP


"pfm721" wrote:

> Dave,
>
> As always you were spot on! Thanks for the quick reply it worked great.
>
> Patrick
>
> "Klatuu" wrote:
>
> > 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" wrote:
> >
> > > 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
> > >
From: Mark on 2 Jul 2008 21:10
Why do you even need the checkbox? Just check all the appointment completed
dates and if none are null, the project is complete.

Steve


"pfm721" <pfm721(a)discussions.microsoft.com> wrote in message
news:FAB52CAA-DF73-4D76-917E-695B230E2317(a)microsoft.com...
>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
>


 |  Next  |  Last
Pages: 1 2
Prev: Setting an autonumber back to zero
Next: How to user Dataset created by Visual Studio 2008