Help on how or why to build subforms without master/child links

[darbid]

Hey everyone.

The mdb has an SQL backend. I am looking at my subforms and remember that I read somewhere here (I think banana wrote it) that using the master child relationship might be less efficient than setting a subform by setting it's recordsource in the oncurrent event of the parent form. Where was that? I think it was a general post about backends. But more importantly without the master child link we don't have access automatically building the link when a new record is added to the subform. What is the best way to do this?

 

[gemma-the-husky]

in the before update event for a new record in the sub-form, set the FK in the subform record, with suitable code.

 

[darbid]

Thanks GTH. And that way a new record is only created when a new record is wanted. I was thinking of just making the default of one of the child fields = to the master field but that would mean there would always be a new record in the child.

 

[darbid]

To take this one step further how can it be done if the subform is created with a link table involved.

eg tbl1 - tbl_tbl1_tbl2 - tbl2

Following what I have learnt from you in the before update of the subform I code the Foreign Key of tbl_tbl1_tbl2 to be equal to the Primary Key of tbl1.

(I think I am using the right wording here) How do I make the link eg how do I make the Foreign Key of tbl_tbl1_tbl2 equal to the Primary key of tbl2? I would assume that the Primary Key of tbl2 will only exist after or at the earlist at update.

 

[gemma-the-husky]

when you build the form, you specify that items in form1 are linked to items in form2 via certain fields.

if you do that, and add a new record in the subform, then access automatically adds the lniked values to maintain the relationship.

===========
now if you dont specifiy what the links are, then the subform has to specified, and requeries based on something external, such as a variable - so you have to set the variable and requery the subform as you change items in the first from. No problem, this is normal stuff. BUt now if you add a new item to the subform, access doesnt know how to set ANY of the fields. So you have to manually set the fields that manage the link between the main form and the subform yourself - in code - and this would normally be in the subforms beforeupdate event

 

[darbid]

now if you dont specifiy what the links are, then the subform has to specified, and requeries based on something external, such as a variable - so you have to set the variable and requery the subform as you change items in the first from.

I am with you so far. In the parent form's on Current event I will alter the subform record source with an SQL that includes WHERE subformFK = parentformPK

BUt now if you add a new item to the subform, access doesnt know how to set ANY of the fields. So you have to manually set the fields that manage the link between the main form and the subform yourself - in code - and this would normally be in the subforms beforeupdate event

Ok I am with you here too. I think I would have a hidden field on the subform which is bound to the subformFK. I would then make subformFK = parentformPK

Correct me please if I am wrong.

I then move on to getting my head around there being a linked table involved. ie Where there is a second PK/FK relationship that needs to be manually done as well. Maybe I should do a dummy example and see if it works and move away from the theory. I was kinda hoping that there was an accepted way of doing this.

 

[darbid]

I think my questions are showing my lack of practical understanding. I will give it go and see what happens.

Thanks GTH again.

 

[gemma-the-husky]

the thing is

a subform implies that the current record on the mainform is linked to all the records in the subform - which mandates there to be a link - otherwise it isnt a subform, if you see what i mean.

=======
now, one circumstance i can see for there not to be a link - is that the items shown in the subform are based on evaluating a function that determines which items should be displayed. In which case, you cannot construct an automatic link. Lets say you have an option group enabling you to select different groups of items, that cant easily be indexed - so you have to use a function.

=======
and the only other reason to simulate a subform I can think of, is if you have two continuous/datasheet style sub forms - since access cannot nest a continuous subform inside another continuous subform automatically

eg main form customers
sub form orders by customer (no problem - an automatic form/subform)
sub-subform, orderlines by order (cannot be done - nested continuous forms)

but you can simulate the sub-subform. You can show it as an unlinked subform on the mainform. You base it on a query that uses the current ordernumber from the first
subform - and as you move to different items in the subform (or indeed move to different items in the main form) you actively requery the second subform (because access cannot manage this link automatically.