Click here to Skip to main content
15,742,477 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.Fields("Quantity").Value = cp.Fields("Quantity").Value + 1


     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)
Exit loop                                                                                       
  End If

 End If 
End With 
End Sub
Updated 10-May-23 12:56pm

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900