combo box link

[Tanya]

Hi
Can anyone please point me in the direction of a good example/tutorial on
how to use a combo box which is dependent on the value in another. i.e. I
have combo box labeled cboFocus which looks up Literacy, Numeracy etc and I
want the second combo cboTarget to lookup values dependent on cboFocus i.e.
if Literacy, then spelling, reading, writing etc
I Believe I have the table structures correct and have seen an example on
YouTube, however, I simply cannot get the second combo to show the related
data.
Any help is appreciated.
Cheers
Tanya

 

[Jeff Boyce]

Check at mvps.org/access for "Cascading Comboboxes". You might check at
microsoft.com, too.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tanya]

Hi Jeff
Thanks for your suggestions. I've searched and searched for a good example
which will help me without success. Here is what I have todate:

cboFocus
SELECT Focus.FocusID, Focus.Focus
FROM Focus
ORDER BY Focus.Focus;

Private Sub cboFocus_AfterUpdate()
cboFocus.Requery
Me.cboTarget = Me.cboFocus

End Sub

cboTarget
SELECT Target.Focus, Target.Target
FROM Target
ORDER BY Target.Target;

Private Sub cboTarget_AfterUpdate()
cboTarget.Requery
End Sub

What I am getting is when I use cboTarget I am viewing all Targets, instead
of those that are criteria based i.e. based on cboFocus criteria

I think the proble is my sql code, and I am really weak in sql

Any support would be greatly appreciated.

Regards
Tanya

 

[Jeff Boyce]

The general concept is:

Combobox1 has a query that "feeds" it.

Combobox2 has a query that "feeds" it.

In Combobox1's AfterUpdate event, add the following code:

Me!Combobox2.Requery (be sure to substitute your own control
name)

In the query that feeds Combobox2, put a selection criterion in on the field
by which you are trying to limit the items in combobox2. That selection
criterion should look something like:

Forms!YourFormName!Combobox1

This has the effect of having a selection in Combobox1 force the query that
feeds Combobox2 to re-run, looking at what's been selected in Combobox1.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tanya]

Thanks Jeff, still can't quite get it to work

----Combobox1 Named cboFocus
SELECT Focus.FocusID, Focus.Focus
FROM Focus
ORDER BY Focus.Focus;

Private Sub cboFocus_AfterUpdate()
cboFocus.Requery
Me.cboTarget = Me.cboFocus
Me!cboTarget.Requery

End Sub

----Combobox2 Named cboTarget

SELECT Target.Focus, Target.Target
FROM Target
WHERE (((Target.Focus)=[Forms]![Goal]![cboTarget]))
ORDER BY Target.Target;

Private Sub cboTarget_AfterUpdate()
cboTarget.Requery
End Sub

When I added the lines as you suggested I ended up with a blank dropdown for
cboTarget

Any suggestions?

Thanks in advance

Tanya

 

[Tanya]

Is it useful for you to see the tables?

----Focus
focusID, AutoNumber
Focus, Text

----Target
TargetID, AutoNumber
Focus, Text
Target, Text

My next question is, perhaps in Target Table I should be relating to FocusID
rather than simply having the information recorded in Focus [Field]

Any thoughts?

Thanks in advance

Thanks Jeff, still can't quite get it to work

----Combobox1 Named cboFocus
SELECT Focus.FocusID, Focus.Focus
FROM Focus
ORDER BY Focus.Focus;

Private Sub cboFocus_AfterUpdate()
cboFocus.Requery
Me.cboTarget = Me.cboFocus
Me!cboTarget.Requery

End Sub

----Combobox2 Named cboTarget

SELECT Target.Focus, Target.Target
FROM Target
WHERE (((Target.Focus)=[Forms]![Goal]![cboTarget]))
ORDER BY Target.Target;

Private Sub cboTarget_AfterUpdate()
cboTarget.Requery
End Sub

When I added the lines as you suggested I ended up with a blank dropdown for
cboTarget

Any suggestions?

Thanks in advance

Tanya

The general concept is:

Combobox1 has a query that "feeds" it.

Combobox2 has a query that "feeds" it.

In Combobox1's AfterUpdate event, add the following code:

Me!Combobox2.Requery (be sure to substitute your own control
name)

In the query that feeds Combobox2, put a selection criterion in on the field
by which you are trying to limit the items in combobox2. That selection
criterion should look something like:

Forms!YourFormName!Combobox1

This has the effect of having a selection in Combobox1 force the query that
feeds Combobox2 to re-run, looking at what's been selected in Combobox1.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tanya]

The form I am trying to create is related to a table named Goal

GoalID
StudentID
Date
FocusID, Number
TargetID, Number
StrategyID, Number

FORM Named Goal

 

[Jeff Boyce]

Tanya

Please describe the "cascading comboboxes" and what you want them to do.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tanya]

Hi Jeff

I finally managed to get two combo's working and would appreciate your
looking at the third for me.

I found it easier to set up a qry, make that work, then apply to combo2
[cboTarget], once that was working I took the same approach with combo3
[cboStrategy], Here they are:

qryStrategy
SELECT Strategy.TargetID, Strategy.Strategy
FROM Strategy
WHERE (((Strategy.TargetID)=Forms!Goal!cboTarget))
ORDER BY Strategy.Strategy;

qryTarget
SELECT Target.FocusID, Target.Target
FROM Target
WHERE (((Target.FocusID)=[Forms]![Goal]![cboFocus]))
ORDER BY Target.Target;

cboFocus
SELECT Focus.FocusID, Focus.Focus
FROM Focus
ORDER BY Focus.Focus;

Here are the event codes


Option Compare Database


Private Sub cboFocus_AfterUpdate()
cboFocus.Requery
Me.cboTarget = Me.cboFocus
Me!cboTarget.Requery

End Sub

Private Sub cboStrategy_AfterUpdate()
cboStrategy.Requery
Me!cboTarget.Requery

End Sub

Private Sub cboTarget_AfterUpdate()
cboTarget.Requery
Me.cboStrategy = Me.cboTarget
End Sub

Now... what I am trying to achieve, combo1 looks up a criteria [Focus for
Learning i.e. Literacy, numeracy],
combo2 looks up Focus and identifies Targets i.e. Literacy - spelling,
writing, reading
combo3 then looks up matching strategies for the targets i.e. spelling -
rewrite keywords each night...

The third combo in this example is not working, it displays a blank
dropdown. When I run the qryStrategy it appears to be giving the desired
result. i.e. prompt for '1' provides a list of Strategies which match Target
1, Therefore I am wondering if it is the code that needs work?

Any thoughts?

Thank you in advance for your time on this.

Regards
Tanya

 

[Tanya]

Hi Jeff

I cracked it!!!
Added another line of code to cboTarget

Working a treat.

Thanks for earlier directions.

Kind Regards
Tanya