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

rst.Edit and Update Problem

100+
P: 129
Hi I am trying to insert certain information into a table based on a unique id. In terms of the Data Environment, it is within Stock and BOM Details. I have enforced a Button to do this command however it only works on 1 row at a time instead of moving onto the next, which i have coded it too. Can anyone tell me why or how to fix this issue. I can't keep clicking on the different rows followed by the button.

I am using Microsoft Access 2007:

Expand|Select|Wrap|Line Numbers
  1.     Dim Reply As String
  2.     Dim strSQL As String
  3.     Dim strSQL2 As String
  4.     Dim db As Database
  5.     Dim rst As Recordset
  6.  
  7.     strSQL = "INSERT INTO stkmas ([StkShortDesc],[Width],[Depth],[Height]) SELECT [StkShortDesc],[Width],[Depth],[Height] FROM preordlin WHERE IsNull(StkID)"
  8.     strSQL2 = "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 = forms!frmPreSOLine!StkID"
  9.  
  10.     StkID.value = DLookup("[StkID]", "stkmas", "[StkShortDesc] = Forms![frmPreSOLine]![StkShortDesc]")
  11.     Price.value = DLookup("[SalePrice1]", "primas", "[StkID] = Forms![frmPreSOLine]![StkID]")
  12.  
  13.     If IsNull(StkID) Then
  14.         Reply = MsgBox("There are Stock Items present that do not belong to our existing Stock files. Would you like to ADD them?", vbYesNo, "None Existent Stock Details!")
  15.         If Reply = vbYes Then
  16.             DoCmd.RunSQL strSQL
  17.         Else
  18.         End If
  19.     Else
  20.     End If
  21.  
  22.     DoCmd.SetWarnings False
  23.  
  24.     Set db = CurrentDb()
  25.     Set rst = db.OpenRecordset("SELECT * FROM preordlin")
  26.  
  27.     Do Until rst.EOF
  28.         rst.Edit
  29.         DoCmd.RunSQL strSQL2
  30.         rst.Update
  31.         rst.MoveNext
  32.     Loop Until rst.EOF
  33.     rst.Close
  34.     Me.Requery
  35.     Set rst = Nothing
  36.  
  37.     DoCmd.SetWarnings True
With this coding i receive no error however it removes the StkShortDesc and Price from the actual fields and still only displays data for the first row. I have checked books and the internet forums, to my point of view this code should work however it doesn't. Any help would be much appreciated!

Thanks

Chris
Aug 21 '08 #1
Share this Question
Share on Google+
7 Replies


ADezii
Expert 5K+
P: 8,669
  1. The syntax that you are using is not correct, one option would be:
    Expand|Select|Wrap|Line Numbers
    1. Do While Not rst.EOF
    2.   rst.Edit
    3.     DoCmd.RunSQL strSQL2
    4.   rst.Update
    5.   rst.MoveNext
    6. Loop
  2. I have never seen this type of code syntax before where you are executing multiple Action Queries (INSERT), inserting Records into a Table, while at the same time looping through a Recordset based on the very same Table that Records are being inserted into. I honestly can't believe that the code works at all, and if it should work, I believe that it will eventually lead to major conflicts down the line.
  3. I feel as though your code needs to be drastically restructured but don't take my word on it, see what some of the other Admins/Moderators/Experts have to say on the Topic.
Aug 21 '08 #2

FishVal
Expert 2.5K+
P: 2,653
I would agree with ADezii. This code will never work as you are expecting
Expand|Select|Wrap|Line Numbers
  1. While Not You = DavidBlaine
  2. Wend
  3.  
Kind regards,
Fish
Aug 21 '08 #3

Expert Mod 2.5K+
P: 2,545
Hi. ADezii and FishVal have said much of what needs to be said here - your code is in need of considerable revision!

If you process a recordset within a loop the .Edit and .Update methods apply to what you do using the recordset's fields directly - not to what you do using separate DoCmd's to run SQL updates. The .Update is intended for field-level updates of the current record, and only the current record, within that recordset instance. Bear in mind that SQL updates can apply to many tables simultaneously, and many records within them, and you will start to see the erroneous linking of updates with recordset processing that is going on.

Each time you use OpenRecordset you open a copy of the current query or table concerned. Using external updates that bypass the recordset within its processing loop will, at the very least, generate update anomalies between what is in the loop and what you have updated using the RunSQL statements. Once a recordset has been opened the data is effectively static until you close and re-open it, unless you change field values using the recordset's own methods to do so (not by running external SQL updates).

You can use the .Addnew method to insert a new record in a recordset - but then you have to set the field values individually. It is not done using an SQL statement. Loop processing of recordsets is record-by-record; SQL inserts and updates are set-oriented. The two are very different.

I hesitate to think what will go on if values are changed the way you are doing - I have no real idea what will happen to the values read by the recordset for the remaining records in the loop, because the mixed syntax is distinctly non-standard.

-Stewart
Aug 21 '08 #4

nico5038
Expert 2.5K+
P: 3,072
I would expect the strSQL2 to be depend on value(s) from the extracted row.

Something like:


Expand|Select|Wrap|Line Numbers
  1.     Dim Reply As String
  2.     Dim strSQL As String
  3.     Dim strSQL2 As String
  4.     Dim db As Database
  5.     Dim rst As Recordset
  6.  
  7.     strSQL = "INSERT INTO stkmas ([StkShortDesc],[Width],[Depth],[Height]) SELECT [StkShortDesc],[Width],[Depth],[Height] FROM preordlin WHERE IsNull(StkID)"
  8.  
  9.     StkID.value = DLookup("[StkID]", "stkmas", "[StkShortDesc] = Forms![frmPreSOLine]![StkShortDesc]")
  10.     Price.value = DLookup("[SalePrice1]", "primas", "[StkID] = Forms![frmPreSOLine]![StkID]")
  11.  
  12.     If IsNull(StkID) Then
  13.         Reply = MsgBox("There are Stock Items present that do not belong to our existing Stock files. Would you like to ADD them?", vbYesNo, "None Existent Stock Details!")
  14.         If Reply = vbYes Then
  15.             DoCmd.RunSQL strSQL
  16.         Else
  17.         End If
  18.     Else
  19.     End If
  20.  
  21.     DoCmd.SetWarnings False
  22.  
  23.     Set db = CurrentDb()
  24.     Set rst = db.OpenRecordset("SELECT * FROM preordlin")
  25.  
  26.     Do Until rst.EOF
  27.         rst.Edit
  28.     strSQL2 = "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
  29.         DoCmd.RunSQL strSQL2
  30.         rst.Update
  31.         rst.MoveNext
  32.     Loop Until rst.EOF
  33.     rst.Close
  34.     Me.Requery
  35.     Set rst = Nothing
  36.  
  37.     DoCmd.SetWarnings True
Probably you should also move the warning message into the rst loop, or better create a checking loop before the INSERT loop.

Nic;o)
Aug 21 '08 #5

100+
P: 129
Hi all, thanks for all your views. I know my coding is bad (sucks) i didn't know of any other way to process the information. The reason why i am doing this is because i have imported a csv file which contains the top level details of a particular stock item. My client wishes to process that information and distinguish the Sub-Stock and Component Details that go with the Stock item in question and insert these details into the orderline. For example:

CSV File

StockShortDesc, Qty, Width, depth and Height

300 DL Unit,1,300,250,600

Pre-order Line

Stock ID, StockShortDesc, Qty, Width, depth and Height

201, 300 DL Unit, 1, 300, 250, 600

With the click of the button it finds all the related Sub-Stock Details and Inserts it into the Pre-Order Line, so for example:

201, 300 DL Unit (Contains)

140, BaseCarcDrawer
389, Feet
161, DrawerFrontFascia
401, DoorFasciaDrawer

And so on the 140 Sub-Stock Item contains Components as well, so by the time i have finished i have a long list of components within the orderline. My clients wants it this way as changes maybe required during the assembly line.

Table wise i have self-joins:

Stockmas table - StkBOMmas table - Stockmas table - StkCmpBOMmas table
StkID - StkID, SubStkID - StkID - SubStkId, CmpID
(One) - (Many) - (One) - (Many)

I know this seems messy however the coding for the button works, it assigns the details according and correctly, however if you click on the same StkID twice it duplicates the values, but i can stop that. It assigns the SubStock details as well correctly but again duplicates when clicked upon twice.

Is there no way i can loop this procedure to all rows of data?
Aug 21 '08 #6

100+
P: 129
I would expect the strSQL2 to be depend on value(s) from the extracted row.

Something like:


Expand|Select|Wrap|Line Numbers
  1.     Dim Reply As String
  2.     Dim strSQL As String
  3.     Dim strSQL2 As String
  4.     Dim db As Database
  5.     Dim rst As Recordset
  6.  
  7.     strSQL = "INSERT INTO stkmas ([StkShortDesc],[Width],[Depth],[Height]) SELECT [StkShortDesc],[Width],[Depth],[Height] FROM preordlin WHERE IsNull(StkID)"
  8.  
  9.     StkID.value = DLookup("[StkID]", "stkmas", "[StkShortDesc] = Forms![frmPreSOLine]![StkShortDesc]")
  10.     Price.value = DLookup("[SalePrice1]", "primas", "[StkID] = Forms![frmPreSOLine]![StkID]")
  11.  
  12.     If IsNull(StkID) Then
  13.         Reply = MsgBox("There are Stock Items present that do not belong to our existing Stock files. Would you like to ADD them?", vbYesNo, "None Existent Stock Details!")
  14.         If Reply = vbYes Then
  15.             DoCmd.RunSQL strSQL
  16.         Else
  17.         End If
  18.     Else
  19.     End If
  20.  
  21.     DoCmd.SetWarnings False
  22.  
  23.     Set db = CurrentDb()
  24.     Set rst = db.OpenRecordset("SELECT * FROM preordlin")
  25.  
  26.     Do Until rst.EOF
  27.         rst.Edit
  28.     strSQL2 = "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
  29.         DoCmd.RunSQL strSQL2
  30.         rst.Update
  31.         rst.MoveNext
  32.     Loop Until rst.EOF
  33.     rst.Close
  34.     Me.Requery
  35.     Set rst = Nothing
  36.  
  37.     DoCmd.SetWarnings True
Probably you should also move the warning message into the rst loop, or better create a checking loop before the INSERT loop.

Nic;o)
Thanks for that coding nico5038 it nearly works and it is the closest i have gotten. When i implemented this coding it does it perfectly however it changes the Unit Stock id to the last record stock id, yet it inserts all the necessary items. I wish i could show you what i mean!
Aug 21 '08 #7

100+
P: 129
Thanks nico5038 i have it sorted now and the code works well, cheers.
Aug 21 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.