Update multiple records in subform (1 Viewer)

[Dougalsworld]

Hoping that this is a simple problem

I have an orders form and subform, ie one order can have several product records with the following firlds
Product
Ordered
Received
At the bottom I have a command button 'ReceiveOrder'
What I want is on click of this button that the received field (that is 0 by default) will equal Ordered.
ie If I had orderd 2 and I hit the button it would change the recieved to 2 as well.
This much I have done with the following code
Private Sub ReceiveButton_Click()
Me.frmReceivingSubform.Form!QtyReceived = Me.frmReceivingSubform.Form!QtyOrdered
End Sub

This work fine if there is only one product, but if I have more than one it will only update the selected record, is there an ammendment I can do that means all the product records on that particular order will update.

I've searched the forum for the answers but can't find the answer, can anyone help,
Thanks

 

[RuralGuy]

I would probably use an Update query under the button to effect the changes with the same selection criteria as the SubForm. Then requery the SubForm.

 

[Pat Hartman]

When you refer to a subform field as you have, you are referring to the currrent record in the subform. You would need to use the subform's RecordSetClone to loop through the subform's recordset and update each record or you would need to run an update query that updates the records in the subform's recordset.

 

[Dougalsworld]

If rs("QtyReceived") <> 5 Then
.Edit
rs("QtyReceived") = [QtyOrdered]
.Update
End If

Thanks for your help guys I'm going with the recordsetclone idea at the moment. I've looked up all that I can find that relates to what I'm trying, and I've come up with code to attach to the Click() function on a button. This works fine if I put the button in the subform but not in the main form, and its because I dont know the correct structure for the field names.
My two forms are
frmReceiving, &
frmReceivingSubform

My field names are
QtyOrdered, &
QtyReceived

How would i structure these field names in a recordsetclone query in the main form (frmReceiving)

 

[Pat Hartman]

The names would be the same. You just need to refer to the correct RecordSetClone.

Dim rs AS DAO.Recordset
Set rs = Me.Yoursubformname.RecordSetClone

then rs("QtyReceived") would be fine.

 

[Dougalsworld]

Hi,
Thx for the speedy reply still having problems
I'm using the code below, like I said I want a button that automatically changes the QtyReceived for every line of an order to the same as the QTYOrdered. I'm very much a Newbie to VBA and am doing my best to teach myself as I go. I've found this forum invaluable, and most of the problems I come across I'm able to sort myself using previous posts for my information, but this ones has stumped me. When I use the code in my main form it doesn't work, keeps bringing up the debugger and highlights the first line. have I not linked it to the button correctly??
I've managed to get a variation of the code to work on the subform, but I need it on the main form...how badly have I mucked this up??


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

 

[Dougalsworld]

Should probably have said, the error message I get is:
"Compile Error"
"User defined type not defined"