Solved Trying to update import table from a query (1 Viewer)

[StuartWB]

I have inherited an old database that I am trying to automate, I receive data from a subcontractor to which I have created a temp table. I have written a query to get me the Description and serial numbers of the sub assemblies that are fitted to the product approx 50 in total code below.

[SQL]
SELECT Product.Desc, Temp.Ser
FROM Temp INNER JOIN Product ON Temp.MRI = Product.MRI
GROUP BY Product.Desc, Temp.Ser, Product.MRI;

[Query Output]
Desc Ser
Sub Assy 1 1122
Sub Assy 2 2233
Sub Assy 3 3344
Sub Assy 4 4455

What I need to do is to update the import table which is in the following format and

Import Table
Product Sub Assy1 Sub Assy2 Sub Assy3 Sub Assy4
Sn 1234 Sn 1122 Sn 2233 Sn 3344 Sn 4455

The product serial number is already defined when selecting the import, I just need to import all the sub assys

I tried looping through both as recordsets but I kept getting an error item not found in collection

Dim fld As Fields
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("Product", dbOpenDynaset)
Set rst2 = db.OpenRecordset("Import", dbOpenDynaset)
Dim Int1 As Integer
Dim Int2 As Integer
Int1 = 1
Int2 = 1

rst.MoveFirst
Do Until rst.EOF
rst2.MoveFirst
Do Until rst2.EOF
If rst![Desc] = rst2![Desc] Then
rst2.Edit
rst2![Ser] = rst![Ser]
rst2.Update
End If
rst2.MoveNext
Int1 = Int1 + 1
Loop
rst.MoveNext
Int2 = Int2 + 1
Loop
rst.Close
rst2.Close

Set rst = Nothing
Set rst2 = Nothing

MsgBox "Finised"



Any help would be really appreciated

 

[Gasman]

You appear to be using the field names from the query, which do not match the import table.?
Walk through the code line by line to see where the error is. I think it will be the first comparison.?
Not sure the logic is correct as well as you are moving first within a loop, but one thing at a time.

The structure of the import table does not look correct either.? What happens if you get yet another sub assembly.?

 

[StuartWB]

Hi Gasman thanks for the reply I will have a look, may be i need to be more specific in that i need to to loop through the rows in the query results and when the description us equal to the input table column name enter the serial number

 

[Gasman]

You do not have any product desc in the query though?
Also as you move to another record in the query output, you need to move to the next field in the product table and do so until the product number/desc changes.? (sideways)

I appreciate you inherited this, but problems like this are only going to multiply and you are going to try and apply workarounds like this.

 

[StuartWB]

Having stepped through slowly and taken your advice Gasman i am now 90% of the way there, i have 2 loops that work correctly. just need to finish off, when the loops match.

 

[Gasman]

Create a variable to store the 'previous' key field.
Compare fields after your MoveNext

I tend to also have a boolean varaiable set eg: blnSameClient and set it to True or False and Loop while true.
Here I actually want the same transactions for a client in a single email.

    Do While Not rs.EOF
        ' Set flag and field to check
        blnSameClientType = True
        strClientType = rs!Client & rs!TranType
...
...
            rs.MoveNext
            ' Has client or tran type changed?
            If Not rs.EOF Then
                If strClientType = rs!Client & rs!TranType Then
                    blnSameClientType = True
                Else
                    blnSameClientType = False
                End If
            Else
                blnSameClientType = False
            End If
            ' Increment the counter
            intTransactions = intTransactions + 1
        Loop                                     ' End blnClientType loop

HTH

 

[StuartWB]

sorry not replied sooner but i have been away. this part now seems to be ok,