Depicting Fields from Rows and Updating Others via a Loop

[Constantine AI]

I was wondering if you could give me some advice on another issue. I have this code for inserting more details based on the StkID.

Dim strSQL As String
 
          Dim db As Database
          Dim rst As Recordset
.
          DoCmd.SetWarnings False
 
          DoCmd.GoToRecord , , acFirst
 
          Set db = CurrentDb()
 
          Set rst = db.OpenRecordset("SELECT * FROM preordlin")
        Do
 
              rst.Edit
.
              strSQL = "INSERT INTO preordlin ([OrderNo],[StkID],[StkShortDesc],[Width],[Depth],[Height],[Qty]) SELECT forms!frmCustomerOrderForm!sfrmSOHeader!OrderNo AS OrderNo,[SubStkID],[stkmas.StkShortDesc],preordlin.Width,preordlin.Depth,preordlin.Height,  [Qty] FROM (stkbommas INNER JOIN stkmas ON stkbommas.substkID = stkmas.StkID) WHERE stkbommas.StkID =" & rst!StkID
 
              DoCmd.RunSQL strSQL
 
              rst!SuppNo = DLookup("[SuppNo]", "stkmas", "[StkID] = Forms![frmPreSOLine]![StkID]")
 .
              rst.Update
 .
              rst.MoveNext
 .
          Loop Until rst.EOF
 .
          rst.Close
 .
          Me.Requery
 .
          Set rst = Nothing
 .
           DoCmd.SetWarnings True
 

This code works almost perfectly, however the width, depth and height fields stay fixed to the first record and not change based on the rows. I have just tried changing the code from "preordlin.Width,preordlin.Depth,preordlin.Hei ght" to this "rst!Width,rst!Depth,rst!Height" unfortunately i receive another error asking me to input the fields for rst!Width,rst!Depth,rst!Height. Which works when i input the values in each field for each row. But i want it to establish itself what the values are for each row.

I have changed the code to;

strSQL = "INSERT INTO preordlin ([OrderNo],[StkID],[StkShortDesc],[Width],[Depth],[Height],[Qty]) SELECT forms!frmCustomerOrderForm!sfrmSOHeader!OrderNo AS OrderNo,[SubStkID],[stkmas.StkShortDesc],[preordlin.Width],[preordlin.Depth],[preordlin.Height],[Qty] FROM (stkbommas INNER JOIN stkmas ON stkbommas.substkID = stkmas.StkID) WHERE stkbommas.StkID =" & rst!StkID

This works but only puts values in all the fields based on the first row not changing the fields when the second row has focus within the Loop.

Do you have any advice you could give me please?

[nico5038]

Best to keep your question located in one thread.
It might be confusing for the other experts (and you) when the same problem is posted twice.

I'll close this one for you.

Nic;o)