Updating 'Locked' Records

[mcupito]

I am trying to update an associate's status (they left the company voluntarily, were fired, etc.)via Combobox and it keeps throwing an error saying that the records are locked. I have tried 3 different solutions yet I can't figure it out. I did research and it says that it means the records are open elsewhere, so I am kind of stuck.

Does anyone have any ideas?!

Here is the code for the AfterUpdate() on the Combobox.

Select Case Me![StatusCbx]
        Case 1
        'Active: Calculate vesting date with standard vesting period - award units remain unchanged
            If MsgBox("Update status?", _
                vbYesNo + vbQuestion + vbDefaultButton2) = vbNo Then
                Cancel = True
                Me.StatusCbx.Undo
            Else
                DoCmd.Hourglass True
                DoCmd.SetWarnings False
                    DoCmd.OpenQuery "ActiveVestingDateUpdateQry"
                    Me.Refresh
                    DoCmd.OpenQuery "SeparationDateActiveUpdateQry"
                DoCmd.SetWarnings True
                DoCmd.Hourglass False
                Me.Refresh
            End If
   
        Case 2
        'Voluntary separation: determine eligibility for special vesting
        DoCmd.OpenQuery "SeparationDateUpdateQry"
            If Me.Parent!Age >= 55 And Me.Parent!YearsofService >= 10 Then
                DoCmd.OpenQuery "SeparationDateUpdateQry"
                
                If MsgBox("Employee is eligible for special vesting. Update status and calculate special vesting?", _
                    vbYesNo + vbQuestion + vbDefaultButton2) = vbNo Then
                    Cancel = True
                    Me.StatusCbx.Undo
                Else
                    
                    DoCmd.RunCommand acCmdSaveRecord
                    DoCmd.Hourglass True
                    DoCmd.SetWarnings False
                        DoCmd.OpenQuery "VoluntarySepUpdateQry"
                        DoCmd.OpenQuery "SpecialVestingUpdateQry"
                        DoCmd.OpenQuery "VoluntarySepUpdateQry"
                    DoCmd.SetWarnings True
                    DoCmd.Hourglass False

                    Me.Refresh
                End If

            Else
                If MsgBox("Employee is not eligible for special vesting. Update status and determine award forfeitures?", _
                    vbYesNo + vbQuestion + vbDefaultButton2) = vbNo Then
                    Cancel = True
                    Me.StatusCbx.Undo
                Else
                    DoCmd.RunCommand acCmdSaveRecord
                    DoCmd.Hourglass True
                    DoCmd.SetWarnings False
                        DoCmd.OpenQuery "VoluntarySepUpdateQry"
                        DoCmd.OpenQuery "ForfeitVestingDateUpdateQry"
                        DoCmd.OpenQuery "ForfeitedUnitsToContributionQry"
                    DoCmd.SetWarnings True
                    DoCmd.Hourglass False
                
                    Me.Refresh
                End If
            End If

        Case 5
        'Death/Disability: set vesting date for unvested awards to the current date
            'DoCmd.OpenForm "SeparationDateFrm"
            
            If MsgBox("Update status for death/disability?", _
                vbYesNo + vbQuestion + vbDefaultButton2) = vbNo Then
                Cancel = True
                Me.StatusCbx.Undo
            Else
                DoCmd.RunCommand acCmdSaveRecord
                DoCmd.Hourglass True
                DoCmd.SetWarnings False
                    DoCmd.OpenQuery "DeathVestingDateUpdateQry"
                DoCmd.Hourglass False
                DoCmd.SetWarnings True
     
                Me.Refresh
            End If
            
        Case 3
        'Involuntary separation:
        'Determine forfeiture of all awards
            'DoCmd.OpenForm "SeparationDateFrm"
            
            If MsgBox("Update status for separation?", _
                vbYesNo + vbQuestion + vbDefaultButton2) = vbNo Then
                Cancel = True
                Me.StatusCbx.Undo
            Else
                DoCmd.RunCommand acCmdSaveRecord
                DoCmd.Hourglass True
                DoCmd.SetWarnings False
                    DoCmd.OpenQuery "ForfeitVestingDateUpdateQry"
                    DoCmd.OpenQuery "InvoluntarySepUpdateQry"
                    DoCmd.OpenQuery "ForfeitedUnitsToContributionQry"
                    DoCmd.OpenQuery "ForfeitUpcomingPayoutsQry"
                DoCmd.Hourglass False
                DoCmd.SetWarnings True

                Me.Refresh
            End If
        Case 6
        'Employment with Competitor
            'DoCmd.OpenForm "SeparationDateFrm"
            
            If MsgBox("Update status for employment with competitor?", _
                vbYesNo + vbQuestion + vbDefaultButton2) = vbNo Then
                Cancel = True
                Me.StatusCbx.Undo
            Else
                
                DoCmd.RunCommand acCmdSaveRecord
                DoCmd.Hourglass True
                DoCmd.SetWarnings False
                    DoCmd.OpenQuery "ForfeitVestingDateUpdateQry"
                    DoCmd.OpenQuery "EmploymentCompUpdateQry"
                    DoCmd.OpenQuery "ForfeitedUnitsToContributionQry"
                    DoCmd.OpenQuery "ForfeitUpcomingPayoutsQry"
                DoCmd.Hourglass False
                DoCmd.SetWarnings True

                Me.Refresh
            End If
       
    End Select

[Rabbit]

Are you sure it is in the AfterUpdate? The AfterUpdate doesn't have a cancel variable. Because it's already updated, there's nothing to cancel.

However, the BeforeUpdate event does have a cancel variable because it is in the process of updating the record. And if you try to modify the data in the BeforeUpdate event, then you will get a lock violation.

[mcupito]

Thanks for the reply, Rabbit. Yes, I am sure it is the AfterUpdate() event. Here is the code I omitted:

Private Sub StatusCbx_AfterUpdate()
On Error GoTo Err_Handler

Dim Cancel As Boolean
Dim InputSepDate As Date

[Rabbit]

What is the cancel varible used for? Which line of code is throwing the error?

[mcupito]

The cancel variable is used for the MsgBox.

The code (so far) that is throwing the error is the voluntary separation. The "SeparationDate UpdateQry"

        Case 2
        'Voluntary separation: determine eligibility for special vesting
        DoCmd.OpenQuery "SeparationDateUpdateQry"
            If Me.Parent!Age >= 55 And Me.Parent!YearsofService >= 10 Then
                DoCmd.OpenQuery "SeparationDateUpdateQry"
                
                If MsgBox("Employee is eligible for special vesting. Update status and calculate special vesting?", _
                    vbYesNo + vbQuestion + vbDefaultButton2) = vbNo Then
                    Cancel = True
                    Me.StatusCbx.Undo
                Else
                    
                    DoCmd.RunCommand acCmdSaveRecord
                    DoCmd.Hourglass True
                    DoCmd.SetWarnings False
                        DoCmd.OpenQuery "VoluntarySepUpdateQry"
                        DoCmd.OpenQuery "SpecialVestingUpdateQry"
                        DoCmd.OpenQuery "VoluntarySepUpdateQry"
                    DoCmd.SetWarnings True
                    DoCmd.Hourglass False

                    Me.Refresh
                End If

            Else
                If MsgBox("Employee is not eligible for special vesting. Update status and determine award forfeitures?", _
                    vbYesNo + vbQuestion + vbDefaultButton2) = vbNo Then
                    Cancel = True
                    Me.StatusCbx.Undo
                Else
                    DoCmd.RunCommand acCmdSaveRecord
                    DoCmd.Hourglass True
                    DoCmd.SetWarnings False
                        DoCmd.OpenQuery "VoluntarySepUpdateQry"
                        DoCmd.OpenQuery "ForfeitVestingDateUpdateQry"
                        DoCmd.OpenQuery "ForfeitedUnitsToContributionQry"
                    DoCmd.SetWarnings True
                    DoCmd.Hourglass False
                
                    Me.Refresh
                End If
            End If

Here is the SQL for the SeparationDateU pdateQry

UPDATE AssociateTbl INNER JOIN AwardTbl ON AssociateTbl.EmployeeID = AwardTbl.EmployeeID SET AssociateTbl.SeparationDate = Date()
WHERE (((AwardTbl.EmployeeID)=[Forms]![AstProfileFrm]![EmployeeID]));

[mcupito]

I deleted the INNER JOIN to the AwardTbl and that seems to have fixed it. Thanks for your help as always, Rabbit.

[zmbd]

DoCmd.OpenQuery

You use a lot of these, something to be aware of, each time you open the query like this, each query can potentiall a lock the underlying table(s).

Another thing to keep in mind is that several types of queries are not updatable: Why is my query read-only?

[mcupito]

Thanks, zmbd! I did not know that. VBA has never been something I am great at.