Loop Problem

[JDPrestige]

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?

[weekend00]

you recordset rst has only one row because you have the condition:

WHERE (((qryContractAdbriMasonry.LoadLineID)=" & Forms!frmTest!
subfrmTestDetail.Form.LoadLineID & "));",

the LoadLineID is the value in current row of the subform, so your code only update this row in the table.

if you want to update the whole table, just remove the where clause.

[NTC]

- or - approach it as a database task rather than a programmatic task. By that I mean - make & trigger an UpdateQuery rather than via a loop method.

[JDPrestige]

OK, i've removed the WHERE clause but the DLOOKUP doesn't fire correctly. It appears that whatever record has focus the DLOOKUP looks up that particular value & then updates all the rows in the recordset with the SAME value. I don't get this!! Am i missing something?