update record or add record

[Gordo]

Hi,

I'm trying to add items received to my inventory table. If the item is already in the table, I just want to update the number and cost etc. If the item is not in the inventory table I want to add it. My problem is determining which item in the source table is already in the target table so I can either update of add. Can anyone tell me how to find an item number in target table by looping through the source table?

Here's the code I've written which doesn't work.


Dim I As Integer
Dim db As Database
Dim rs As Recordset
Dim rs2 As Recordset

Set rs = Nothing
Set rs2 = Nothing

Set db = CurrentDb
Set rs2 = db.OpenRecordset("Inv_Recd2")
Set rs = db.OpenRecordset("Inv_Recd")
rs.MoveFirst

' loop through table Inv_Recd looking for ItemNumbers in Inv_Recd2. If the item number
' is found then edit the record with new info. If item is not found, add it.

For I = 0 To rs.RecordCount - 1
If rs2.Fields("ItemName") = rs.Fields("ItemName") Then
rs2.Edit
rs2.Fields("Cost") = rs.Fields("Cost")
rs2.Fields("Received") = (rs2.Fields("Received") + rs.Fields("Received"))
rs2.Fields("Price") = rs.Fields("Price")
rs2.Fields("Dept") = rs.Fields("Dept")
rs2.Fields("RPrice") = rs.Fields("RPrice")
rs2.Update
Else
rs2.AddNew
rs2.Fields("ItemNum") = rs.Fields("ItemNum")
rs2.Fields("ItemName") = rs.Fields("ItemName")
rs2.Fields("Cost") = rs.Fields("Cost")
rs2.Fields("Received") = rs.Fields("Received")
rs2.Fields("Price") = rs.Fields("Price")
rs2.Fields("Dept") = rs.Fields("Dept")
rs2.Fields("RPrice") = rs.Fields("RPrice")
rs2.Update
End If
rs.MoveNext
Next I

Errhandler: MsgBox Err.Number & Err.Description

rs.Close
Set rs = Nothing
rs2.Close
Set rs2 = Nothing
db.Close
Set db = Nothing

End Sub

 

[JANR]

Just use a Update query with an Outer Join on ItemName and it will Update existing records and add new records to items not found in Inv_Recd2.

Something like this:

UPDATE Inv_Recd LEFT JOIN Inv_Recd2 ON Inv_Recd.ItemName = Inv_Recd2.ItemName 
SET Inv_Recd2.ItemNum = [Inv_Recd].[ItemNum], Inv_Recd2.ItemName = [Inv_Recd].[ItemName], Inv_Recd2.Cost = [Inv_Recd].[Cost], Inv_Recd2.Recived = [Inv_Recd2].[Recived]+[Inv_Recd].[Recived], Inv_Recd2.Price = [Inv_Recd].[Price], Inv_Recd2.Dept = [Inv_Recd].[Dept], Inv_Recd2.RPrice = [Inv_Recd].[RPrice];

Ps Test on a copy first since updates are irreversible

JR

 

[Gordo]

Hi JR,

Wow, I'm surprised that the update will add records as well as update existing records. This makes my code significantly simpler. Thanks very much for you suggestion.

Gordo

 

[Gordo]

Hi HR,

I got this to work on the access tables in a test, but now when I try to do it to a SQL Server database, I get "ODBC call failed". I haven't a clue why, I can read the file which is linked to my db. Do I need special permission to write to it?

Gordo

 

[JANR]

I have never used SQL Server, but if I where to guess is if ItemNum is the Primary key in your SQL Server table and you are not permitted to change it, try and remove

Inv_Recd2.itemNum = Inv_Recd.ItemNum

From the SET-clause in the update query

Just a thought, others with more knowlage of SQL Server could jump in.

JR

 

[willknapp]

The first thing I noticed is that, while you are looping through the records in rs, you are not looping through the records in rs2. For this reason, you're only comparing the rs!ItemNumber to the Item Number field of the first record in rs2. This could be the source of your ODBC error - your code will try to add a new record to rs2 if the first record doesn't match the current record in rs. If Inv_Recd2 doesn't allow duplicates in the "ItemName" field, you'd get an error if you tried to add the item from rs and the ItemName existed somewhere in rs2 other than the first record.

To avoid this, you need to update your code to loop through every record in rs2 before moving to the next record in rs. This could be very time consuming, though, depending on the number of records you're dealing with. Also, the RecordCount property of a recordset isn't wholly reliable until you've moved to the end of the recordset, so you should only use that as an index for a "For...Next" loop if you add "rs.MoveLast" just before "rs.MoveFirst". Alternatively, you can use a "Do...Loop Until rs.EOF" instead of the "For...Next" loop and save yourself that hassle.

The update query would be the best route, but if that's giving you issues for some reason, I would try using a DLookup on Inv_Recd2 using the ItemName value in rs, the update or add as needed:

Set db = CurrentDb
Set rs = db.OpenRecordset("Inv_Recd")
rs.MoveFirst
 
' loop through table Inv_Recd looking for ItemNumbers in Inv_Recd2. If the item number
' is found then edit the record with new info. If item is not found, add it.
 
Do
    If isnull(Dlookup("ItemName", "Inv_Recd2", "ItemName = '" & rs!ItemName & "'")) then
        set rs2 = db.OpenRecordset("Inv_Recd2")
        With rs2
            .AddNew
            .Fields("ItemNum") = rs.Fields("ItemNum")
            .Fields("ItemName") = rs.Fields("ItemName")
            .Fields("Cost") = rs.Fields("Cost")
            .Fields("Received") = rs.Fields("Received")
            .Fields("Price") = rs.Fields("Price")
            .Fields("Dept") = rs.Fields("Dept")
            .Fields("RPrice") = rs.Fields("RPrice")
            .Update
            .Close
        End With
        Set rs2 = nothing
    Else
        set rs2 = db.OpenRecordset("SELECT * from Inv_recd2 WHERE [ItemName] = '" & rs!ItemName & "'")
        rs2.MoveFirst
        With rs2
            .Edit
            .Fields("Cost") = rs.Fields("Cost")
            .Fields("Received") = (rs2.Fields("Received") + rs.Fields("Received"))
            .Fields("Price") = rs.Fields("Price")
            .Fields("Dept") = rs.Fields("Dept")
            .Fields("RPrice") = rs.Fields("RPrice")
            .Update
            .Close
        End With
        Set rs2 = nothing
    End If
    rs.MoveNext
Loop Until rs.EOF

Note, depending on the number of records and which fields are indexed, it may be faster to create rs2 based on the actual table on the outset (rather than the SQL statement with the WHERE clause) and use the FindFirst method instead of creating a new recordset each time, so you should consider trying each if the procedure seems to be taking longer than it should.

As JR pointed out, though, you will have issues adding a new record if the ItemNum is an autonumber field (or the SQL Server equivalent). If it's not, check with the SQL DBA - they ought to be able to run a trace that would provide a more detailed description as to why you're getting the error. More than likely, it has to do with trying to alter a primary key of the Inv_Recd2, which it appears "ItemNum" is in this case. If it's not, and "ItemNum" is unique to "ItemName", then you need to normalize your tables, creating a table dedicated solely Items, where "ItemNum" is the Primary Key of that table. In this case, you'd then want to remove "ItemName" from your inventory tables and only include "ItemNum".