By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,136 Members | 1,054 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,136 IT Pros & Developers. It's quick & easy.

Depicting Fields from Rows and Updating Others via a Loop

100+
P: 129
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.

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3.           Dim db As Database
  4.           Dim rst As Recordset
  5. .
  6.           DoCmd.SetWarnings False
  7.  
  8.           DoCmd.GoToRecord , , acFirst
  9.  
  10.           Set db = CurrentDb()
  11.  
  12.           Set rst = db.OpenRecordset("SELECT * FROM preordlin")
  13.         Do
  14.  
  15.               rst.Edit
  16. .
  17.               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
  18.  
  19.               DoCmd.RunSQL strSQL
  20.  
  21.               rst!SuppNo = DLookup("[SuppNo]", "stkmas", "[StkID] = Forms![frmPreSOLine]![StkID]")
  22.  .
  23.               rst.Update
  24.  .
  25.               rst.MoveNext
  26.  .
  27.           Loop Until rst.EOF
  28.  .
  29.           rst.Close
  30.  .
  31.           Me.Requery
  32.  .
  33.           Set rst = Nothing
  34.  .
  35.            DoCmd.SetWarnings True
  36.  
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?
Aug 23 '08 #1
Share this Question
Share on Google+
1 Reply


nico5038
Expert 2.5K+
P: 3,072
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)
Aug 24 '08 #2

This discussion thread is closed

Replies have been disabled for this discussion.