Problem with record locking during UPDATE query

Question

I have a form whose Default View is a Datasheet.  The Record Locks property of the form is set to Edited Record (since my database is used in a multiuser environment).  One of the fields (DisplayPrintFlag) within the datasheet is a Yes/No datatype.  In the After Update event for DisplayPrintFlag, I have written the following code:

    Private Sub DisplayPrintFlag_AfterUpdate()

        Dim strSQL As String

        strSQL = "UPDATE WBSStatus SET WBSStatus.DisplayPrintFlag = " & _

                 Me.DisplayPrintFlag.Value & " " & _

                 "WHERE WBSStatus.SPRFNumber = '" & Me.SPRFNumber.Value & "' " & _

                 "AND WBSStatus.PhaseDeliverableSequence Between " & _

                 Me.PhaseDeliverableSequence.Value & " And " & _

                 Me.PhaseDeliverableSequence.Value + 99

       'DoCmd.SetWarnings False    (this line temporarily disabled for debugging purposes)

        DoCmd.RunSQL strSQL

       'DoCmd.SetWarnings True    (this line temporarily disabled for debugging purposes)

    End Sub

When the code executes, only some of the records that satisfy the WHERE clause get updated, and I get the following error message:

Microsoft Access can't update all the records in the update query.    Microsoft Access didn't update 0 field(s) due to a type conversion failure, 0 record(s) due to key violations, 3 record(s) due to lock

    violations, and 0 record(s) due to validation rule violations.

However, if I execute the same SQL statement (as shown above, but obviously with actual values) in Query Design mode (i.e., outside of the form), all of the records that satisfy the WHERE clause get updated.

Can someone kindly tell me (1) why Access can't lock all the records based on my code, yet it can lock all the records when I run the query in Query Design mode, and (2) how to modify my code so that I don't run into the record locking problem.

Thanks in advance for any assistance.

Answer 1

I finally found a solution to my problem, thanks to a post by boblarson (http://www.access-programmers.co.uk/forums/showthread.php?t=233924).  Hopefully others can benefit from the same solution.

My revised code is shown below with boblarson's solution in bold.  I also replaced DoCmd.RunSQL with object.Execute (in order to avoid having to call the SetWarnings method).

    Private Sub DisplayPrintFlag_AfterUpdate()

        Dim dbs as DAO.Database

        Dim strSQL As String

        strSQL = "UPDATE WBSStatus SET WBSStatus.DisplayPrintFlag = " & _

                 Me.DisplayPrintFlag.Value & " " & _

                 "WHERE WBSStatus.SPRFNumber = '" & Me.SPRFNumber.Value & "' " & _

                 "AND WBSStatus.PhaseDeliverableSequence Between " & _

                 Me.PhaseDeliverableSequence.Value + 1 & " And " & _

                 Me.PhaseDeliverableSequence.Value + 99

        If Me.Dirty Then Me.Dirty = False   ' removes any locks on the form.        dbs.Execute strSQL

    End Sub

Thanks Bob!

Answer 2

I finally found a solution to my problem, thanks to a post by boblarson (http://www.access-programmers.co.uk/forums/showthread.php?t=233924).  Hopefully others can benefit from the same solution.

My revised code is shown below with boblarson's solution in bold.  I also replaced DoCmd.RunSQL with object.Execute (in order to avoid having to call the SetWarnings method).

    Private Sub DisplayPrintFlag_AfterUpdate()

        Dim dbs as DAO.Database

        Dim strSQL As String

        strSQL = "UPDATE WBSStatus SET WBSStatus.DisplayPrintFlag = " & _

                 Me.DisplayPrintFlag.Value & " " & _

                 "WHERE WBSStatus.SPRFNumber = '" & Me.SPRFNumber.Value & "' " & _

                 "AND WBSStatus.PhaseDeliverableSequence Between " & _

                 Me.PhaseDeliverableSequence.Value + 1 & " And " & _

                 Me.PhaseDeliverableSequence.Value + 99

        If Me.Dirty Then Me.Dirty = False   ' removes any locks on the form.        dbs.Execute strSQL

    End Sub

Thanks Bob!

Answer 3

I finally found a solution to my problem, thanks to a post by boblarson (http://www.access-programmers.co.uk/forums/showthread.php?t=233924).  Hopefully others can benefit from the same solution.

My revised code is shown below with boblarson's solution in bold.  I also replaced DoCmd.RunSQL with object.Execute (in order to avoid having to call the SetWarnings method).

    Private Sub DisplayPrintFlag_AfterUpdate()

        Dim dbs as DAO.Database

        Dim strSQL As String

        strSQL = "UPDATE WBSStatus SET WBSStatus.DisplayPrintFlag = " & _

                 Me.DisplayPrintFlag.Value & " " & _

                 "WHERE WBSStatus.SPRFNumber = '" & Me.SPRFNumber.Value & "' " & _

                 "AND WBSStatus.PhaseDeliverableSequence Between " & _

                 Me.PhaseDeliverableSequence.Value + 1 & " And " & _

                 Me.PhaseDeliverableSequence.Value + 99

        If Me.Dirty Then Me.Dirty = False   ' removes any locks on the form.        dbs.Execute strSQL

    End Sub

Thanks Bob!