show values in one Combo box based on values selected from another combobox (1 Viewer)

[Emma]

I have two fields on a form, both combo boxes. One is a value list (TYPE) which is one of three values (type1, type2, type3). The other combo looks up to a table SPECIFIC, and this records specific information and may have several specific records relating to Type1 etc.

If the user selects a TYPES (for example type1), then I need my database to show all the records in the SPECIFIC table called TYPE1 in my dropdown list on the form. However, despite much mulling over this I donot know where to start!

The furthest I have got is that AfterUpdate event on the TYPE field should run some sort of query to filter the SPECIFIC table for the relevant fields. However, I have failed miserably to get any further with this. I was sure someone else has asked this same question (show values in a Combo box based on values in another combobox?) but cannot find it anywhere!

If anyone can help, I would (yet again!!) be most grateful (Just when I think I am getting my head round VB I seem to hit another wall!! One day I may be able to answer someone elses question - preferably something easy!)

Many Thanks
Emma

 

[Chris RR]

OK, let's say your two combo boxes are cboType and cboSequence. I'm also assuming that the sequence is stored in a table that also contains type.

In the AfterUpdate for cboType, put something like this:
Me.cboSequence = Null
Me.cboSequence.Requery

Then, make the RowSource for cboSequence a query that has My_tablefield_for_type = [MyForm]![cboType]



[This message has been edited by Chris RR (edited 11-14-2000).]

 

[Neal]

Microsoft has a paper on the subject: Article ID Q97624.
I found it pretty confusing, but did manage to get it to work.
Here's what I would do.
Use the wizard to set up combo1. Say you want to type in the values, then type in the diference Types.
Create a query with the two fields from your table: Type and Specific. Use the value from combo1 as the criteria in the Type field (ie. forms!yourForm!combo1).
Use the wizard to set up combo2 on your form. This time, say you want to get the values from a query. Select the query you just created, and the Specific field as the source of the values). You'll need to add a line of code on the GotFocus event of combo2 to requery so the data is fresh should your user change from one type to another. The code would be: Combo2.Requery
If I haven't been clear enough, I could send you a sample.

 

[Emma]

Many thanks to you both, my problem is now sorted!

Regards,
Emma