Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Sep 2006
    Location
    St. Augustine, Florida, USA
    Posts
    28
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Edits during data entry (2000)

    We have an Access database that track minutes of service that our staff provides to clients. I would like to include an edit that insures that start time + minutes does not overlap with another service event for that staff on the same day. Currently each service event is captured on a separate record. All suggestions welcome!

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Edits during data entry (2000)

    Show us the table structures involved. You can really only control this from a form.
    Charlotte

  3. #3
    Lounger
    Join Date
    Sep 2006
    Location
    St. Augustine, Florida, USA
    Posts
    28
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Edits during data entry (2000)

    I've attached a copy of the table definition. Let me know if this helps. Performing the edit on a form would be fine. The records are entered/displayed in tabular format on the the form.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Edits during data entry (2000)

    Which field in the table represents the staff member?

    Your table has Start Time, End Time and Minutes. Are you using all three?

    Let'c call the Start time of this record as S1 and the end time as E1

    The record overlaps another one in three cases:
    * if there is another record (with the same staff member and date), where the Starttime is before this E1, and the End Time is after this E1.
    * if there is another record (..) where the Start time is before S1 and the End Time is after S1
    * if there is another record where the Start Time is after S1 and the End time is before E1
    Regards
    John



  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Edits during data entry (2000)

    What field in the table determines a Staff member?

    What you need to do is to create a query which checks for the appropriate staff member that has overlapping start and end times of the event, when that query returns no records means the start and end time is ok.

    The criteria of the query apart from the staff member should be:
    Forms!ArrivalDateTime >= Rec.StartDateTime and forms!ArrivalDateTime < Rec.EndDateTime OR
    Forms!DepartureDateTime > Rec.StartDateTime and forms!DepartureDateTime <= Rec.EndDateTime OR
    Forms!ArrivalDateTime < Rec.StartDateTime and forms!DepartureDateTime > Rec.EndDateTime

    You will have to concatenate the Date and Time fields. Rec is the table record.

  6. #6
    Lounger
    Join Date
    Sep 2006
    Location
    St. Augustine, Florida, USA
    Posts
    28
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Edits during data entry (2000)

    Staff member is identified by TherCode. The Staff memer info is the main screen and the events are listed on a tabular sub-form that includes the staff member ID (TherCode). I've attached a screen print. Thanks for everyone's input so far!!

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Edits during data entry (2000)

    We could go round and round with this trying to get the field names correct, or you could post your database here for people to look at.

    What you need to do is to build a SQL statement after the 3 fields have been keyed in (Svc Date, Start ans Svc Min) and to open this as a recordset. If none are returned by the recordset it means that slot is vacant. You need to do this in the BeforeUpdate of the SvcMin field or alternatively in the BeforeUpdate event of the sub form.

  8. #8
    Lounger
    Join Date
    Sep 2006
    Location
    St. Augustine, Florida, USA
    Posts
    28
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Edits during data entry (2000)

    patt - Thanks for the help I've received so far. Field names were not the issue for me. I was responding to you and one other person that wanted to know what field determined the staff member. The suggestions did point me in the right direction. As it turns out I have resolved the issue with a query.

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Edits during data entry (2000)

    Just for interests sake, would you paste the text of the query here please.

  10. #10
    Lounger
    Join Date
    Sep 2006
    Location
    St. Augustine, Florida, USA
    Posts
    28
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Edits during data entry (2000)

    There are two queries...the first defines S1 and E1 as suggested in a previous post. The second matches up to all the records by TherCode and date to identify any time overlaps and updates those that meet the criteria. Here are the sql statements for each:

    SELECT MinutesTracking.TherCode, MinutesTracking.MinDate, MinutesTracking.ExcepDays, IIf([ExcepDays]>0,"12:01:00 AM",[StTime]) AS S1, IIf([ExcepDays]>0,"11:59:00 PM",[EndTime]) AS E1
    FROM MinutesTracking;

    UPDATE MinutesTracking INNER JOIN SelMinComp ON (SelMinComp.MinDate = MinutesTracking.MinDate) AND (MinutesTracking.TherCode = SelMinComp.TherCode) SET MinutesTracking.OvrlapDet = True
    WHERE (((MinutesTracking.StTime)>[S1] And (MinutesTracking.StTime)<[E1]) AND ((MinutesTracking.EndTime)>[S1] And (MinutesTracking.EndTime)<[E1])) OR (((MinutesTracking.EndTime)>[S1] And (MinutesTracking.EndTime)<[E1])) OR (((MinutesTracking.StTime)>[S1] And (MinutesTracking.StTime)<[E1]));

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •