Combobox list

[georgp]

Hi everybody,

i have two comboboxes. One pulls out a list with company names, and the second pulls out a product name list. Many of these companies have the same products but with different names.
I would like to have the second combobox list first to show the product names of the company selected in the first combobox, then e.g. a line '----------' and then the product names of the other companies.
I can not figure out the SQL commands how to pull (in the second combobox list) the product names of the selected company first and then how to put the line between this company's products and the products of the other companies.
Any advise is welcome.

Thanks for your help, georgp

 

[TheAceMan1]

How are ya georgp . . . . .

What you ask is fair enough . . . . but think about this!:

[blue]Since you are the designer of the Database, [purple]you have to have some shceme for identifying the same product with different names![/purple].[/blue]

So the question is:

How would you know the same product under different names?

Only you can tell us this! . . . . . .

See Ya! . . . . . .

 

[georgp]

Hi Aceman1,

thanks for your interest. I do not want to hide anything, but my tables deal with chemicals, actually polymers and their brand names, and I believe it is pretty confusing if I disclose the details.
Basically there are many vendors for the same chemical product, but they sell it under different names. Although some of these chemicals are almost the same, they differ a little bit, e.g. in purity. All these differences are part of one lookup table linked to the primary lookup table for combobox2.
Now I want to generate a new chemical composition.
The combobox1 shows all vendors for all chemicals, the combobox2 then should show all chemicals from my vendor selection of combobox1, and if it is either not available from the vendor (or out of stock in my place) then I want to have the option to select the same chemical from another vendor. But the products from the selected vendor should appear first.
So my primary lookup table would look like this (there are actually many more fields):

Vendor GivenNamebyVendor ScientificName
DuPont BeautyLili Methacrylic acid
DuPont GoodSmell Thiophenol
Lonza HardWood Methacrylic acid
Dow WhiteLine Methacrylic acid

You see article 1, 3 and 4 are almost the same ('Methacrylic acid'), but have different brand names (and also some different properties stored in the secondary lookup table) and different vendors.

Now my combobox2 is linked to a third, the main table which finally yields the information I really want. The list of combobox2 should show the fields Vendor, GivenNamebyVendor and the ScientificName in the order of:
1. Alphabetically all GivenNamebyVendor from the vendor selected in combobox1,
2. then a line indicating that the selected vendor product list is finished, and
3. then all products from the other vendors ordered by vendor, GivenNameByVendor.

My problem is how to get e.g. DuPont products on top of the combobox2 list (if DuPont is selected in combobox1), or Lonza products (if Lonza is selected in combobox1).

Confusing? Let me know, if you have any idea.

regards, georgp

 

[TheAceMan1]

OK georgp . . . . .

Realize, we need some model to go by. I'm sure you can devise an equivalent [blue]model[/blue] of this. its how to relate one to many, without a seperate table, that leaes me in the dark. I can think of some things, but since I'm not the designer . . . . . what doI know?

I'm sure you see my point!

See Ya! . . . . . .

 

[KenReay]

HI

You need a UNION query I think as the source of your second combo,

Let us say the form is called MyForm, cboVendor has the Id of the Vendor in it, the Rowsource for cboProduct needs to be something like:

SELECT GivenNameByVendor, ScientificName, "0" As Seq FROM tblProducts WHERE Vendor = Forms!MyForm!cboVendor
UNION
SELECT "---" As GivenNameByVEndor," " As ScientificName, "1" As Seq From tblProducts
UNION
SELECT GivenNameByVendor, ScientificName, "2" As Seq FROM tblProducts
WHERE Vendor <> Forms!MyForm!cboVendor
ORDER BY Seq, GivenNameByVEndor

I have not tested above, but my logic is as follows

First Select gives us the Products for the Selected Vendor

Second SElect gives us a dummy line of "--"

Third Select gives us the products from all vendors except the seelcted Vendor

The purpose of the Seq column is to allow us to Sort the rows so that Selected Vendor comes first, then Dummy line of ---, then Remaining Products

Incidentally, I do not like teh idea of the "---", while I can see its appeal in terms of looking at the list, What happens if the user selects the "---" line?

But, it is your Form, so really the point is does the Union query do teh trick?



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK