Help with recordsets (1 Viewer)

[aron.ridgway]

Hi, I need to loop through and edit a subform.

The subform is for receiving an order, i want to run a receive all with a button click. So i have a query that works out what is remaining, i then want to update the qty in the subform with the remaining qty.

I have tried several ways with no joy, i need it to find the receivedetailPK in a temp table that stores the latest qty.

So it will loop through the subform, find the related remaining qty in the table and update the qty in the subform and then move to the next record.

I have tried an update query using the ReceiveDetailPK as a criteria, which works but only works on the 1st line.

any help would be great!

 

[BlueIshDan]

Please keep in mind that we have no clue what your system is.
Could you try and explain this in more detail, provide code of what you have now, and narrow down to what it is that you need.


Regards,
BlueIshDan

 

[aron.ridgway]

i will try! i have the following code that makes all quantities 0 before running any other code. This is basically what i want to do but rather than put a zero. i want to basically lookup the remaining qty for each record and update the record.

I'm just not sure how to put that into code

Private Sub cmdRecieveAll_Click()
Dim AreYouSure As String

AreYouSure = MsgBox("You are about to Receive All of Order" & txtOrderNumber.Value & " , Are you Sure?, The whole order and it's Quantities will be updated?", vbYesNo, "Receive All?")
If (AreYouSure = vbYes) Then
With Forms!frmReceive!sfrmReceiveDetailEntry.Form.RecordsetClone
Do While Not .EOF
.Edit
!Qty = 0
.Update
.MoveNext
Loop
End With

 

[BlueIshDan]

You want to check the value of !Qty before you set it to 0?

 

[aron.ridgway]

nope i want it to find the remaining qty for each record in a table called tblQtySoFarTEMP( this table keeps a running total of the total qty for each item.

As for example an order with the qty of 50, could already been received 2 times, 1st time = 3, 2nd time = 10.

So in tblQtySoFarTemp would be 37 remaining. This is the qty i want to input into the subform, so its then classed as fully received.

 

[BlueIshDan]

how do you link these two tables? by id or order name or what?
I don't feel I have enough technical information to help you. Just vague information about your idea.

 

[aron.ridgway]

The tables are not linked as its only a TempTable, as every time the query runs it replaces the table. But the ReceiveDetailPK are in both tables, is that any help?

 

[BlueIshDan]

MSgBox DLookup("qty", [table you want data from], "ReceiveDetailPK = " & !ReceiveDetailPK)

Just a wild guess due to my lack of understanding.

Replace [table you want data from] with the table you want your quantity from.
If that table's qty field is not named the same, update the first parameter to match it.

 

[aron.ridgway]

I appreciate your help, i have used your idea to create the bellow code, The code runs with no errors, but it's only putting zero's in table? so it runs a query to make sure the latest vales are in the temp table. And them im trying to lookup the value from the table?

DoCmd.OpenQuery "qryQuantitySoFar"

With Forms!frmReceive!sfrmReceiveDetailEntry.Form.RecordsetClone

Do While Not .EOF
qtyValue = DLookup("RemainingQty", [tblQtySoFarTEMP], "OrderDetailPK = " & !OrderDetailFK)
!Qty = qtyValue
.MoveNext
Loop
End With