Replacing items in a ListBox

[ajaysharma10]

Hi,

I have the following problem:

User has two listboxes set up in a
[LIST OF ALL ITEMS]-->[ITEMS CHOSEN] fashion.

Every time the user adds a new item to the [ITEMS CHOSEN] list, the program needs to run a check on all items in that listbox.

There is a table of "packages" in the database, where several à la carte items comprise of a "package". If the [ITEMS CHOSEN] listbox has ALL the items of a "package", have it prompt the user and if they choose YES, replace the individual items with the "package" item in the [ITEMS CHOSEN] listbox.

An à la carte item can belong to more than one package.


Thanks you so much for your help.

Using Microsoft Access 2010
Visual Basic for Applications 7.0

[jonh]

If Items and Packages are different things they shouldn't go in the same list.


Sounds like you need more lists.


This example (not tested much or refined in any way) uses a table called package with fields :
ID autonum/PK
packagename text
packageitem text


The form has 4 listsboxes :
lstAllItems - multiselect
lstItemsChosen - multiselect (this list is optional)
lstSelectedPackages - multiselect - 3 columns
lstNonPackageItems - multiselect


lstAllItems - obvious


lstItemsChosen - optional, selecting an item in lstAllItems displays it here but this list has no other function.


lstSelectedPackages - This list has 3 columns. It displays package names for any items chosen (column 1). It also displays the number of items per package (col 2) and the number of items matching that package that are selected (col 3). If the values in columns 2 and 3 don't match, i.e. all items are not selected the package can't be selected.


lstNonPackageItems - displays chosen items with selected packages filtered out.




lstSelectedPackages and lstNonPackageItems are the two you would be interested in.





Private Sub GetPackages()
lstSelectedPackages.ColumnCount = 3
lstSelectedPackages.ColumnWidths = "2cm;1cm;1cm"

sql = sql & "SELECT package.packagename, Sum(1) AS Total, b.cnt "
sql = sql & "FROM package INNER JOIN ( "
sql = sql & "SELECT a.packagename, Sum(1) AS cnt "
sql = sql & "FROM ( "
sql = sql & "SELECT packagename FROM package WHERE [packageitem] In (" & lstvalues(lstAllItems, ",", "'") & ")"
sql = sql & ") AS a "
sql = sql & "GROUP BY a.packagename "
sql = sql & ") as b "
sql = sql & "ON package.packagename = b.packagename "
sql = sql & "GROUP BY package.packagename, b.cnt "
Debug.Print sql
lstSelectedPackages.RowSource = sql
End Sub


Private Sub Form_Open(Cancel As Integer)
lstAllItems.RowSource = "select distinct packageitem from package"
End Sub


Private Sub lstAllItems_Click()
sql = sqlwhere(sql, "packageitem", lstvalues(lstAllItems, ",", "'"), True)
If Len(sql) Then sql = "select distinct packageitem from package " & sql
lstItemsChosen.RowSource = sql
GetPackages
lstSelectedPackages_Click
End Sub


Private Sub lstSelectedPackages_Click()
If lstSelectedPackages.Column(1, lstSelectedPackages.ListIndex) <> lstSelectedPackages.Column(2, lstSelectedPackages.ListIndex) Then
lstSelectedPackages.Selected(lstSelectedPackages.ListIndex) = False
Exit Sub
End If
sql = sqlwhere(sql, "packageitem", lstvalues(lstAllItems, ",", "'"), True)
sql = sqlwhere(sql, "packagename", lstvalues(lstSelectedPackages, ",", "'"), False)
If Len(sql) Then sql = "select distinct packageitem from package " & sql
lstNonPackageItems.RowSource = sql
End Sub


Private Function lstvalues(lb As ListBox, sep As String, Optional wrap As String) As String
For Each li In lb.ItemsSelected
If Len(lstvalues) Then lstvalues = lstvalues & sep
lstvalues = lstvalues & wrap & lb.ItemData(li) & wrap
Next
End Function


Private Function sqlwhere(sql, fld, lst As String, isin As Boolean) As String
If Len(lst) Then
If Len(sql) Then
sqlwhere = sql & " and [" & fld & "] " & IIf(isin, "", " not ") & " in (" & lst & ")"
Else
sqlwhere = " where [" & fld & "] " & IIf(isin, "", " not ") & " in (" & lst & ")"
End If
Else
sqlwhere = sql
End If
End Function