check and modify similar item in range VBA 1

[davyre]

Hi,
Im working on a code to check whether similar value(partID) had been inserted in the column in the table "PartsList".
So basically it would check if the current partID already exist in the table. If yes (i.e exist) then update the partQty and price value for that particular part. If no (i.e the part is new/neven been inserted into the table before), then create new entry to insert the details into the table (i.e partID, partName, partQty, and price).
=====================================
partID | partName | partQty | Price
=====================================
11111 | Screw | 3 | 3
11112 | Bolt | 5 | 10
11115 | Cpr Wire | 2 | 18

Now I want to insert for example partID=11122 partName="Razor" partQty=8 Price=32. I want to check if the partID 11122 already exist in the table, in this case not. So if not then just add the new entry
=====================================
partID | partName | partQty | Price
=====================================
11111 | Screw | 3 | 3
11112 | Bolt | 5 | 10
11115 | Cpr Wire | 2 | 18
11122 | Razor | 8 | 32

If I want to insert for example partID=11111 partName="Screw" partQty=2 Price=2, check if the partID 11111 already exist, which is yes. Then it would update the table only for the partQty and the Price i.e
=====================================
partID | partName | partQty | Price
=====================================
11111 | Screw | [highlight #FCE94F]5[/highlight] | [highlight #FCE94F]5[/highlight]
11112 | Bolt | 5 | 10
11115 | Cpr Wire | 2 | 18

how can I do that? Sorry for long post, I cant describe it in good english. Any help is much appreciated. Thanks!

 

[dhookom]

I would use an update query to first update the original table with the new data based on a join of the PartID (assuming this is the primary key). You could use an unmatched query as the source for an append query to add new records.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

To add to dhookom's post, Update query can (should?) return the number of affected records, so if your Update query updates 1 (or more) record, you are done. But if the number of affected records comes back as 0, you know you don't have a record to update, so you need to Insert (append?) a new record to the table.

Have fun.

---- Andy

 

[davyre]

Thanks for the replies, but the problem I currently facing is about the IF THEN clause. I have an idea using IF THEN,

 IF newPartID=(something in the PartID column in PartsList table)
             UPDATE PartsList
             SET PartQty=PartQty+newPartQty, Price=Price+newPrice
             WHERE PartID=newPartID
       ELSE
             INSERT INTO PartsList (PartID, PartName, PartQty, Price)
             VALUES ("newPartID", "newPartName", "newPartQty", "newPrice")
       END IF

how should I write in IF condition? how can I tell VB to look the whole column and check if newPartID has the same value as some entry above? and for the UPDATE clause, can I do the SET as I said above?
Thanks!

 

[PHV]

I'd use something like this:

If newPartID = DLookup("PartID", "PartsList", "PartID=" & newPartID) Then
    strsql = "UPDATE PartsList SET PartQty=PartQty+" & newPartQty & ",Price=Price+" & newPrice _
             & " WHERE PartID=" & newPartID
Else
    strsql = "INSERT INTO PartsList (PartID,PartName,PartQty,Price) VALUES (" _
             & newPartID & ",'" & newPartName & "'," & newPartQty & "," & newPrice & ")"
End If
CurrentDb.Execute strsql, dbFailOnError

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[davyre]

Thanks PHV, but just a little bit error, my PartID and newPartID are string datatype. So when I run the code, it gave me error when the PartID is having letter i.e H11120. Where should I change the code?

 

[davyre]

and where can I use Trim function? Because I'm afraid the PartID in the table sometimes having spaces in it

 

[PHV]

If newPartID = DLookup("PartID", "PartsList", "PartID=[!]'[/!]" & newPartID [!]& "'"[/!]) Then
    strsql = "UPDATE PartsList SET PartQty=PartQty+" & newPartQty & ",Price=Price+" & newPrice _
             & " WHERE PartID=[!]'[/!]" & newPartID [!]& "'"[/!]
Else
    strsql = "INSERT INTO PartsList (PartID,PartName,PartQty,Price) VALUES [!]'[/!]" _
             & newPartID & "[!]'[/!],'" & newPartName & "'," & newPartQty & "," & newPrice & ")"
End If
CurrentDb.Execute strsql, dbFailOnError

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[davyre]

thanks!

 

[davyre]

now if I want to export it to Excel, instead of updating it to the table, I want the update occurs when it is exporting to Excel. So the table will be untouched (with similar values), but when I export it to Excel it will update etc so the Excel file wont have any similar values in it. This is because I realize that if I update the table, it will make mess to the database.

dim db as database
dim rst as recordset

set db=currentDb
set rst=db.OpenRecordset("QryPartsTableSum")
.
.
.

intLoopCount=1
with rst
      Do Until .EOF = True
        strLoopCount = Trim(Str(intLoopCount))
        
        'set if
        if activeworkbook.Worksheets("Sheet1").range("$A$7:A"&"strLoopCount).value=rst!TmpPartNumber then
         objSheet.Range("C" & strLoopCount).Value = objSheet.Range("C" & strLoopCount).Value + rst!TmpPartCost
         objSheet.Range("D" & strLoopCount).Value = objSheet.Range("D" & strLoopCount).Value + rst! [Sum Of TmpQty]
        else
         objSheet.Range("A" & strLoopCount).Value = rst!TmpPartNumber
         objSheet.Range("B" & strLoopCount).Value = rst!TmpPartDesc
         objSheet.Range("C" & strLoopCount).Value = rst!TmpPartCost
         objSheet.Range("D" & strLoopCount).Value = rst![Sum Of TmpQty]
                
        intLoopCount = intLoopCount + 1
        .MoveNext
    Loop
.
.

how do I set the IF condition? Thanks