Simple Question for a simple DB

[Darth_Beckett]

Ok I am right now making a simple Vendor/Product database to create a line sheet for some sales folks. I have 3 tables: Vendors, Products, and an associate entity Vendors_Products to relate the two. I have a form currently that draws the Vendor Name (primary key) from the Vendor table and the Product Name from the associate entity. This allows me to create new vendors and select current product types from a drop down box. The problem is that the drop down box is too long and it is tiresome when 1 vendor has 10 product types.

Can anyone tell me how to resolve this? I thought it would be better to have option buttons and display all available products. Then you could just click all of the option buttons that apply to that Vendor and it would create the relationships...is this possible?

 

[Moniker]

Don't go the option button/checkbox route. The second a product is removed or added, it's a nightmare to maintain. The easy way around this is to use a ListBox instead of a ComboBox. In the ListBox properites, make sure Multi Select is set to Extended. This will allow you to select multiple items in the ListBox that aren't in order (I.e., you could select items 1, 3-5, 8, and 11). The "Simple" Multi Select Option only allows you to select items that are in order (I.e., if you select items 1-4 and then if you select item 6, items 1-4 will become unselected).

To figure out which products are selected in that ListBox, cycle through it, which would look something like this:

Sub CycleThroughListBox()

    Dim ctr As Variant
    
    For Each ctr In Your_ListBox_Name.ItemsSelected
        .
        <Put your code here to process the selected items>
        .
    Next

End Sub

~Moniker