CODEWITHSUNDEEP
vbaformsms-accesscheckbox
Jaitnium
Jaitnium

Reputation: 650

MS Access: Looping through subform records causes Error '3021'

I have a main form and a subform that displays several records. When a checkbox is checked on the main form, I want all "BoxLblTime" and "Material Arrived" fields on the subform to be updated. This is the vba code that is executed when the checkbox is clicked:

Private Sub MaterialArrived_chkbx_Click()
   Dim temp As Variant
   Dim tempString As String
   Dim ctl As Control

   'If checkbox is checked
   If Forms("JOBS Form").Controls("MaterialArrived_chkbx").Value < 0 Then

      Dim rs As Object
      'Get records of subform
      Set rs = Forms("JOBS Form").[Order Form].Form.Recordset
      'Loop until end
      Do While Not rs.EOF
        rs.Edit
        'Update the two fields
        rs![Material arrived] = True
        rs!BoxLblTime = Now()
        rs.Update
      rs.MoveNext
      Loop
      Set rs = Nothing   
   End If
End Sub

There is some unusual behavior when using this code:

1) When the checkbox on the main form is checked, the two fields are updated in the subform. But if I uncheck the checkbox on the subform and then recheck the main form checkbox, the subform checkbox stays unchecked.

2) When the checkbox on the main form is checked, the two fields are updated. But if I uncheck the checkbox on the subform, move to a new set of subform records (next or back) and then check the main form checkbox, I get the error: '3021' No current record.

Why is this unusual behavior happening?

EDIT: Here is my code using the update query approach:

Private Sub MaterialArrived_chkbx_Click()
   If Forms("JOBS Form").Controls("MaterialArrived_chkbx").Value < 0 Then

      With CurrentDb().QueryDefs("Update Orders")
         .Parameters("[Material Arrived]").Value = True
         .Parameters("[BoxLblTime]").Value = Now()
         .Execute dbFailOnError
      End With
      Forms("JOBS Form").Form.Requery
   End If
End Sub

But I'm getting "Item not found in collection" error.

Upvotes: 0

Views: 566

Answers (1)

Kostas K.
Kostas K.

Reputation: 8518

I would suggest an alternative approach.

Create an update query and pass a Boolean parameter indicating the material arrived value.

'Call update query
Private Sub MaterialArrived_chkbx_Click()
    With CurrentDb().QueryDefs("Update Orders")
        .Parameters("[prmMaterialArrived]").Value = Me.MaterialArrived_chkbx.Value
        .Parameters("[prmID]").Value = Me!ID
        .Execute dbFailOnError
    End With
    Me.[Order Form].Form.Requery
End Sub


'SQL
PARAMETERS [prmMaterialArrived] Bit, [prmID] Long;
UPDATE T
SET T.[Material arrived] = [prmMaterialArrived], T.BoxLblTime = Now()
WHERE (((T.ID)=[prmID]));

Upvotes: 1

Related Questions