Cascading combo selection in continuous subform

[WingerD]

For my biology database: I have a main form (species) and a subform (traits). Each trait is identified both by an autonumber index value and a Trait_CODE (8 characters, in sortable order); for each Trait, the user may chose among a number of trait-specific selections. So, if Size is a Trait, Selections may be Very Small, Small, Medium...etc. But for Shape the Selections should be Elongate, Medium, Round,...etc.

I have set up a sample form with unbound combo boxes to cascade selection values: (1) cboTrait and (2) cboSelection:



for cboTrait
SELECT qry_BFId_Traits.Trait_CODE
FROM qry_BFId_Traits
ORDER BY qry_BFId_Traits.Trait_CODE;

> Also in this combo box have an AfterUpdate event:
Private Sub cboTrait_AfterUpdate()
CboSelection.Value = Null
CboSelection.Requery
End Sub

for cboSelection
SELECT qry_BFId_Selections.Selection_SH, qry_BFId_Selections.Trait_CODE
FROM qry_BFId_Selections
WHERE (((qry_BFId_Selections.Trait_CODE)=[forms]![Form1]![cboTrait]));

This works to create cascading selections satisfactorily, in an isolated form with no query associations.

BUT, I cannot figure out how to connect this to the fields I am trying to fill in my continuous subform query (namely [TraitData_TraitId] and [TraitData_SC_SelectionID]), each of which links to a separate table for reference look-up values.

As far as i can tell, this combo box example only works (1) when unbound, or (2) when in a main form, controlling all records in subform, but my specific objective is to create separate, cascading selection choices for each row in my continuous subform. How do I do this?

Sorry, I am sure this is a dumb, oft-repeated question. But I'm brand new to the group and hope I can get some insight.

Thanks in advance!

[isladogs]

Just about to sign off as its late here in the UK but you may find it useful to look at these examples of cascading combos:
a) UK postcodes - on my website http://www.mendipdatasystems.co.uk/c...xes/4594455723
b) Animal classification - FMS website - http://www.fmsinc.com/microsoftacces...cascading.html - apparently this has some flaws

HTH

[Micron]

It appears that in your examples, the combos in question are not on a continuous form whereas I think these are. If that's the case, then I think the latter combos need to get the value of prior combos (or some other field - can't recall which it is at the moment) based on the current record, not on just the selection in the prior combo.

[isladogs]

I don't see why it makes any difference whether its a continuous form.
In fact I have multiple examples where cascading combos are used with continuous forms
For example, see the Student Target Group Explorer example at: http://www.mendipdatasystems.co.uk/m...ter/4594454290

This includes 12 combos mostly independent of each other.
However the tutor group combo depends on the year group. So in the example above it has been filtered to 9BN



Apologies for the huge images - these are full screen forms

[Micron]

on a continuous form

I meant where the combo is a field in the continuous form records. I'm not seeing any evidence of combos in the records on your pics, not that I can tell if it's a continuous form or datasheet. Yours look like they're in a header?

[isladogs]

It's a continuous (sub)form. There are no combos in the subform controls.

However, I've just read post #1 again and now understand why you wrote that.
Sorry that I missed the point before.

However, I think what the OP is suggesting makes little sense

[Micron]

Sorry that I missed the point before.

All is forgiven, Obi-Wan.
I do think though that what I'm saying is applicable. It seems that the combos in question are on a csf because

This works to create cascading selections satisfactorily, in an isolated form with no query associations. BUT, I cannot figure out how to connect this to the fields I am trying to fill in my continuous subform

To make it work when they're on a csf, you'd use the Current event so that you can get a value of from the "active" (current) record. Not much I can add to this in terms of direction without seeing the db, save to say that [forms]![Form1]![cboTrait] won't work when that control is on a csf. I wouldn't use combos as fields on a csf if I could help it as they're more difficult to deal with.

[June7]

Think I have done something similar only for search/filter records, not for data entry. I am not quite understanding your implementation.

If you want to provide db for analysis, follow instructions at bottom of my post.

[isladogs]

All is forgiven, Obi-Wan.

LOL. I've been called many things over the years but never that before. I've never seen any of the Star Wars films.

[CJ_London]

here is an example which uses cascading combos in continuous forms or datasheets

https://www.access-programmers.co.uk...d.php?t=275155

[WingerD]

here is an example which uses cascading combos in continuous forms or datasheets

https://www.access-programmers.co.uk...d.php?t=275155

Thanks for this (and other remarks).

As some of you have noticed, I am using my continuous form (actually a subform, but I'm not sure that matters in terms of the issue at hand) to build a dataset, in which I want cascading menu choices as I make input selections for new records. I am not using the combo boxes to filter a pre-existing dataset (that would be easier, could rely on unbound boxes). Sorry if in setting up the question I did not state all this clearly.

So...I have been busy on a different project but will check out the very helpful suggestions provided, and come back if I can't solve my original problem (indeed, I'll come back anyway to verify if successful).

If I still have problems I'll post an abbreviated database so you can see what I'm trying to do.

Cheers,
WingerD