Hi All,
I have a form "frmTest" & subform "subfrmTestDetail". The subform
"subfrmTestDetail" displays the recordset. I have a cmd button on "frmTest"
with the following code:
Dim rst As DAO.Recordset
Dim lngLoadLineID As Long
Set rst = CurrentDb.OpenRecordset("SELECT [LoadLineID],[EntryDate],
[DeliveryDate],[Yard],[JDNo],[Plant],[OrderID],[Client],[Street],[Suburb],
[AllocationSuburbOrder],[AreaKlm],[Pallets],[TimberSisFlexi],[Brick],
[BrickType],[UnderMin],[Tile],[Apex],[Ridge],[Spoon],[Qty],[Delivered],
[PickupNumber],[DocketNo],[InterstateCarrierID],[TruckType],[Driver],[TruckNo]
,[Rego],[Mobile],[SpecialReq],[Suby],[MinQty],[Code],[PTonne],[LoadCost],
[JDRate],[SubRate],[JDPayment],[SubPayment],[Balance],[ContractID],[Confirmed]
,[fosUserName],[AmountPaid1],[AmountPaid2],[AmountPaidBalance],[Credits],
[OutstandingBalance],[DeliverySlot],[DeliveryMethod],[PickupFrom],
[DeliveredTo],[DriverID],[DayApproved],[CheckerApproved],[ApprovedDate],
[ApprovedBy],[WorkSortOrder],[PaymentCode],[PricePeriod],[Builder],[JobNo],
[Invoiced],[Postcode],[LoadBoxNo],[Allocated],[Abbrev],[Basket],
[SpecialComments],[SpecialComments2],[SpecialComments3],DriverNo,
ConfirmedLoad,[CommentAdded],[ContactName],[ContactNumber] FROM
qryContractAdBriMasonry " _
& "WHERE (((qryContractAdbriMasonry.LoadLineID)=" & Forms!frmTest!
subfrmTestDetail.Form.LoadLineID & "));", dbOpenDynaset, dbSeeChanges)
With rst
rst.MoveFirst
Do Until rst.EOF
.Edit
lngLoadLineID = ![LoadLineID]
![JDRate] = DLookup("[JDRate]", "qryContractRatesAdbri1", "[code]
= " & Chr(34) & Forms!frmTest!subfrmTestDetail.Form.Code & Chr(34))
![SubRate] = DLookup("[SubyRate]", "qryContractRatesAdbri1", "
[code]= " & Chr(34) & Forms!frmTest!subfrmTestDetail.Form.Code & Chr(34))
!MinQty = 3
!JDPayment = !JDRate * !MinQty
!SubPayment = !SubRate * !MinQty
!Balance = !JDPayment - !SubPayment
.Update
rst.MoveNext
If rst.EOF Then
Exit Do
End If
Loop
End With
Forms!frmTest!subfrmTestDetail.Requery
'------------------------------------------ end code ---------------------
The idea is for the code to loop all records displayed in subform & update.
It only updates the very first record & does not loop the entire recordset.
Can anyone see where i've gone wrong & why it isn't looping all records?