RecordSetClone coding error on form

[Dougalsworld]

Hello

below is the code I am using to update multiple records on a sub form so that QtyReceived=QtyOrdered when you click the ReceiveButton, now my understanding of VBA is a little limited and the code I'm using won't work in my main form "frmReceiving" only in the subform "frmReceivingSubform" can anyone see what I'm doing wrong?

Private Sub ReceiveButton_Click()
Dim rs As DAO.Recordset

Set rs = Me.frmReceivingSubform.RecordsetClone

With rs

.MoveFirst

Do While Not .EOF
If rs("QtyReceived") = 0 Then
.Edit
rs("QtyReceived") = [QtyOrdered]
.Update
End If
.MoveNext
Loop

.Close

End With

Set rs = Nothing
End Sub

Thanks

 

[ChrisO]

Two possible methods you could try: -

Private Sub SQLUpdate_Click()

    CurrentDb.Execute " UPDATE " & Me.frmReceivingSubform.Form.RecordsetClone.Name & _
                      " SET QtyReceived = QtyOrdered" & _
                      " WHERE QtyReceived = 0"
   
End Sub

[color=green]'OR…[/color]

Private Sub DAOUpdate_Click()
    
    With Me.frmReceivingSubform.Form.RecordsetClone
        If (.RecordCount) Then
            .MoveFirst
            Do While Not .EOF
                If !QtyReceived = 0 Then
                    .Edit
                    !QtyReceived = !QtyOrdered
                    .Update
                End If
                .MoveNext
            Loop
        End If
        .Close
    End With
    
End Sub

Hope that helps.

Regards,
Chris.

 

[Dougalsworld]

Cheers Chris

Second code worked a treat,
many thanks

 

[Descecrator]

THNX Chris!!! You're my man!