[AccessD] data validation

J.C.,

    In this context, my sample db named  DentalAppointments might perhaps be of interest to you. It is available at Rogers Access Library. Link:
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=45 

    The sample displays default time-slots of 30 minutes each, with the facility to expand or shrink the same by altering the start and (or) end times for any given particular slot. Such alteration is permitted only if it does not impinge upon already booked slots.

    When the user expands an existing time-slot, and if the altered duration infringes with any other free slots, the affected time slots get disabled and highlighted in a different color. In short, there is built in safeguard against entry of conflicting time slots.

    You could adapt the underlying approach suitably, for your specific needs. (In the scenario mentioned by you, time from and time to of pass request could be considered similar to the scheduled working hours of the clinic in sample db). 

    A brief description is given below. (Attention is drawn to the precautions regarding prior registration of Date And Time Picker control as mentioned below).
 
Best wishes,
A.D.Tejpal
------------

Brief Description
(Sample Db: DentalAppointments)
=========================
    This sample db demonstrates Appointments Planner for a dental clinic having two chairs.

    The Planner opens with appointment grid for today's date pre-selected and the cursor moves to first available free time slot.

    If any other date is selected in the Date Picker control, fresh appointment grid for that date is presented (if not already existing).

    Default values of time slots can be altered (e.g. longer slots might be needed for some tasks) - provided it does not intrude upon an already booked timeslot.

    Caution - 
    (a) An activex control named 'Microsoft Date And Time Picker Control' has been used in this database. This control is required to be registered in the access installation on user's computer, before attempting to open & use the sample db.

    (b) For registration - file  mscomct2.ocx  should be available in System32 folder of windows operating system. Open any access database other than this db (even a blank one will do), click Tools -> Activex Controls -> Register. Navigate to the location of above .ocx file and click OK.
    
    Version - Access 2000 file format

    Reference - DAO 3.6
=======================================

  ----- Original Message ----- 
  From: jwcolby 
  To: Access Developers discussion and problem solving 
  Sent: Friday, April 08, 2011 08:16
  Subject: [AccessD] data validation


  Scenario:

  I fill out a pass request = date / time from / time to.
  The pass request's time from needs to be < the Time to.

  Three detail records - location / time from / time to.

  The detail record's time from needs to be >= the pass request time from and <= the pass request time 
  time to.

  Any subsequent detail record needs to follow the same rules but also needs to be after the first 
  request's time from / to.

  Any change to any time needs to re-perform the validation.

  Pass request:

  5/1/2011 08:45 - 13:00

  Detail record:

  Church 09:00 - 11:00
  Restaurant 11:00-12:00
  park 12:00-12:45

  There must always be a pass request date / time
  There must always be one or more detail records.
  There may be up to three detail records.

  How would you perform data validation in this scenario?

  I vote for a pair of classes - time class and supervisor which is the pass time and has code to check the details against itself, with a collection to hold the time classes.  Time classes have code to compare itself against another time class.

  Etc.

  I have never had to do a validation like this but now I need to.
  -- 
  John W. Colby
  www.ColbyConsulting.com