Write Conflict When Running a Loop

[jax1000rr]

Hi all,



I get a write conflict on only the last record when I run the below Loop. Any suggestions?

Private Sub btn_assign_Click()
Dim rst As Recordset, i As Integer
Set rst = Me.RecordsetClone
i = 0
rst.MoveFirst
Do While i < 25
   i = i + 1
   rst.Edit
   If rst![reinstated_1] = True Then
   rst![file_assigned_to] = Me.Form!assign_to
   rst![current_wf] = "Assigned - Reinstate Follow Up"
   rst![date_assigned] = Date
   Else
   rst![file_assigned_to] = Me.Form!assign_to
   rst![current_wf] = "Assigned - Pending Review"
   rst![date_assigned] = Date
   End If
   rst.Update
   rst.MoveNext
Loop
MsgBox i & "assigned."
rst.Close
Set rst = Nothing
DoCmd.Requery
End Sub

[June7]

Have you step debugged?

Why 25 as the loop condition? Could there be more or less than 24 records?

Consider:

For i = 1 to 24
   rst.Edit
   rst![file_assigned_to] = Me.Form!assign_to
   rst![current_wf] = IIf(rst![reinstated_1] = True, "Assigned - Reinstate Follow Up", "Assigned - Pending Review")
   rst![date_assigned] = Date
   rst.Update
   rst.MoveNext
Next

[alcapps]

change for i to rst.eof
Now ever how many records are in the recordset they will be processed.
rst.eof = recordset end of file ..
so keep looping until you have reached the end of the recorset.

you can add protection as well in a different color this checks for records to process.

Private Sub btn_assign_Click()
Dim rst As Recordset, i As Integer
Set rst = Me.RecordsetClone
i = 0
if rst.recordcount > 0 then
rst.MoveFirst
Do While not rst.eof
i = i + 1
rst.Edit
If rst![reinstated_1] = True Then
rst![file_assigned_to] = Me.Form!assign_to
rst![current_wf] = "Assigned - Reinstate Follow Up"
rst![date_assigned] = Date
Else
rst![file_assigned_to] = Me.Form!assign_to
rst![current_wf] = "Assigned - Pending Review"
rst![date_assigned] = Date
End If
rst.Update
rst.MoveNext
Loop
end if
MsgBox i & "assigned."
rst.Close
Set rst = Nothing
DoCmd.Requery
End Sub

[jax1000rr]

Ok, still having issues with this. If the value entered in Me.Form.no_of_files_to_assign is less than the RecordCount all is well, but if I enter a value > the RecordCount 0 records are edited and if I enter the exact # of RecordCount I get Run-time error 3021 - No current record. Code is below:

Private Sub btn_assign_Click()
Dim rst As Recordset, i As Integer
Set rst = Me.RecordsetClone
i = 0
If rst.RecordCount < Me.Form.no_of_files_to_assign Then
rst.MoveFirst
Do Until i = rst.EOF
   i = i + 1
   rst.Edit
   If rst![reinstated_1] = True Then
   rst![file_assigned_to] = Me.Form!assign_to
   rst![current_wf] = "Assigned - Reinstate Follow Up"
   rst![date_assigned] = Date
   rst![removed_from_hmda] = True
   Else
   rst![file_assigned_to] = Me.Form!assign_to
   rst![current_wf] = "Assigned - Pending Review"
   rst![date_assigned] = Date
   End If
   rst.Update
   Me.RecordsetClone.Requery
   rst.MoveNext
  Loop
  Me.filter_file_state = Null
  Me.filter_decision_status = Null
  DoCmd.Requery
rst.Close
Else
rst.MoveFirst
Do Until i = Me.Form.no_of_file_to_assign
   i = i + 1
   rst.Edit
   If rst![reinstated_1] = True Then
   rst![file_assigned_to] = Me.Form!assign_to
   rst![current_wf] = "Assigned - Reinstate Follow Up"
   rst![date_assigned] = Date
   rst![removed_from_hmda] = True
   Else
   rst![file_assigned_to] = Me.Form!assign_to
   rst![current_wf] = "Assigned - Pending Review"
   rst![date_assigned] = Date
   End If
   rst.Update
   Me.RecordsetClone.Requery
   rst.MoveNext
  Loop
  Me.filter_file_state = Null
  Me.filter_decision_status = Null
  DoCmd.Requery
rst.Close
End If
MsgBox i & " Loans have been assigned."
DoCmd.Save
Set rst = Nothing
End Sub

[June7]

Why even compare against 'number of files to assign'?

Surely the number of files to assign is the number of records in the RecordsetClone.

[alcapps]

Try to loop using
do while not rst.eof
Code goes here for the spin in records
Rst.movenext
loop

i would not use a variable I for that.

[jax1000rr]

ugh... I have been at this all day with different versions of the below code. Maybe someone can provide a better way of geting this done. What I have is a single form (I have a subform in the main so I cant use a continous form) that I need to edit the data on then loop to the next record for the number of times a user enters into the form control "no_of_files_to_assign".

If the user enters a number in the control that is more then the RecordsetCount, I need the loop to continue to the EOF and not give me the No current record error.

This is my first attempt at using RecordSets and loops.....

Here is what I have right now; the below code only loops through 1 record then stops regardless of the value in the control:

Private Sub btn_assign_files_Click()
Dim rst As Recordset
Dim x As Integer
x = 0
Set rst = Me.RecordsetClone
rst.MoveFirst
Do Until x = Me.no_of_files_to_assign
x = Me.no_of_files_to_assign
   rst.Edit
   If rst![reinstated_1] = True Then
   rst![file_assigned_to] = Me.Form!assign_to
   rst![current_wf] = "Assigned - Reinstate Follow Up"
   rst![date_assigned] = Date
   rst![removed_from_hmda] = True
   Else
   rst![file_assigned_to] = Me.Form!assign_to
   rst![current_wf] = "Assigned - Pending Review"
   rst![date_assigned] = Date
   End If
   rst.Update
   DoCmd.Requery
   'rst.MoveNext
  Loop
  Me.filter_file_state = Null
  Me.filter_decision_status = Null
  rst.Close
MsgBox x & " files have been assigned."
DoCmd.Save
Set rst = Nothing
End Sub

[jax1000rr]

ugh... I have been at this all day with different versions of the below code. Maybe someone can provide a better way of geting this done. What I have is a single form (I have a subform in the main so I cant use a continous form) that I need to edit the data on then loop to the next record for the number of times a user enters into the form control "no_of_files_to_assign".

If the user enters a number in the control that is more then the RecordsetCount, I need the loop to continue to the EOF and not give me the No current record error.

This is my first attempt at using RecordSets and loops.....

Here is what I have right now; the below code only loops through 1 record then stops regardless of the value in the control:

Private Sub btn_assign_files_Click()
Dim rst As Recordset
Dim x As Integer
x = 0
Set rst = Me.RecordsetClone
rst.MoveFirst
Do Until x = Me.no_of_files_to_assign
x = Me.no_of_files_to_assign
   rst.Edit
   If rst![reinstated_1] = True Then
   rst![file_assigned_to] = Me.Form!assign_to
   rst![current_wf] = "Assigned - Reinstate Follow Up"
   rst![date_assigned] = Date
   rst![removed_from_hmda] = True
   Else
   rst![file_assigned_to] = Me.Form!assign_to
   rst![current_wf] = "Assigned - Pending Review"
   rst![date_assigned] = Date
   End If
   rst.Update
   DoCmd.Requery
   'rst.MoveNext
  Loop
  Me.filter_file_state = Null
  Me.filter_decision_status = Null
  rst.Close
MsgBox x & " files have been assigned."
DoCmd.Save
Set rst = Nothing
End Sub

OK, so I finally got the code to loop through multiple records but I am still running into the issue that if the user enters a value larger than the RecordSetCount I get the No current record error.

Updated Code

Private Sub btn_assign_files_Click()
Dim rst As Recordset, i As Integer
Set rst = Me.RecordsetClone
i = 0
If rst.RecordCount > 0 Then
rst.MoveFirst
Do While Not i = Me.no_of_files_to_assign
i = i + 1
rst.Edit
If rst![reinstated_1] = True Then
rst![file_assigned_to] = Me.Form!assign_to
rst![current_wf] = "Assigned - Reinstate Follow Up"
rst![date_assigned] = Date
Else
rst![file_assigned_to] = Me.Form!assign_to
rst![current_wf] = "Assigned - Pending Review"
rst![date_assigned] = Date
End If
rst.Update
rst.MoveNext
Loop
End If
MsgBox i & " assigned."
rst.Close
Set rst = Nothing
DoCmd.Requery
End Sub

[jax1000rr]

Finally got it! I used the last code I posted then entered a validation rule on the forms control to not allow it to be larger than the RecordSetCount. Thanks alcapps, I used your code as a base.

[June7]

Okay, user specifies number to assign and always start with first record? Try:

Private Sub btn_assign_files_Click()
Dim rst As Recordset
Dim x As Integer
Set rst = Me.RecordsetClone
rst.MoveFirst
If Not rst.EOF
  x = 1
  Do While x <= Me.no_of_files_to_assign And Not rst.EOF
     rst.Edit
     If rst![reinstated_1] = True Then
       rst![file_assigned_to] = Me.Form!assign_to
       rst![current_wf] = "Assigned - Reinstate Follow Up"
       rst![date_assigned] = Date
       rst![removed_from_hmda] = True
     Else
       rst![file_assigned_to] = Me.Form!assign_to
       rst![current_wf] = "Assigned - Pending Review"
       rst![date_assigned] = Date
     End If
     rst.Update
     DoCmd.Requery
     rst.MoveNext
     x = x + 1
  Loop
End If
Me.filter_file_state = Null
Me.filter_decision_status = Null
rst.Close
MsgBox x & " files have been assigned."
DoCmd.Save
Set rst = Nothing
End Sub

[jax1000rr]

Okay, sounds odd that user specifies number to assign. And always start with first record? Try.

Private Sub btn_assign_files_Click()
Dim rst As Recordset
Dim x As Integer
Set rst = Me.RecordsetClone
rst.MoveFirst
x = Me.no_of_files_to_assign
Do While Not rst.EOF
  If x <= rst.RecordCount
     rst.Edit
     If rst![reinstated_1] = True Then
       rst![file_assigned_to] = Me.Form!assign_to
       rst![current_wf] = "Assigned - Reinstate Follow Up"
       rst![date_assigned] = Date
       rst![removed_from_hmda] = True
     Else
       rst![file_assigned_to] = Me.Form!assign_to
       rst![current_wf] = "Assigned - Pending Review"
       rst![date_assigned] = Date
     End If
     rst.Update
     DoCmd.Requery
  End If
  rst.MoveNext
Loop
  x = x + 1
  Me.filter_file_state = Null
  Me.filter_decision_status = Null
  rst.Close
MsgBox x & " files have been assigned."
DoCmd.Save
Set rst = Nothing
End Sub

For some reason this looped through all the records except the last one. There were 33 records and the control had 2 entered. When the loop finished there was only 1 record left.

The reason I have the user specifying the no to assign is because there will be several hundred records at any given time that need to be split up between 10 users. What other options do I have other than starting at the first record? Im anticipating the user to assign 25 files to one person then 25 to another, then maybe 5 to this one.

TIA

[June7]

Ooops. I came back and edited my post some more and changed code. Try again if you still need solution. I didn't see your post #9 before I posted.

I don't know your data nor the business process you are coding for but by using Bookmarks can specify record to start with. But I don't know if that will help or hinder.