Search code examples
ms-accessruntime-errorado

Run-time error 91 VBA when trying to resolve write conflict error

I will preface this with the fact that I am a newbie to Access/VBA. I am trying to create a set of forms for data entry in MS Access for a linked SQL Server Database to record vegetation data. I have a form with three unbound controls that provide the values to fields for several records in a subform. Specifically, the three controls in the main form are Transect_OID, Data_Recorder, and Data_Observer. In the subform, each Transect_OID corresponds to 30 records corresponding to each of 30 locations along a transect where plant species are recorded. I have added some event procedures in the main form to autopopulate the corresponding fields in the subform to ensure consistency of records for an entire transect. However, this setup resulted in a write conflict error. After scouring the interwebs for fixes, I followed the advice in solution #5 in this link https://www.accessrepairnrecovery.com/blog/fix-ms-access-write-conflict-error. When I add the suggested OnActivate event procedure, I get Run-time Error 91 "object variable or with block variable not set." Here is my code for that event procedure:

Private Sub Form_Activate()
Dim rs As ADODB.Recordset
Set rs = Me.Recordset.Clone
rs.Open
rs.Bookmark = Me.Bookmark
DoCmd.RunCommand acCmdRefresh
Me.Bookmark = rs.Bookmark
rs.Close
Set rs = Nothing
End Sub

Private Sub Form_Deactivate()
DoCmd.RunCommand acCmdSaveRecord
End Sub

Private Sub Observer_AfterUpdate()
Me.frmLPIDetail.Form.Data_observer = Me.Observer
Me.Dirty = False
End Sub

Private Sub Recorder_AfterUpdate()
Me.frmLPIDetail.Form.Data_recorder = Me.Recorder
Me.Dirty = False
End Sub

From researching other Run-time error 91 complaints, it seem like this error get thrown when:

  1. the appropriate library isn't selected as a reference. In this case that would be Microsoft ActiveX Data Objects 2.5 Library. I have ensured that this is selected.

  2. An object variable is declared but not set. Line 3 of the code should be where it is set. However, this the line the debugger complains about.

  3. An object variable is set to nothing. That is the case of the second-to-last line, but my understanding is that line resets the object variable

  4. A Go To statement within a With block. These commands are never called in this subroutine, so this cause can easily be ruled out.

I am at a loss for why this is happening. Any advice would be much appreciated.

Solution

  • First the write conflict:

    It turned out that my issue stemmed from the fact that I had bit datatype fields in the control source table for this form. Apparently, if bit fields that come from a linked SQL Server database are left null Access somehow converts them to 0s which makes that a write conflict. In SQL, I change the value of all records to 0. This solved the write conflict problem.

    Second the Runtime Error code 91: This results from the fact that I was trying to use the work around suggested by the link in my original question on the main form, which was not the source of the write conflict. When I added the original code to the subform and removed it from the main form the runtime error 91 went away.