I've been looking through the other questions related to mine, but most are about multi-user and this one came close but not quite.
System
MS Access 2013
with Linked Tables to Office 365 Sharepoint
tblQuote - frmQuote
tblQuoteItems - sbfrmQuoteItems
No Record Locks
I'm attempting to setup a select all/deselect all button that when clicked runs db.Execute Update on the tblQuoteItems where equal to Quote ID and Quote Version.
I have a button on the main form that calls the below process.
Public Sub SelectLineItems(strTable As String, strID As String, _
intID As Integer, bln As Boolean, Optional intVersion As Integer)
Dim db As Database
Dim strSQL As String
Dim strVersion As String
Set db = CurrentDb
strSQL = "UPDATE " & strTable & " SET [Selected] = "
If intVersion > 0 Then
strVersion = " AND [QuoteVersion] = " & intVersion
Else
strVersion = ""
End If
If bln Then
strSQL = strSQL + "False WHERE " & strID & " = " & intID & strVersion & ";"
Else
strSQL = strSQL + "True WHERE " & strID & " = " & intID & strVersion & ";"
End If
db.Execute strSQL, dbFailOnError
db.Close
Set db = Nothing
Exit Sub
It's pretty simple, nothing to crazy. The problem occurs when I try to run this after a record has been modified by the form and it still has focus. Because of the dbFailOnError I get the error message, If I remove dbFailOnError it will update every record except the one that has been modified through the form.
If I modify the record then select a different record manually by clicking with the mouse, the record is no longer locked and the Update works with no errors.
I have tried to replicate the process of clicking on a new record and have put the below code
If Me.Dirty Then Me.Dirty = False
In every Event I could think of like:
The subform_Current, subform_Dirty, subform.Control.Dirty/Lost_focus/subform_Before and After Update, The exit event of the subform control on the main form...etc
Placing the code in different areas doesn't make any difference, the first time the code is called it updates the record and I can confirm this in the table, the new value is present.
I've attempted to requery the subform
I've tried
DoCmd.GoToRecord , , acFirst
Then setting focus to the first control of the record.
I've even tried changing the update from the db.Execute to using a recordset object
Dim db As Dao.Database
Dim rs As Dao.Recordset
Dim strSQL As String
Dim strVersion As String
If intVersion > 0 Then
strVersion = " AND [QuoteVersion] = " & intVersion
Else
strVersion = ""
End If
strSQL = "SELECT * FROM " & strTable & " WHERE " & strID & "= " & intID & strVersion
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
With rs
Do Until .EOF
.Edit
!Selected = bln
.Update
.MoveNext
Loop
End With
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
But again it will cycle through every unlocked record and update, until it gets to the one modified by the form which then throws the same error.
I've tried opening the recordset then closing it then reopening it. But it doesn't seem to matter it's the form that's holding onto the locked record.
The only solution that has worked for me was to Set the subform recordsource to nothing, then run the update, then reset the recordsource to what it was.
The Selected column is within the QuoteItems table itself, and not in it's own table with reference to the QuoteItems ID
My question is how do I get the form to release the record through code that mimics the action of manually clicking on a new record without resetting the subform's recordsource.
