Does Form Load before underlying Query can Finish or? 1

[uncleG]

I have a form with 3 individual sub forms on it using A2003, Split FE/BE.
The Main form:
frmPurLinesMake2 bound to qryPurlinesMake2
field: qryPurLinesMake2.Quantity
field: TheFinQty (=[frmProductionYield2].[Form]![Sum2])
The 3 subs are:
frmProductionYield2 is bound to qryProductionYield2(provides part mfg history)
frmXIsMadeFrom3 is bound to qryXIsMadeFrom3 (provides Qty calculations)
frmIsMadeFrom2 is bound to qryIsMadeFrom2 (provides a list of items to make the part from)

When I open my form qryXIsMadeFrom3 looks to the main form for Quantity and TheFinQty and gets the value for Quantity but some how misses [TheFinQty]. If I Requery the forms it still misses TheFinQty, yet the form fills in completely when I exit from design mode or if I run the source Query after the form is Open.
Help and Guidance appreciated
Thanks,
UncleG

Follows is qryXIsMadeFrom3

SELECT tblIsMadeFrom.ItemNo2, tblIsMadeFrom.Ln, tblItems.PName, tblItems.ExtLine, tblIsMadeFrom.QtyPer, tblIsMadeFrom.QtyYie, tblIsMadeFrom.Rank, tblIsMadeFrom.ItemNoFk, tblIsMadeFrom.mfID, (([Forms]![frmPurlinesMake2]![Quantity])*1) AS FullQty, ((([FullQty])/([QtyYie]))*1) AS FullQtyRequires, (([Forms]![frmPurlinesMake2]![TheFinQty])*1) AS CompletedParts, ((([FullQty])-([CompletedParts]))*1) AS StillNeed, ((([StillNeed])/([QtyYie]))*1) AS StillNeedRequires
FROM tblIsMadeFrom LEFT JOIN tblItems ON tblIsMadeFrom.ItemNo2 = tblItems.ItemID
WHERE (((tblIsMadeFrom.ItemNoFk)=[Forms]![frmPurLinesMake2]![ItemID]))
ORDER BY tblIsMadeFrom.Ln, tblIsMadeFrom.Rank;

 

[PHV]

The subforms are loaded BEFORE the main form.
Why not use linked subforms instead ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[uncleG]

Hi PHV, thanks for the interest, Sorry for the confusion, by individual I was implying that the subforms where not 3 levels deep however each is related to a field on the main form as follows. Where I said is bound I was implying to where the data source actually came from. I probably need some work on my vocabulary.

main form =[frmPurLinesMake2], SubForm: [frmProductionYield2] Link Child: LotNo, Link Master: LotNo
main form =[frmPurLinesMake2], SubForm: [frmXIsMadeFrom3] Link Child: ItemNoFk, Link Master: ItemID
main form =[frmPurLinesMake2], SubForm: [frmIsMadeFrom2] Link Child: ItemNoFk, Link Master: ItemID


Hope this helps to paints a clearer picture
Thanks,
UncleG

 

[TheAceMan1]

How are ya uncleG . . .

You have a problem in the sequence with which the form/subforms are opened and loaded. Considering (already stated by [blue]PHV[/blue]) subforms open first, we still don't know which subform opens 1st, 2nd or 3rd. You say:

[blue]When I open my form [purple]qryXIsMadeFrom3[/purple] looks to the main form for Quantity and [purple]TheFinQty[/purple] and gets the value for Quantity [purple]but some how misses [TheFinQty][/purple][/blue]

When [blue]frmXIsMadeFrom3[/blue] opens, the mainform isn't open yet to provide [blue]TheFinQty[/blue], which is dependent on an equation looking at [blue]frmProductionYield2[/blue] ... and we can't say if [blue]frmProductionYield2[/blue] is open yet ... (we still don't know the sequence with which the subforms are opening).

One way to circumvent this is by taking control of when the subforms open. The subforms start out with their [blue]recordsources[/blue] empty and in the mainforms [blue]On Load[/blue] event we assign the recordsources approrpriately. Something like:

[blue]   [frmProductionYield2].Form.RecordSource = "qryProductionYield2"
   Me.Recalc 'force recalc for TheFinQty
   [frmIsMadeFrom2].Form.RecordSource = "qryIsMadeFrom2"
   frmXIsMadeFrom3.Form.RecordSource = "qryXIsMadeFrom3"[/blue]

[blue]Your Thoughts? . . .[/blue]


See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[uncleG]

My Thoughts.....
That would be...Thank You very much TheAceMan1, it now works like a charm. I was unaware of setting the Recalc and calling it from the load event. Some how I just never needed it before. I had tried resetting the tab orders of the subforms, Requery of the subforms, I had even renamed one subform suspecting it may be alphabetical, none of which had any effect.
Thanks Again,
UncleG