Results 1 to 3 of 3
  1. #1
    SiDai is offline Novice
    Windows Vista Access 2013 32bit
    Join Date
    Sep 2018
    Posts
    1

    Deleting a record in a child table whilst keeping a field from the record in the parent table

    Hi

    I am developing an Access Database for use by UK NHS Pharmacists within my organisation. The database is held on a secure NHS Server. The purpose of the database will be to record details of calls received by the on-call Pharmacist outside of opening hours from other hospital staff.



    The 'Parent Table - [tblCallDetails]', contains the specific details of the call also the name of the Pharmacist who answered the call. The details of the Pharmacists are kept in a 'Child table - [tblStaffDetails]' that includes the name of the Pharmacist. [tblCallDetails] has been set up to choose the name of the Pharmacist from [tblStaffDetails] using a Combo box, set up using the Lookup wizard in the Data type of the field.

    Current legislation requires calls to be kept for up to 21 years, in the case of calls involving patients who are children. However, the Pharmacists on-call will change over that period and many will discontinue on-call duties. Therefore, I will need to update the [tblStaffDetails] to reflect the changes in personnel. However, I need to be able to keep the Pharmacist name in the [tblCallDetails] for legal reasons, such should there be a future requirement to review the actions taken the Pharmacist can be easily identified.

    I am wondering what is the cleanest approach to take, so that Pharmacist Combo-box only lists the Pharmacists who are currently working, whilst still ideally maintaining a record of previous Pharmacists within both tables.

    Thanks for any help in advance

    SiDai

    Click image for larger version. 

Name:	Screen Shot 2018-09-25 at 19.11.31.jpg 
Views:	10 
Size:	89.1 KB 
ID:	35600

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Record in tblStaffDetails should never go away so the pharmacist data will always be available for historical records.

    Data entry for new records can restrict choices to 'active' pharmacists. The combobox RowSource query can have filter that includes records where RemoveRequestDate Is Null.

    This is called cascading or dependent combobox. Very common topic. Search forum.
    Last edited by June7; 09-25-2018 at 08:02 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you prevent inactive pharmacist names from being in the list, how do you ever search on them in the future? Recreate the form and its underlying query for a different purpose? Not me.
    Consider a means of applying one list or the other - or the entire list of pharma's. An option frame with 3 buttons could provide such choice. 1 for active, 1 for inactive and 1 for all. I would probably disable the combo unless a user chooses one of the options, then enable it. Should work as long as there aren't conflicting form properties such as only allowing additions.

    You could also open this form to the chosen search option, but user would have to close and reopen in order to change options.
    Last edited by Micron; 09-25-2018 at 05:34 PM. Reason: added info
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 17
    Last Post: 07-21-2023, 12:09 AM
  2. Replies: 7
    Last Post: 07-28-2015, 11:50 AM
  3. Deleting Child table record
    By Farida in forum Access
    Replies: 2
    Last Post: 07-07-2015, 05:39 AM
  4. Replies: 3
    Last Post: 08-09-2012, 01:49 PM
  5. Delete child records with parent record
    By jpkeller55 in forum Access
    Replies: 8
    Last Post: 09-07-2010, 08:52 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums