How to update combo box "2" based on combo box "1" choice?

[Daniel Yantis]

Combo Box 1 Displays a query/list with 2 columns:

SELECT Classes.CLASS_DESC, Judges.Judge, * FROM Classes;

Combo Box 2 Displays a query/list:

SELECT Judges.Judge FROM Judges;

I want Combo Box 2 to change based on Combo Box 1 answer. Combo Box 2 will still be editable but its default will be based on Combo Box 1.

Example:
I have 250 Classes and 6 Judges. The number of juges will change from time to time and can be as many as 30. The Classes are each assigned a Judge and the assignments can change based on the number of entrants in a classs - ie if a class gets to large I may need to reassign judges to relieve overwork.

Combo Box 1 displays:
Alpha | Bobby
Beta | Bobby
Blue | John
Gold | John
Red | Dan
...

Combo Box 2 displays:
Bobby
John
Dan
Ed
Jim
Bill

I want this to happen:
When you choose "Gold" in Combo Box 1
Combo Box 2 changes to "John" as the default choice.

[Stewart Ross]

We have an Insights article on cascading combo/list boxes which takes you through in detail how to change the rowsource of the second combo using the after-update event of the first combo to trigger the change.

The process is straightforward , but fairly involved when you look at the steps involved, so the Insights article is a very good way to learn how to do it.

-Stewart

[Mr Key]

I have attached the database at record Lookup with 2 Combobox post, it seems you have a similar problem with 2-combobox, get a look at it also. If you have any question, you may ask again
Click here to view th post and attached database

[Daniel Yantis]

I can't seem to adapt the example to work for me.

I used the example and pasted the code given then edited it for my data. I don't seem to understand it.

Attached is my database.

When I update the Combo Box "Class"
I need Text Box "Judge" to change.

***I just simplified my database and this should make it simpler, but I still don't get how to update my TEXT BOX based on the answer from the COMBO BOX.***

[Mr Key]

It seems Judges are very few only 6, therefore You can use Listbox instead of Combobox for judges and set selections due to Classes Combobox as clarified on the attachement below and you are done!
Mark it as answered if it has!!!

[Daniel Yantis]

I must be tired of looking at this. I just don't get it. I tried your fix but seems too odd for me to follow. I'm not very good at this.

I just modified my database and removed [Groups] completely. This should (in my mind) make the problem easier. Now when I change the Combo Box "Class" it needs to Update the Text Box "Judge".

What I can't seem to figure out is how to select the value of the second column in the Combo Box.

[Mr Key]

I have just attached the database for you to use, I have decided to use listbox and not a textbox as you did.
Mark it as answered if it solves your problem

[Daniel Yantis]

The fix you made does not update as you change records.
Also, you can't change the choice in the list box later.

[Daniel Yantis]

We have an Insights article on cascading combo/list boxes which takes you through in detail how to change the rowsource of the second combo using the after-update event of the first combo to trigger the change.

The process is straightforward , but fairly involved when you look at the steps involved, so the Insights article is a very good way to learn how to do it.

-Stewart

I used this example but my application does not match this example. I do not need to change the RowSource just the Default Choice.

[Mr Key]

Open the db and then select one Class_Desc it will update the Judge field automatically, I have tried it before being sent 2 u. Ofcourse we cannot change edit the value in the Combobox but you can do so if you want.
Just read it carefully and apply the changes if wish.
This is just the alternative of how you can approach your design, the real choice is yours.

[Daniel Yantis]

Mr Key, I really did try the fix you sent above. It does change the Judge list box. But then go to the next record using the next/previous buttons and it does not update for the next record. It just keeps the previous data. I tried adding the source field, but that did not fix it either.

[Mr Key]

You have changed your database design, I am talking about latest, I hope you are not using the same folder with the same old database. Remove the old one with Judges group from your C:\ and open again
thanks

[Daniel Yantis]

I did. I just relinked to the one in the folder you have edited. Does it work for you?

[Mr Key]

Yes its, Open the attached db on post no. 7 above, If you are using 2003 that might be a problem, but it works fine without any problem. You may choose any Class value and it gives the Corresponding Judge. I have just opened the db and it work, I dont know what might be the problem. If you have 2003 then rename it to .mdb format instead of .accdb

[Daniel Yantis]

I am using 2010 Enterprise edition. The actual update for the list box works when I choose the new class. The problem is that when I go to next record it does not update the judge for the next record. I figure it was not linked to a source in the database table, but I fixed that too. It just keeps the same data as the previous change.

This is frustrating me!