Manipulate a specific record from different recordset

See more:

MS-Access

VBA

I have three (3) ListBoxes, wvListBox, ivListBox & cpListBox. All designed from three (3) separate Queries. wvListbox and ivListBox are designed to submit records upon a Double Click on each record, to the cpListbox. In a nutshell, cpListBox acts as a receiver for raw records sent from wvListBox and ivListBox, which act as the senders.

"Quantity" is another Field/Column for all the ListBoxes and it measures the Double Click on the records. A Double-Click on the "sender" means a decrement to the Quantity of that record by 1 and an increment by 1 in the receiver. This is if there is already a similar record existing in the receiver. Else, a new record is added with Quantity being 1.

The Primary Keys for the Senders (wvListBox & ivListBox) are of the same data type (Auto Number). Records sent from these listboxes (Senders) cannot be uniquely manipulated. The code is mistaking one for the other.

So, I had to add another Field/Column named "Storage" to achieve a Composite Key. And this seems to work fine but the code seems to recognize only the top record by displaying duplicates in the receiver. The idea to achieve the increment for the similar record, sent from the same source is not achieved.

This is how best I could expound on my situation for your kind assistance.

Note that the snippet below is from the wvListBox's Double-Click Event

What I have tried:

Private Sub wvListBox_DblClick(Cancel As Integer)

Dim InvPartsTbl As DAO.Recordset

Dim db As Database
Dim cp As Recordset
Dim wv As Recordset
Dim pStrge As String
Dim ID_wv As Integer


Set db = CurrentDB
Set cp = db.OPenRecordset("committedPartsTbl")                   'Table for ListBox cpListBox
Set wv = db.OPenRecordset("wreckedVehTbl")                       'Table for ListBox wvListBox

pStrge = "Wrecked Part"

With Me.wvListBox

 ID_wv = .column(0)                   

 If DLookup("ID", "committedPartsTbl", "PartID =" & ID_wv) = .Column(0) Then

 Do While Not cp.EOF

   If cp.Fields.Item(10).Value = pStrge And cp.Fields.Item(1) = ID_wv Then

      cp.Edit
      cp.Fields("Quantity").Value = cp.Fields("Quantity").Value + 1
      cp.Update


  Else 

     cp.AddNew
     cp.Fields("PartID").Value = .Column(0)
     cp.Fields("PartName").Value = .Column(1)
     cp.Fields("Source").Value = .Column(2) 
     cp.Fields("Quantity").Value = 1                                                                                                                                                                       
     cp.Fields("Guarantor").Value = .Column(3)                                                                                                                                                                              
     cp.Fields("Storage").Value = pStrge 
     cp.Fields("StorageDate").Value = .Column(5)
     cp.Fields("Description").Value = .Column(6)
     cp.Update
                                                                                                       
Exit loop                                                                                       
  End If
       
  cp.MoveNext
Loop

 End If 
End With 
End Sub