limit contents of one combo box based on the selection in another 5

[goodwork]

I am having problems with the WHERE statements.

I have two combo boxes. One of them (the first one) is the results of a query. It displays a list of catergories - i.e A, B, C etc.

the second one is a combo box of a list of hundreds of names, however from all catergories

How do I, when selecting the first combo box, a catergory, i,e "A" will bring up a list of names in the second combo box belonging to catergory A. Not all catergories

Thank you very much

 

[coco86]

In your second combobox, the query should be something like "...where category = forms!formname!combo1". You'll probably need to refresh the contents of combo2 when a selection is made in combo1

 

[KenReay]

If I understand your question correctly you need:

First combo, cboCategory, with a rowsource, based on a SELECT DISTINCT query, so that user sees each Category only once

Second Combo, cboSubCategory, with a rowsource based on your table as you describe, but with a criteria of Forms!YourForm!cboCategory (using your own form name of course).

In the got focus event of cboSubCategory put code so:

If IsNull(cboCategory) Then
msgbox "Please enter Category"
cboCategory.SetFocus
Else
cboSubCAtegory.Requery
End If
Regards

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

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

UK

 

[goodwork]

Ken,

Thanks for your answer, but I still cannot make it work.
I think the problem lies in the criteria in the rowsource of the second combo box. I have tried inserting at the end of the row source - WHERE Forms!YourForm!cboCategory (using my own form name of course).

Please can you explain to me more clearly about the "criteria" on the table in the rowsource.

Thank you very much.

 

[KenReay]

Hi

I was being a bit 'loose' with my description, assuming people know the background, when perhaps they do not... sorry

you need the criteria to be:

= Forms!YourForm!cboCategory (using my your own form name of course).

this is as it would appear in the query design grid, under the relevant column

if you are building an sql string in code then it would be:

SELECT ...etc .. WHERE YourCol = '" & Forms!YourForm!cboCategory & "';".

Regards

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

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

UK

 

[goodwork]

IT WORKS!!

Thanks a lot!

A star on the way!

 

[jonsagara]

So, after selecting a value in the subCombo, how to you access that value programatically?

In my case, the subCombo has two columns. Is there a collection built into the Value property? Jon Sagara

 

[KenReay]

Hi

jonsagara

I think you need to look at the .column() collection, note it is zero based so .column(0) is the first value in the list, and if you have a muklticouln array, then .column() is a multidimensional array so .column(0,0) etc Regards

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

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

UK

 

[jonsagara]

Awesome - thanks Ken! Jon Sagara