Re: How to fix client/server recordsets out of synch
Hi eveyone,
I really need some help with this problem. I have subform with bunch of check boxes and previous and next buttons.
What I need is when user clicks on a check box the previous selection is saved in a field in the table and the
current choice is saved as well. This is done in the stored procedure in the afterupdate event of the control. And that's
working fine. But the second part in the afterupdate event I want to reset the controls in the subform and does that.
The error occurs when clicking on previous or next button to move to the next record after the change is made.
This is Error message:
Row cannot be located for updating. Some values may have been changed since it was last read.
I know it has to do with the client recordset is not in synch with server recordset. I don't see
why the code I have won't fix the problem. I am using Access 2000 and SQL SERVER 2000.
Please Help.
Thank you!
'This subroutine has two parts:
' saves previous disposition and add new disposition to tblBag_results via stored procedure
' then set the controls in the form by checking a control if is true and
' set other check boxes value to false
Private Sub belt_sort_AfterUpdate()
'Dim rst As New ADODB.Recordset 'Declare as global in the subform module
'Dim obj As adp2.Class1 'Declare as global in the subform module
Dim ret_val As Integer
Set rst = New ADODB.Recordset
Set obj = New adp2.Class1
Set rst = obj.FLoad
If Me!belt_sort.Value <> 0 Then 'belt sort is checked
If (Forms!bag_results!access_type.Value = 1) Then
'saves previous disposition and add new disposition to tblBag_results
Call save_prev_dispo(3)
End If
obj.Update rst
rst.UpdateBatch
set_ctrls (False)
Me!bag_scrap.Value = False
Me!belt_sort.Value = True
Me!bag_result.Value = 2
rst.Update
rst.MarshalOptions = adMarshalModifiedOnly
obj.Update rst
rst.UpdateBatch
End If
'Previous button
Private Sub cmdPrevious_Click()
Set rst = Me.Recordset
Set rstclone = Me.RecordsetClone
rstclone.Bookmark = rst.Bookmark <-- Error message above occurred here
rst.MovePrevious <-- Or here
rst.MoveNext
some other code.....
End Sub
Class module
Public Function FLoad() As Recordset
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open "Select * from tblBag_results", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Set rs.ActiveConnection = Nothing
Set FLoad = rs
End Function
Public Sub Update(ByVal rsClient As Recordset)
Dim rsConnection As New ADODB.Connection
Dim rsServer As New ADODB.Recordset
'rsConnection.Open szConnect
rsServer.Open rsClient
Set rsServer.ActiveConnection = CurrentProject.Connection
rsServer.UpdateBatch
End Sub
Hi eveyone,
I really need some help with this problem. I have subform with bunch of check boxes and previous and next buttons.
What I need is when user clicks on a check box the previous selection is saved in a field in the table and the
current choice is saved as well. This is done in the stored procedure in the afterupdate event of the control. And that's
working fine. But the second part in the afterupdate event I want to reset the controls in the subform and does that.
The error occurs when clicking on previous or next button to move to the next record after the change is made.
This is Error message:
Row cannot be located for updating. Some values may have been changed since it was last read.
I know it has to do with the client recordset is not in synch with server recordset. I don't see
why the code I have won't fix the problem. I am using Access 2000 and SQL SERVER 2000.
Please Help.
Thank you!
'This subroutine has two parts:
' saves previous disposition and add new disposition to tblBag_results via stored procedure
' then set the controls in the form by checking a control if is true and
' set other check boxes value to false
Private Sub belt_sort_AfterUpdate()
'Dim rst As New ADODB.Recordset 'Declare as global in the subform module
'Dim obj As adp2.Class1 'Declare as global in the subform module
Dim ret_val As Integer
Set rst = New ADODB.Recordset
Set obj = New adp2.Class1
Set rst = obj.FLoad
If Me!belt_sort.Value <> 0 Then 'belt sort is checked
If (Forms!bag_results!access_type.Value = 1) Then
'saves previous disposition and add new disposition to tblBag_results
Call save_prev_dispo(3)
End If
obj.Update rst
rst.UpdateBatch
set_ctrls (False)
Me!bag_scrap.Value = False
Me!belt_sort.Value = True
Me!bag_result.Value = 2
rst.Update
rst.MarshalOptions = adMarshalModifiedOnly
obj.Update rst
rst.UpdateBatch
End If
'Previous button
Private Sub cmdPrevious_Click()
Set rst = Me.Recordset
Set rstclone = Me.RecordsetClone
rstclone.Bookmark = rst.Bookmark <-- Error message above occurred here
rst.MovePrevious <-- Or here
rst.MoveNext
some other code.....
End Sub
Class module
Public Function FLoad() As Recordset
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open "Select * from tblBag_results", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Set rs.ActiveConnection = Nothing
Set FLoad = rs
End Function
Public Sub Update(ByVal rsClient As Recordset)
Dim rsConnection As New ADODB.Connection
Dim rsServer As New ADODB.Recordset
'rsConnection.Open szConnect
rsServer.Open rsClient
Set rsServer.ActiveConnection = CurrentProject.Connection
rsServer.UpdateBatch
End Sub
