Have multiselect listbox select unique values

[Skip Bisconer]

I want to be able to open a query or table ( I haven't decided which yet) from a multiselect listbox list using part number vendor three letter codes. As many of our items are purchased through a wholesaler the buyer must analyse several vendors products to generate a purchase order.

Unfortunately there are many part numbers with the same vendor code and I need the listbox to only display one selection per vendor and have the table or query open with only those items selected otherwise I get 10 K items to wade through and my attempts at this have only produced a single line item from my selection. I don't know if this can be.

I would appreciate some ideas and possible code to make this or something similar to happen. i have only delt with a Multiselect list box one and my memory is fuzzy on the code. Below is a representation of my table

tblPurchaseAnalysisLoc1

Part Description VCode
ALR70141850 ELECTRODE,7014 1/8 50# ALR
ALRALR70181850 ELECTRODE 1/8 7018 ALR
ALRALR701833250 ELECTRODE 7018 3/32 ALR
ALRAMESC2FNNP SAFETY CAP ACETYLENE FINE ALR
AMEANCFL5 SOAPSTONE,FLAT 3/16X1/2X5(1GR) AME
ANCANT6114 CART, SGL CYL 4 WHEEL SLD WH ANC
ANTBDMMHSS MED MARKING SOAPSTONE HOLDER ANT
BDMBDMTLSS THIN LINE SOAPSTONE BDM
BDMBER0745 PENCIL,RED BEROL VERITHIN BDM

 

[jal]

I'm still not clear on what you want to show in your listbox. Given the data sample above, what will your listbox look like ideally - you said "one selection per vendor". Did you mean this:

Part Description VCode
ALR70141850 ELECTRODE,7014 1/8 50# ALR
AMEANCFL5 SOAPSTONE,FLAT 3/16X1/2X5(1GR) AME
ANCANT6114 CART, SGL CYL 4 WHEEL SLD WH ANC
ANTBDMMHSS MED MARKING SOAPSTONE HOLDER ANT
BDMBDMTLSS THIN LINE SOAPSTONE BDM


That wouldn't seem to make sense as it would mean that the buyer couldn't see all the parts and part numbers for a particular vendor - he would only see one of them.

Anywho if that's what you want, you could populate your listbox like this:

listbox1.RowSource = "SELECT FIRST(Part), FIRST(Description), VCode FROM tblPurchaseAnalysisLoc1 GROUP BY Vcode"

Once the user has selected multiple items from the listbox you could build an SQL string based on his selections. Roughly something like this:

Dim i as Long, rowNo as Long
dim SQL as string
SQL = "SELECT * FROM tblPurchaseAnalysisLoc1 WHERE "
For i = 0 to listbox1.ItemsSelected.Count -1
rowNo = listbox1.ItemsSelected(i)
SQL = SQL & " PartNo = '" & listbox1.Column(0, rowNo) & "' OR "
Next i
Me.Subform1.Form.RecordSource = SQL

 

[Skip Bisconer]

The buyer must see all the part numbers for each vendor code. I would like to have the list box only show the unique vendor code. Something like what you get with the filter option in a table or query where you can see just one of every vendor code and the user checks just the ones they need to analyze. In other words I would like to have the Table Filter capabilities from a Continuous Form.

I want to control what the user can change so I wish to keep them away from the tables. I hope I am making sense. Or maybe I am barking up the wrong tree, as I seem to do often.

 

[jal]

I was hoping you would type out EXACTLY WHAT YOU WANT TO SEE IN YOUR LISTBOX. You didn't. Based on your last post I'm revising my theory. Now I am thinking you want to see this:

VCode
ALR
AME
ANC
ANT
BDM

That would be a matter of:

listbox1.RowSource = "SELECT DISTINCT Vcode FROM tblPurchaseAnalysisLoc1 "

Until I know what you want, I can't help you.

 

[Skip Bisconer]

Thanks jal. I believe you have figured out what I need. I will tie this in with my form and see how it works.