Queriery on child table

[GaryPanicZ]

Hi everyone

Vague heading I know -

What I am tryng to do is get a list on a form ... easy enough

I have my mainform and its child Mainfrm-Childfrm and this relation is fine
now I want to add on another say grandchild

Mainfrm childfrm
Dave simon
daisy


now when I do simon - has a relation of Liz and George and list these on a form - they also show up on Daisy relation on the form


each has a table


maintbl
childtbl
grandchild tbl

how do I get that only simons show on the form and if Daisy has none -(or say peter and susan) that they show up
the form is a continuous form with a list field on it

(I hope i have explianed this )

screen shot

[mike60smart]

Hi Gary

Can you upload a zipped copy of your database?

[ArviLaanemets]

I'd do this with 2 tables, like:
tblPersons: PersonID, ...;
tblPersonChilds: PersonChildID, PersonID, ChildID (where ChildID is FK equal with PersonID of this persons child in tblPersons).

Now you create a single form (e.g. fPerson) based on tblPersons, where you can select a person, and it's info is displayed
As next you create a continuous form, based on tblPersonChilds (e.g. fPersonChilds1), and insert it as subform (e.g. sfPersonChilds1) into form Person. Whenever you select a person in main form, all childs he/she has are displayed in this subform.
As next, you add an hidden unbound control into main form, and for form fPersonChilds1 in subform dfPersonChilds1 you create an OnCurrent event, witch enters ChildID value of active record into unbound control in main form (fPerson).
As next, you create another continuous form similar to fPersonChilds (e.g. fPesonChilds2), and insert it too into main form as subform, but you link the ChildID in this subform to unbound control in Main form. As result, whenever you select any child in 1st subform, all his/her childs (i.e. Main forms person's grandchilds) of this child are displayed in second subform.

You can continue with this for so many levels as you'll have room in Main form for all those subforms!

But in case you want to get a list of all descendants of person from those 2 tables, using a query, then you have to create the back-end of your database e.g. in SQL Server, as Access doesn't support such queries (recursive ones).

[GaryPanicZ]

ahh - sneaky ....I did't think of doing it this way and relatively easy .

I'll give it a go - need coffee before i start this

[GaryPanicZ]

OK it refreshes my list depending on what (subform) child record I am on - great (big thanks -)- but somewhere there must be a way of doing tihs - where it show straight away
david has simon
and Daisy has none or say liz and peter

but big thanks in the meantime .. i will get myself another coffee and drink to your good health...