Results 1 to 9 of 9
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    Need recommendation for a "delete record" sub-process

    Experts:

    I need some recommendations with a process as part of *deleting a record".

    Here's what I have:
    - tblEmployees having an autonumber ID field called [EmployeeID].
    - Amongst other fields, I then have a number field called [ReportsToEmployeeID].
    - Now, when adding a new employee to the table, the [EmployeeID] number of his/her supervisor is selected in the [ReportsToEmployeeID] field.

    Now, here's what I need some help with:
    - If I delete the supervisor (employee) his/her ID number remains in the subordinate employee [ReportsToEmployeeID] field.
    - So, as part of the record deletion process, I somehow would like to inform the user/administrator (via dialogue box or query or else) that "Supervisor XYZ" is no longer w/ the company and thus, all of the supervisor's subordinate employees need to be reassigned to a new supervisor.
    - Please keep in mind that number of subordinate employees could potentially be a large number, so a dialogue box listing all subordinate employees may not be the best choice.



    Any thougts on how to close the loop of informing the system user/admin about reassigning a new supervisor ID to the other employees?

    Thank you,
    EEH

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    If you know who the new supervisor will be at the time you delete the old one then you could just run an update query to change the appropriate IDs.

    Alternatively, you could set the Supervisor ID for the affected subordinate employees to 0 and have some code run on the On Activate event of a form (perhaps your switchboard form) to count the records that now have 0 as the Supervisor ID and then nag the user to assign a Supervisor to these records.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    really you should not be deleting records - but have a 'date left organisation' field or similar

    you can have a simple message box with code along the lines of

    Code:
    if dcount("*","myTable","FKfield=" & me.PKField) >0 then
       msgbox "This person cannot be deleted until all subordinates have been reassigned"
    end if
    other methods would be to disable the delete button so user cannot delete

    Code:
    me.btnDelete.enabled=dcount("*","myTable","FKfield=" & me.PKField) =0

    Finally, as a backup set your relationships to enforce referential integrity - this will prevent a record being deleted accidentally

  4. #4
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Ajax - good point, we'll archive the employees (vs. deleting).

    For testing purposes, please humor and check as to why the delete process is not working yet.

    - I added a test "supervisor" record and used its PK ID as the FK ID for the subordinate employee.
    - Then clicked on "delete" and your recommendation DCOUNT provided output message as you indicated.
    - I then changed the supervisor ID to another supervisor ID and attempted to delete original supervisor record.
    - The msgbox no longer appeared (great); however, the supervisor record was not deleted either. What is missing in the below code?

    Code:
    Private Sub cmd_DeleteRecord_Click()
        
        'Prompt to confirm the delete current record operation
        If MsgBox("Do you want to delete the record?", vbYesNo + vbQuestion, "Delete Record") = vbYes Then
            
            If DCount("*", "tblEmployees", "ReportsToEmployeeIDfk=" & Me.EmployeeIDpk) > 0 Then
                MsgBox "This person cannot be deleted until all subordinates have been reassigned."
            End If
            
         Else
            
            DoCmd.SetWarnings False
            DoCmd.RunCommand acCmdDeleteRecord
            DoCmd.SetWarnings True
            
        End If
           
    
         
    End Sub
    Once the delete records function actually deletes the test record, I then will replace delete command with an update function of where boolean value is changed for field "Onboard" = false.

    But again, for right now, I need to delete the supervisor record if no subordinate has been assigned to supervisor. What VBA code am I missing?

    EEH

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you have the process order incorrect, try

    Code:
        'Prompt to confirm the delete current record operation
        If MsgBox("Do you want to delete the record?", vbYesNo + vbQuestion, "Delete Record") = vbYes Then
            
            If DCount("*", "tblEmployees", "ReportsToEmployeeIDfk=" & Me.EmployeeIDpk) > 0 Then
    
                MsgBox "This person cannot be deleted until all subordinates have been reassigned."
            
             Else
            
                DoCmd.SetWarnings False
                DoCmd.RunCommand acCmdDeleteRecord
                DoCmd.SetWarnings True
            
             End If
    
        End If

  6. #6
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Perfect -- works like a charm! I will update the delete function to a "history/archive" function.

  7. #7
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Ajax -- my apologies... I thought VBA code/line for updating the boolean field would be straightforward.

    Code:
                CurrentDb.Execute "UPDATE T01_StaffMembers SET T01_StaffMembers.Onboard = False WHERE (((T01_StaffMembers.StaffMemberIDpk) = " & StaffMemberIDpk
    Any feedback on how I can update that field to true?

    Thanks,
    EEH

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    not quite sure what you are asking

    Any feedback on how I can update that field to true?

    CurrentDb.Execute "UPDATE T01_StaffMembers SET T01_StaffMembers.Onboard = False WHERE (((T01_StaffMembers.StaffMemberIDpk) = " & StaffMemberIDpk


    also remove the brackets since they are not needed for this query, or ensure you close them off - you have 3 ( and only 1 )
    CurrentDb.Execute "UPDATE T01_StaffMembers SET T01_StaffMembers.Onboard = True WHERE (((T01_StaffMembers.StaffMemberIDpk) = " & StaffMemberIDpk

    However if the user currently has the record in their form - just update the field

    still recommend you use a date field rather than a boolean field and just populate it with the date they left (i.e. no longer 'onboard')

    I know from your previous posts you said you weren't interested in the history - just hope you don't get asked questions like 'how has the organisation changed in the last month/ 6 months/year?'. Using a yes/no field simply gives you the view at this point in time - and if someone has made a mistake ("thought I had marked JB as left, must have marked someone else, but don't know who"), no way to unwind it.

    But it's your app.....

  9. #9
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Ajax -- excellent recommendation! I just added a field "DateDeparted" which is set to =Now() when user "archives" the employee. This was a very simple modification yet adds value to the organization.

    Thank you for providing this recommendation!!!

    EEH

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

Similar Threads

  1. Replies: 4
    Last Post: 12-13-2016, 09:48 AM
  2. Replies: 13
    Last Post: 05-06-2014, 12:42 PM
  3. Replies: 7
    Last Post: 11-30-2013, 12:33 PM
  4. turn "about to delete record" warning back on
    By markjkubicki in forum Programming
    Replies: 1
    Last Post: 11-01-2012, 12:21 PM
  5. Replies: 4
    Last Post: 06-14-2010, 02:31 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