troubleshooting Question

Workaround Could not update; currently locked by another session on this machine

Avatar of JOKL
JOKL asked on
Microsoft Access
2 Comments1 Solution254 ViewsLast Modified:
I have built  A Share Registration Application and in the Transaction Subform you select the share certificates to cancel and then resisue them to someone else.

When you select a share cert to cancel the application must find the record in the transaction detail table and change its inissue flag and recored the current transactionid in the cancelationID field for audit trail   - so you can track which transcation cancelled the shaares when veiewing the original record and to ensure that a cert when cancelled does not presentitself again on the Combo box which offers the user the share certs owned by the shareholder and available for Cancellation.

So when the user selects a cert in the Combo box the before update code kicks in to effect the above.

The 1st step is to see if he is changing his mind on which cert he wants to cancel - you have to undo the previous cancellation and cancel this one
 if it is new cancellation you just go ahead and cancel the cert issued previously and record the information on the currect record

My problem is that I get the error above when i try and edit the old record on the table my code is as follows: Do I need to clone the recordset?

Private Sub CertNoIssued_BeforeUpdate(Cancel As Integer)
Dim test As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("sharetransactiondetails", dbOpenDynaset)
Dim Msg As String
Dim NewID As String
Set db = CurrentDb
Dim SearchID As Variant
test = Me!TransactionDescription2
Select Case test
        Case "Sell", "Cancel", "Redemption"

        'Are you Editing an Existing Cert # or Entering a new One ???
        If Me.CertNoIssued.OldValue <> Me.CertNoIssued And Me.CertNoIssued.OldValue > 0 Then
            ' You are editing a sharecert therefore I must undo the cancellation ID and Reset the Inissue Flag
       
            SearchCertID = Me.CertNoIssued.OldValue
            SearchCompanyId = Forms!Sharetransactions.CompanyId
            SearchShareholderID = Me.ShareholderID
            SearchNoOfShares = Me.NoOfShares * -1
            SearchCancelID = Forms!Sharetransactions.TransactionID
           
            rst.FindFirst "CertNoIssued =" & SearchCertID & "AND NoOfShares =" & SearchNoOfShares & "And CancelationID = " & SearchCancelID
            rst.Edit
            rst!InIssue = True
            rst!CancelationID = 0
            rst!ShareLocation = 1
            rst.Update
            SearchID = Me.CertNoIssued.Column(2)
            rst.FindFirst "TransactionDetailID =" & SearchID
            rst.Edit
            rst!InIssue = False
            rst!CancelationID = Forms!Sharetransactions!TransactionID
            rst.Update
            rst.Close
           
            Me.NoOfShares = -Me.CertNoIssued.Column(1)
            Me.InIssue = False
            Me.ShareLocation = 3
        Else
        ' you are selecting a cert for the 1st time
        'search for the transaction on the original cert and update the cancellation ID and Reset the Inissue Flag
         'Set rst = CurrentDb.OpenRecordset("sharetransactiondetails", dbOpenDynaset)
                SearchID = Me.CertNoIssued.Column(2)
                rst.FindFirst "TransactionDetailID =" & SearchID
                rst.Edit
                rst!InIssue = False
                rst!CancelationID = Forms!Sharetransactions!TransactionID
                rst.Update
                rst.Close
                Me.NoOfShares = -Me.CertNoIssued.Column(1)
                Me.InIssue = False
                Me.ShareLocation = 3
        End If

End Select


ASKER CERTIFIED SOLUTION
puppydogbuddy

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros