Looping Through Records on a Subform

[Robert Dunstan]

Hi guys

I have a bit of a problem which I hope someone can help me out with.

I have a main form (frmGoodsReceived) and a subform (frmGoodsReceivedDetailsSubform) both linked on OrderID. Basically this form allows the user to select an order, load the details of that order into the subform and enter the goods received.

What I'm trying to write is a routine that will loop the records of subform for that order and set the value quantity received field to the value of the quantity ordered field. The code has been written in the OnClick event of cmdAllReceived and this button is on the main form. The code I'm using is below:
Private Sub cmdAllReceived_Click()

Dim rst As Recordset
Dim ctlQuantity As Control
Dim ctlReceived As Control

Set rst = Forms!frmGoodsReceived!frmGoodsReceivedDetailsSubform.Form.Recordset
Set ctlQuantity = Forms!frmGoodsReceived!frmGoodsReceivedDetailsSubform.Form!txtQuantity
Set ctlReceived = Forms!frmGoodsReceived!frmGoodsReceivedDetailsSubform.Form!txtReceived


Do While Not rst.EOF And Me.OrderID = Forms!frmGoodsReceived!frmGoodsReceivedDetailsSubform.Form!OrderID
If Not IsNull(ctlQuantity) = True Then
ctlReceived.Value = ctlQuantity.Value
End If
Loop

End Sub

When I click on cmdAllReceived Access just hangs and I have to close it through Task Manager so I'm not even getting any error messages. I'm obviously doing something wrong so if someone could give me some guidance I would be extremely grateful.

 

[sambo]

You are using

Do Whil Not .EOF

Loop

But... You are never using .MoveNext
Also I never see where you use With rst

Maybe try this..

With rst

  Do While Not rst.EOF And Me.OrderID = Forms!frmGoodsReceived!frmGoodsReceivedDetailsSubf
orm.Form!OrderID 
  
    If Not IsNull(ctlQuantity) = True Then 
        ctlReceived.Value = ctlQuantity.Value 
    End If 
  .MoveNext
  Loop 

End With

If Access is hanging you w/ no options but Task Manager, try pushing [Ctrl] + Pause. This will break the code and stop it from running. You had an infinite loop, so there was never an .EOF, thus, it never stopped running.


Good Luck..

 

[sambo]

Oops..

I guess the code break command is
SHIFT + Pause
Its friday, cut me some slack

 

[Robert Dunstan]

Sambo

Well it's Monday morning and I tried your code and guess what....it works a treat!!!

Many thanks and merry christmas

Rob