Subform Problem - How do I update the Record Source. TRYING 4 Days

[PatT123]

I have been trying to code a record source for a Sub Form. The query cannot
be coded directly in because it has a sub query that Access wants to change
when the subform is saved. It puts brackets around fields, puts in periods,
etc.

If I name the query from the query window everything works fine. I want to
not have the query in the query window. I want to keep the query window
clear for adhoc development.

I have tried to put the code into the module to update the record source of
the subform, but the main form will not find the subform when I tried this.
I tried every event on both the form and the subform to update the record
source. Nothing works.
I also tried putting the subquery info into a subform on the subform, but I
could not get the subform to bring up the sub sub.... Maybe it has to do
with the form type?

I also tried to put the subquery into a drop down box tied to a field on the
subform, but could not get that to populate a cell on the form, or to
populate the drop down, except by hitting the drop down arrow.

The query data - Including the subquery is below. - Replacing the [] with
() does not make it work in the form. Any other ideas anyone can come up
with???

SELECT TBLFUNDINGTOPOS.PosNo, TBLFUNDING.Funding, TBLFUNDING.PCA,
TBLPCA.Fund, TBLFUNDING.FedGrant_PH, TBLFUNDING.OthGrant_PH,
TBLFUNDINGTOPOS.Percent, TBLPCA.SubProgram, TBLFUNDINGTOPOS.TotalFTE,
TBLPCA.FundType, FTESUM.PCT
FROM (TBLFunds RIGHT JOIN TBLPCA ON TBLFunds.FUND =TBLPCA.Fund) RIGHT JOIN
(TBLFUNDING RIGHT JOIN (TBLFUNDINGTOPOS LEFT JOIN [SELECT
Sum(TBLFundingToPos.Percent) AS PCT, TBLFundingToPos.DIVFund
FROM TBLFundingToPos
GROUP BY TBLFundingToPos.DIVFund]. AS FTESUM ON
TBLFUNDINGTOPOS.DIVFund=FTESUM.DIVFund) ON
TBLFUNDING.Funding=TBLFUNDINGTOPOS.Funding) ON TBLPCA.PCA=TBLFUNDING.PCA;

 

[Dirk Goldgar]

I have been trying to code a record source for a Sub Form. The query
cannot be coded directly in because it has a sub query that Access
wants to change when the subform is saved. It puts brackets around
fields, puts in periods, etc.

If I name the query from the query window everything works fine. I
want to not have the query in the query window. I want to keep the
query window clear for adhoc development.

I have tried to put the code into the module to update the record
source of the subform, but the main form will not find the subform
when I tried this. I tried every event on both the form and the
subform to update the record source. Nothing works.
I also tried putting the subquery info into a subform on the subform,
but I could not get the subform to bring up the sub sub.... Maybe it
has to do with the form type?

I also tried to put the subquery into a drop down box tied to a field
on the subform, but could not get that to populate a cell on the
form, or to populate the drop down, except by hitting the drop down
arrow.

The query data - Including the subquery is below. - Replacing the []
with () does not make it work in the form. Any other ideas anyone
can come up with???

SELECT TBLFUNDINGTOPOS.PosNo, TBLFUNDING.Funding, TBLFUNDING.PCA,
TBLPCA.Fund, TBLFUNDING.FedGrant_PH, TBLFUNDING.OthGrant_PH,
TBLFUNDINGTOPOS.Percent, TBLPCA.SubProgram, TBLFUNDINGTOPOS.TotalFTE,
TBLPCA.FundType, FTESUM.PCT
FROM (TBLFunds RIGHT JOIN TBLPCA ON TBLFunds.FUND =TBLPCA.Fund) RIGHT
JOIN (TBLFUNDING RIGHT JOIN (TBLFUNDINGTOPOS LEFT JOIN [SELECT
Sum(TBLFundingToPos.Percent) AS PCT, TBLFundingToPos.DIVFund
FROM TBLFundingToPos
GROUP BY TBLFundingToPos.DIVFund]. AS FTESUM ON
TBLFUNDINGTOPOS.DIVFund=FTESUM.DIVFund) ON
TBLFUNDING.Funding=TBLFUNDINGTOPOS.Funding) ON
TBLPCA.PCA=TBLFUNDING.PCA;

What is the actual code you've tried, and what event have you used to
try to run it? Incidentally, you may find that leaving the table
qualifier off of the subquery fields makes the SQL statement work even
if saved at design time. Does this work:

SELECT TBLFUNDINGTOPOS.PosNo, TBLFUNDING.Funding,
TBLFUNDING.PCA,TBLPCA.Fund, TBLFUNDING.FedGrant_PH,
TBLFUNDING.OthGrant_PH,TBLFUNDINGTOPOS.Percent, TBLPCA.SubProgram,
TBLFUNDINGTOPOS.TotalFTE,TBLPCA.FundType, FTESUM.PCT FROM (TBLFunds
RIGHT JOIN TBLPCA ON TBLFunds.FUND =TBLPCA.Fund) RIGHT JOIN (TBLFUNDING
RIGHT JOIN (TBLFUNDINGTOPOS LEFT JOIN [SELECT Sum(Percent) AS PCT,
DIVFund FROM TBLFundingToPos GROUP BY DIVFund]. AS FTESUM ON
TBLFUNDINGTOPOS.DIVFund=FTESUM.DIVFund) ON
TBLFUNDING.Funding=TBLFUNDINGTOPOS.Funding) ON
TBLPCA.PCA=TBLFUNDING.PCA;

?

If you can't save that successfully at design time, I'd expect code
along the lines of this to work, in your main form's Open event:

Me.NameOfSubformControl.Form.RecordSource = "SELECT ... "

In the model above, "NameOfSubformControl" must be the name of the
subform control, on the main form, that provides the window in which the
subform is displayed. That name may or may not be the same as that of
the form object it displays.