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