rst.Edit and Update Problem

[Constantine AI]

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:

    Dim Reply As String
    Dim strSQL As String
    Dim strSQL2 As String
    Dim db As Database
    Dim rst As Recordset
 
    strSQL = "INSERT INTO stkmas ([StkShortDesc],[Width],[Depth],[Height]) SELECT [StkShortDesc],[Width],[Depth],[Height] FROM preordlin WHERE IsNull(StkID)"
    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"
 
    StkID.value = DLookup("[StkID]", "stkmas", "[StkShortDesc] = Forms![frmPreSOLine]![StkShortDesc]")
    Price.value = DLookup("[SalePrice1]", "primas", "[StkID] = Forms![frmPreSOLine]![StkID]")
 
    If IsNull(StkID) Then
        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!")
        If Reply = vbYes Then
            DoCmd.RunSQL strSQL
        Else
        End If
    Else
    End If
 
     DoCmd.SetWarnings False
 
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("SELECT * FROM preordlin")
 
    Do Until rst.EOF
        rst.Edit
        DoCmd.RunSQL strSQL2
        rst.Update
        rst.MoveNext
    Loop Until rst.EOF
    rst.Close
    Me.Requery
    Set rst = Nothing
 
    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

[ADezii]

1. The syntax that you are using is not correct, one option would be:
   

   Do While Not rst.EOF
     rst.Edit
       DoCmd.RunSQL strSQL2
     rst.Update
     rst.MoveNext
   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.

[FishVal]

I would agree with ADezii. This code will never work as you are expecting

While Not You = DavidBlaine
Wend
 

Kind regards,
Fish

[Stewart Ross]

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

[nico5038]

I would expect the strSQL2 to be depend on value(s) from the extracted row.

Something like:

    Dim Reply As String
    Dim strSQL As String
    Dim strSQL2 As String
    Dim db As Database
    Dim rst As Recordset
 
    strSQL = "INSERT INTO stkmas ([StkShortDesc],[Width],[Depth],[Height]) SELECT [StkShortDesc],[Width],[Depth],[Height] FROM preordlin WHERE IsNull(StkID)"
 
    StkID.value = DLookup("[StkID]", "stkmas", "[StkShortDesc] = Forms![frmPreSOLine]![StkShortDesc]")
    Price.value = DLookup("[SalePrice1]", "primas", "[StkID] = Forms![frmPreSOLine]![StkID]")
 
    If IsNull(StkID) Then
        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!")
        If Reply = vbYes Then
            DoCmd.RunSQL strSQL
        Else
        End If
    Else
    End If
 
     DoCmd.SetWarnings False
 
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("SELECT * FROM preordlin")
 
    Do Until rst.EOF
        rst.Edit
    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
        DoCmd.RunSQL strSQL2
        rst.Update
        rst.MoveNext
    Loop Until rst.EOF
    rst.Close
    Me.Requery
    Set rst = Nothing
 
    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)

[Constantine AI]

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?

[Constantine AI]

I would expect the strSQL2 to be depend on value(s) from the extracted row.

Something like:

    Dim Reply As String
    Dim strSQL As String
    Dim strSQL2 As String
    Dim db As Database
    Dim rst As Recordset
 
    strSQL = "INSERT INTO stkmas ([StkShortDesc],[Width],[Depth],[Height]) SELECT [StkShortDesc],[Width],[Depth],[Height] FROM preordlin WHERE IsNull(StkID)"
 
    StkID.value = DLookup("[StkID]", "stkmas", "[StkShortDesc] = Forms![frmPreSOLine]![StkShortDesc]")
    Price.value = DLookup("[SalePrice1]", "primas", "[StkID] = Forms![frmPreSOLine]![StkID]")
 
    If IsNull(StkID) Then
        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!")
        If Reply = vbYes Then
            DoCmd.RunSQL strSQL
        Else
        End If
    Else
    End If
 
     DoCmd.SetWarnings False
 
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("SELECT * FROM preordlin")
 
    Do Until rst.EOF
        rst.Edit
    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
        DoCmd.RunSQL strSQL2
        rst.Update
        rst.MoveNext
    Loop Until rst.EOF
    rst.Close
    Me.Requery
    Set rst = Nothing
 
    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!

[Constantine AI]

Thanks nico5038 i have it sorted now and the code works well, cheers.