Selection made in one combo box, limits choice in another.

[Robbyp2001]

Hello folks

Sorry if this question seems a bit long-winded but I want to make sure that it's clear. I have a form that contains two combo boxes from which the operator selects relevant information for new records. One combo contains all of the possible Yeargroups: Year 7, Year 8, Year 9, Year 10, Year 11 and Year 12. The next combo box contains all of the possible Form classes (see below). Each form class is associated with a particular Yeargroup, but I have noticed that the operator has on occasion selected the correct Yeargroup but the incorrect Form class, leading to problems elsewhere.

What I would like to know is how to limit the entries in the second combo depending on the selection made in the first.

Combo 1
7 Year 7
8 Year 8
9 Year 9
10 Year 10
11 Year 11
12 6th Form

Combo 2
1 FTA Year 8
2 PBE Year 8
3 EMC Year 8
4 FKA Year 8
5 SAT Year 9
6 HAN Year 9
7 NTL Year 9
8 ABR Year 10
9 MVI Year 10
10 JSI Year 10
11 ECO Year 10
12 BAB Year 11
13 MSE Year 11
14 NHA Year 11
18 OAS 6th Form
19 LCA 6th Form
20 ZAB 6th Form
21 MMU Year 9
22 N Ass
23 CCR Year 7
24 TAL Year 7
25 KSM Year 7
26 KUD Year 7

Any combination of the two is at present possible. What I would like is if the operator selects, say, Year 7 in combo 1, then only the corresponding Year 7 records would appear in combo 2, thus, CCR Year 7, TAL Year 7, KSM Year 7 and KUD Year 7.

Can anyone help?

Rob

 

[pbaldy]

See if this helps

http://www.baldyweb.com/CascadingCombo.htm

 

[Robbyp2001]

Dear Pbaldy

I looked at the sample DB and this is exactly what I need. I just need to figure out the code required to get mine up and running. My poor knowledge of VB means it might take some time. Many thanks for your kind help.

Rob

 

[pbaldy]

No problem. Post back if you get stuck.

 

[Robbyp2001]

Hey Paul

I know that this should be easy, especially with your sample dbase, but I just dont seem to grasp the code here. If it's not being too cheeky, could I send you a copy of my dbase so that you could have a look?

Rob

 

[pbaldy]

You can post it here with the failing effort and we'll sort out the error. Compact/repair then zip.

 

[Robbyp2001]

Paul, I think it's too big to attach here. It certainly seems to have problems uploading. I can post it to you at www.sendspace.com. I know for sure that will work.

 

[pbaldy]

If it won't fit here I'd rather you emailed it:

removed for spam prevention

 

[Robbyp2001]

Paul

I deleted a lot of the data and can now attach to an e-mail. Still no luck attaching here though. Could you give me an e-mail address?

 

[pbaldy]

Other than the one I already did?

 

[Robbyp2001]

Paul I cannot see an e-mail address in any of your posts. Am I looking in the wrong place? Anyway my e-mail address is robbyp2001@yahoo.com

 

[pbaldy]

I didn't format it so the spam crawlers wouldn't see it as an email address, but it's there:

If it won't fit here I'd rather you emailed it:

removed for spam prevention

 

[Robbyp2001]

Ah, I see it now. I'll e-mail immediately.

 

[pbaldy]

I don't see either of the methods attempted. There's no code in the first combo to either reset or requery the second.

I also don't see a field that ties those two tables together. The Reg field is similar, but doesn't appear to contain compatible data. Unless I'm blind.

 

[Robbyp2001]

Paul

I deleted the code that I attempted. That is why there is nothing on there.

You make an interesting point about the tables being linked to each other. They are not linked directly to each other because I had not foreseen this problem, however I can now see why they must be linked. I'll need to go look at this again.

 

[rockyjr]

Here's a quick example.

 

[Robbyp2001]

Paul

You're a genius. I now see what's required. I start sorting this out tomorrow.

Many thanks once again!

Rob

 

[pbaldy]

Happy to help Rob. Post back if you run into problems.