combo box link

  • Thread starter Thread starter Tanya
  • Start date Start date
T

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
 
Check at mvps.org/access for "Cascading Comboboxes". You might check at
microsoft.com, too.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
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
 
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
 
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
 
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

Tanya said:
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


Jeff Boyce said:
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
Click to expand...
Click to expand...
 
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
 
Tanya

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

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
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
 
Hi Jeff

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

Working a treat.

Thanks for earlier directions.

Kind Regards
Tanya
 
Back
Top