Cascading Combos

[Tanya]

Hi, I am fairly new to combo box's and would appreciate someone helping me. I have a Main form named Students and a tab with a subform named Goal the following code and Row Source for the subform.

I thought everything was working fine, then I realised cboTarget and cboStrategy are not allowing me to make a selection and by default only accept the first in the list.

Has anyone any suggestions? Any help appreciated.

Here is my code:

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
Me!cboStrategy.Requery

End Sub


Row Source Information
------cboFocus
SELECT Focus.FocusID, Focus.Focus
FROM Focus
ORDER BY Focus.Focus;

------cboTarget
SELECT Target.FocusID, Target.Target
FROM Target
WHERE (((Target.FocusID)=Forms!Students!Goal.Form!cboFocus))
ORDER BY Target.Target;


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


Thanks in advance,
Tanya from Oz

 

[wazz]

hi tanya. if you could, it might be helpful (to save some time on this end) to hear about how the student and goal tables are laid out and related, and also how the three other tables are connected.

i think a lot of the code you have won't be necessary, such as requeries of "themselves":

Private Sub cboFocus_AfterUpdate()
cboFocus.Requery

Private Sub cboStrategy_AfterUpdate()
cboStrategy.Requery

etc.

 

[Tanya]

hi tanya. if you could, it might be helpful (to save some time on this end) to hear about how the student and goal tables are laid out and related, and also how the three other tables are connected.

i think a lot of the code you have won't be necessary, such as requeries of "themselves":

Private Sub cboFocus_AfterUpdate()
cboFocus.Requery

Private Sub cboStrategy_AfterUpdate()
cboStrategy.Requery

etc.

G'day Wazz
Thank you for your quick response.
Here goes...

The tables in question: [ since my last post I have renamed all tables and forms to what I perceive as the standard so names will differ very slightly from first post i.e. frm in front of student]

Main table: Related by StudentID to tblGoals
tblStudents
StudentID
LName
FName

tblGoals related by FocusID to tblFocus
GoalID
StudentID
Date
FocusID
TargetID
StrategyID

tblFocus related to tblTarget
TargetID
Target
FocusID

tblTarget related to tblStrategy
StrategyID
Strategy
TargetID

The main form is frmStudents and the subfrmGoals contains the cascading combos which as I mentioned appeared to be working, however they are not allowing me to select from the dropdown.

I hope I have provided you with enough information.

Kindest regards
Tanya from Oz

 

[wazz]

see if this helps. i'm pretty sure there is a problem with how the tables are related, though. if, for ex, these 3 tables are related like so: focus -> target -> strategy, then you probably only need 'strategy' in the goal table. you will be able to know what the target and the focus are because they are related (in reverse). it's a little bit hard to tell what's happening.

something else to do (if you aren't) when designing or thinking about the tables is to think in terms on one-to-many. two tables may be related, but how? one student may have many goals? one focus may have many targets? and in reverse as well: one target may have many focuses, or just one? very important!! these questions will answer your table-design questions (if any).

p.s. the potential problem with the code/design is that you are cascading through fields in the same table, which doesn't really make sense. iow, you have FocusID, TargetID and StrategyID all inside tblGoal. this does not imply a 'cascade' but the opposite, independent fields.

...and...'Date' is a reserved word so don't name fields 'Date'; use GoalDate or StartDate or sim.

Option Compare Database

[COLOR="Red"]' a simple cascading effect:[/COLOR]

Private Sub cboFocus_AfterUpdate()
[COLOR="red"]    ' cboFocus.Requery
    ' Me.cboTarget = Me.cboFocus[/COLOR]
    Me!cboTarget.Requery
End Sub

Private Sub cboTarget_AfterUpdate()
[COLOR="red"]    ' cboTarget.Requery
    ' Me.cboStrategy = Me.cboTarget[/COLOR]
    Me!cboStrategy.Requery
End Sub

Private Sub cboStrategy_AfterUpdate()
[COLOR="red"]    ' not required
    ' cboStrategy.Requery
    ' Me!cboTarget.Requery[/COLOR]
End Sub

[COLOR="red"]' revised:[/COLOR]

Row Source Information
------cboFocus
SELECT Focus.FocusID, Focus.Focus
FROM Focus
ORDER BY Focus.Focus;

------cboTarget
SELECT Target.TargetID, Target.Target, Target.FocusID
FROM Target
WHERE (((Target.[COLOR="Red"]FocusID[/COLOR])=Forms!Students!Goal.Form![COLOR="Red"]cboFocus[/COLOR])) [COLOR="red"]' this value must be in the first column of cboFocus[/COLOR]
ORDER BY Target.Target;

------cboStrategy
SELECT Strategy.StrategyID, Strategy.Strategy, Strategy.TargetID
FROM Strategy
WHERE (((Strategy.[COLOR="red"]TargetID[/COLOR])=Forms!Students!Goal![COLOR="red"]cboTarget[/COLOR])) [COLOR="red"]' this value must be in the first column of cboTarget[/COLOR]
ORDER BY Strategy.Strategy;

 

[Tanya]

Thank you for your thoughtful comments. I will have to think on this and consider alternative structure for tables and relationships. I have been thinking it was something to do with the relationships and will keep you posted.
Tonight I recreated the relationships and experimented but will need more time to ponder, at present I have redesigned tblGoal so that FocusID, TargetID and StrategyID are related only by a lookup 1:1 otherwise there structure remains the same, identifying Target by FocusID etc and have gotten as far as the cascading combos showing all entries for each combo, now I need to refine this so that cboTarget and cboStrategy only show those that meet the correct criteria.
Cheers
Tanya

 

[Tanya]

I have been working on this for days... and cannot get the third combo box to lookup the value in cboTarget.

Please, if anyone can advise me, I would be very grateful.

cheers
Tanya

 

[wazz]

hi tanya. i still have a lingering question.

i can see that, probably, each focus could have many targets; and each target will probably only have one focus. so, literacy might have spelling and writing, but spelling and writing will *only* have literacy as a focus and not, say, numeracy or attendance.
is this correct?

however...it seems that each target can have many strategies: spelling - read a book, write out words // writing - write out words, read a book. question: is it possible that 'read a book' or any of the strategies could be applied to more than one target? you have to figure this out for yourself using your actual data - real or imagined. if it is *possible*, then the tables have to change slightly.

let us know about this first before going any farther.

(the same goes for the first two tables, by the way.)

p.s. so far i don't see any reason for having tblGoal. all of your data is in the other tables and you can use them to reach your end game, so far.

 

[Tanya]

Hi Wazz
Thanks for the quick response.
The reason I have tbl Goal is to record individual students goals, which would be related by StudentID, in my sample db I have omitted this to keep the size of the file down and keep the project simple for the time being.
You are quite right about Focus: Each Focus can have many Targets and each Target has multiple strategies, the strategies do not overlap however i.e. strategyID number 1 may be for spelling and will NOT relate to any other Target.

Main table = tblstudents
Goals = tblGoal

I need to be able to relate particular goals with individual students. My main form has several tabs, one being Academic Goals and this is where Goals are recorded.

Cheers
Tanya

 

[Tanya]

Finally, I have had success...

I have tried numerous arrangements of the tables and query after query...

I believe the problem was with the indexing, with each combo looking up an index, then returning a number, whilst showing text.

Anyway, the tables tblFocus tblTarget and tblStrategy nolonger have a relationship eliminating indexes from the combo equation.

If anyone can come up with a better solution I would love to hear. Otherwise you can see my attached solution.

Cheers
Tanya