[AccessD] Make Multiple Selections on a Form and Create a Recordfor Each

Tina,

    There is an interesting way that does not involve generation of any redundant records needing subsequent deletion. The user is presented with the full set of shadow records, covering all available options. 

    These shadow records, generated by using left join, do not impose any overhead on the source table. Only those rows where the user makes explicit entries, get transformed into real records. One could say that it gets you the best of both worlds.    

    My sample db named MedicineIntakeMonitor demonstrates the approach outlined above. It is available at Rogers Access Library. Link - 
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=45
 
    The sample is in Access 2000 file format and brief description is given below.
 
Best wishes,
A.D. Tejpal
------------

MedicineIntakeMonitor - (Sample Db)
Brief Description
===========================
    This sample db demonstrates monitoring of actual intake by patients against the course of various medicines prescribed by respective doctors. 

    For a given patient, the subform at top displays the medicine courses as prescribed by the doctors. It includes the name of medicine, name of doctor, date of prescription, duration (days) and doses (number of times per day). There is a field for doctor's notes as well.

    The other subform displays a grid of shadow new records for each dose on each day of the prescribed course for each medicine. To confirm intake of a particular dose of given medicine, the user clicks in a check box named Done. The date and time for such confirmation gets automatically inserted in field named DtStamp. 

    As soon as the user clicks the Done check box, the current shadow record gets converted into an actual record in table T_MedTrack. This way, there is no redundant addition to records, till confirmatory action is actually implemented. Also, if the user attempts to click the check box for any of the future dates (i.e. beyond today's date), a friendly message preventing such an entry comes up.

    As added convenience, highlighting has been provided as follows:
    (a) When the user is on any record in the compliance subform (the lower one), corresponding record in prescription subform (the upper one) gets highlighted in yellow. This way, the user can readily see the doctor's and other particulars relevant to the current dose of current medicine.
    (b) When compliance for a given dose is confirmed and the date stamp matches due date, the record gets highlighted in light blue if the due date is same as today's date. Later, when this due date becomes older than current day's date, the highlight color for such a record changes to orange.
    (c) Records where compliance date stamp does not match the due date, are highlighted in pink.
    (d) Records older than current day's date, where compliance has not been confirmed (via selection of check box), are also highlighted in pink.

    The sample includes report generation for the selected settings, providing compliance status for various prescriptions. Records where compliance is missing or delayed beyond due date, are highlighted in color. Summary of the incidence of missed or delayed compliance is given in the group footer.

    Version:  Access 2000 File Format

    References:  DAO 3.6
===========================

  ----- Original Message ----- 
  From: tinanfields at torchlake.com 
  To: AccessD at databaseadvisors.com 
  Sent: Friday, February 27, 2009 23:52
  Subject: [AccessD] Make Multiple Selections on a Form and Create a Recordfor Each


  I hope the subject line isn't too confusing :)

  Here's the situation:  volunteers fill out a paper form indicating which skills they have and at 
  what level (1 to 3, with 1 being high).  When this database was built (1999), the developer crafted 
  a macro that added one record for every possible skill (32, so far) to the table of volunteers and 
  skills.  The data-entry person then went down the rows, clicking the ones that were to be selected, 
  and entering the skill level number.  This results in lots of empty records.  Since then, the 
  database crashed and the macro no longer works anyway.

  I want to craft a form with a checkbox for each skill and a drop-list of the skill-levels, so the 
  data-entry person can click the checkbox and select the skill level - then click a button that will 
  place one record for each skill selected into the table of volunteers and skills.  This gives the 
  data entry person the same ease of entry, but does not create lots of empty records.  I saw 
  something in the ADH that captures the number of selections made, and I'm guessing I need to start 
  there, but I could sure use some inspiration on this.  If three skills are checked, I want three 
  records entered for that volunteer in the volunteers and skills table - each one referencing one of 
  the selected skills.  

  Thanks for any ideas.

  Tina