Trying to show a list of items that aren't already in a table

[bigalpha]

I have a list box that allows multiple selections [Inventory]. I also have a combo box that has multiple selections [Shows].

Right now, user selects from list box and from a combo box and clicks a button. On button click, the items from the list box are associated with the PK from the combo and stored in a junction table. This allows me to quickly associate many inventory items to one show.

I realized that there I currently have no way to prevent duplicate Inventory+show records in the junction table besides having a composite key. This would be fine except no records get inserted into the junction table if there's a duplicate entry.

Ideally, I think that the user should select from the combo box [Shows]. This should narrow down what shows up in the list box [Inventory] in a way that Inventory items already associated with the show are not displayed.

If I have 10 Inventory items and Inventory items 1-5 are already associated with Show 1; after I select the combo box, the list box only displays Inventory items 6-10.

Here's the associated code

Option Compare Database
Option Explicit



Private Sub cmdAddRecords_Click()
  Dim strSQL        As String
  Dim db            As DAO.Database
  Dim rs            As DAO.Recordset
  Dim ctl           As Control
  Dim varItem       As Variant

  On Error GoTo ErrorHandler

  Set db = CurrentDb()
  Set rs = db.OpenRecordset("tblShowLink", dbOpenDynaset, dbAppendOnly)

  'make sure a selection has been made
  If Me.lstItem.ItemsSelected.Count = 0 Then
    MsgBox "Must select at least 1 product"
    Exit Sub
  End If
  
  If IsNull(Me!cboShow) Then
  MsgBox "Select a show"
  Exit Sub
  End If

'  If Not IsNumeric(Me.txtOtherValue) Then
'    MsgBox "Must enter numeric Other Value"
'    Exit Sub
'  End If

  'add selected value(s) to table
  Set ctl = Me.lstItem
  For Each varItem In ctl.ItemsSelected
    rs.AddNew
    rs!InventoryLinkFK = ctl.ItemData(varItem)
    rs!ShowFk = Me.cboShow
    rs.Update
  Next varItem
  
  MsgBox "These items have been added to the show"

ExitHandler:
  Set rs = Nothing
  Set db = Nothing
  Exit Sub

ErrorHandler:
  Select Case Err
    Case Else
      MsgBox Err.Description
      DoCmd.Hourglass False
      Resume ExitHandler
  End Select
End Sub

 

[vbaInet]

I'm hoping that your inventory list table has a numeric ID that uniquely identifies each inventory? If it does then Option 1 is the way to go, if it doesn't then it would be Option 2.

1. Create a query with that includes both tables and drop the field that links both tables and the ID field in the Inventories table
2. Sort by the inventory ID, and save and close this query.

Option 1:
3. Create a recordset based on the query in (2) and set a WHERE clause that states the ShowsID = ShowsComboBox (pseudocode). And I'm guessing that the bound field in the combo box is the ShowID. Open your recordset.
4. You will perform this logic for each record in the recordset:

If rs!InventoryID < Me.lstInventory Then
    rs.MoveNext
ElseIf rs!InventoryID > Me.lstInventory Then
    ... deselect selected item in listbox ...
    ... move to next item in listbox ...
Else   ' they are equal and hence a match
    rs.MoveNext
    ... move to next item in listbox ...
End If

5. After the loop completes, you will then:

... Move to next item in listbox (if there are more items in ItemsSelected) ...
... Deselect everything else afterwards ...

Option 2:
3. Create a Dictionary object of the inventory IDs in the recordset
4. Loop through the ItemsSelected collection of the listbox and for each item perform an Exists check against the Dictionary.
5. The logic would be (pseudocode again):

If inventories_dictionary.Exists(Me.lstInventory) = True Then
    inventories_dictionary.Remove(Me.lstInventory)  ' pop the item to reduce the size of the dictionary
Else
    ... deselect selected item in listbox ...
End If

 

[bigalpha]

I'm hoping that your inventory list table has a numeric ID that uniquely identifies each inventory? If it does then Option 1 is the way to go, if it doesn't then it would be Option 2.

1. Create a query with that includes both tables and drop the field that links both tables and the ID field in the Inventories table
2. Sort by the inventory ID, and save and close this query.

Option 1:
3. Create a recordset based on the query in (2) and set a WHERE clause that states the ShowsID = ShowsComboBox (pseudocode). And I'm guessing that the bound field in the combo box is the ShowID. Open your recordset.
4. You will perform this logic for each record in the recordset:

If rs!InventoryID < Me.lstInventory Then
    rs.MoveNext
ElseIf rs!InventoryID > Me.lstInventory Then
    ... deselect selected item in listbox ...
    ... move to next item in listbox ...
Else   ' they are equal and hence a match
    rs.MoveNext
    ... move to next item in listbox ...
End If

5. After the loop completes, you will then:

... Move to next item in listbox (if there are more items in ItemsSelected) ...
... Deselect everything else afterwards ...

Option 2:
3. Create a Dictionary object of the inventory IDs in the recordset
4. Loop through the ItemsSelected collection of the listbox and for each item perform an Exists check against the Dictionary.
5. The logic would be (pseudocode again):

If inventories_dictionary.Exists(Me.lstInventory) = True Then
    inventories_dictionary.Remove(Me.lstInventory)  ' pop the item to reduce the size of the dictionary
Else
    ... deselect selected item in listbox ...
End If

Sorry for the delayed reply.

Yes, all my tables have their own unique ID's so looks like option 1 is the path forward. Yes the bound column of the combo box is the Shows ID.

Not extremely familiar with this set up so I have some extra research to do. Thanks for your knowledge. If I can manage to work it out, I'll come back.

 

[vbaInet]

See how you get on and ask questions as you go along.

 

[bigalpha]

I think I'm missing a step somewhere.

I have my listbox rowsource set to show all inventory.
I set up a query that shows the inventory assigned to each Show (this is filtered based on combo box selection.)

In your notes in the code, you say to deselect the selected item in listbox; nothing is selected by default so what exactly am I unselecting?

This is what I have right now. It runs on 'after update' of the combo box and it generates a "Invalid use of property" error on me.lstItem.itemsSelected = False line.

Private Sub cboShow_AfterUpdate()

Dim rs As dao.Recordset
Dim db As dao.Database
Set db = CurrentDb()
Set rs = db.OpenRecordset("qryAddShowFiltered")

    
    If rs!InventoryLinkPK < Me.lstItem Then
        rs.MoveNext
    ElseIf rs!InventoryLinkPK > Me.lstItem Then
        Me.lstItem.ItemsSelected = False
        rs.MoveNext
    Else
        rs.MoveNext
    End If

    rs.MoveNext
    Me.lstItem.ItemsSelected = False



rs.Close
Set rs = Nothing
Set db = Nothing


End Sub

 

[vbaInet]

Ok, you will be working with two "collections", the recordset and ItemsSelected. You need to loop through the recordset and perform your check. ItemsSelected requires an index. Have a look at the help files.

By the way, you can use both options... it just depends which one you find easier.

 

[jdraw]

Further to vbaInet's comments, here is a tutorial from DataPig that shows quite a simple technique when things are in 1 list and not another. Can move back and forth quite easily.

It's good to have this in your "toolkit", even if it isn't applicable to your current post.

Good luck.

 

[vbaInet]

Little caveat with the method explained in the video, it won't work in a multi-user environment. For this sort of thing, it's better to use a Value List listbox and not one that's tied to a Record Source and then use the AddItem and RemoveItem methods accordingly.

Still worth looking at though!

 

[bigalpha]

Thanks, that's definitely something worth keeping for future use. I do like the idea of having the dual boxes for end user use because I think it's easier to manipulate the data.

In my case, though, that won't work as easily since I'm recording new records in a junction table, right? I suppose I could do that using two different queries for the 'add' and 'remove' buttons (append / delete).

Still been working on this for a long time today and haven't made progress. Going to take a look at this again tomorrow.

 

[vbaInet]

Untested/un-compiled:

    Dim db          As DAO.Database
    Dim lngIndex    As Long
    Dim lngSelIndex As Long
    Dim lngSelValue As Long
    Dim x           As Long
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("qryAddShowFiltered")
       
    With rs
        Do While Not .EOF
            lngSelIndex = Me.lstItem.ItemsSelected(lngIndex)
            lngSelValue = Me.lstItem.ItemData(lngSelIndex)
            
            If !InventoryLinkPK < lngSelValue Then
                .MoveNext
            ElseIf !InventoryLinkPK > lngSelValue Then
                Me.lstItem.Selected(lngSelIndex) = False
                .MoveNext
                lngIndex = lngIndex + 1
            Else
                .MoveNext
                lngIndex = lngIndex + 1
            End If
        Loop
    End With
    
    ' Deselect the others (if applicable)
    With Me.lstItem
        For x = lngIndex To .ItemsSelected.Count
            .Selected(.ItemsSelected(lngIndex)) = False
        Next
    End With
    
    rs.Close
    Set rs = Nothing
    Set db = Nothing

 

[bigalpha]

Thanks so much - I had part of that down but wouldn't have gotten the rest of the more intricate parts of that code. Your time is much appreciated!

 

[vbaInet]

Are you up and running?

 

[bigalpha]

Yeah, that did the trick!

 

[vbaInet]

Good !