[AccessD] Unbound Subform Revisited

Well....I tried that, as follows:

Dim strSQL As String

strSQL = "SELECT [qryWeeklyTimesheetVarianceSummary].[Year],
[qryWeeklyTimesheetVarianceSummary].[Month],
[qryWeeklyTimesheetVarianceSummary].[Entity],
Sum([qryWeeklyTimesheetVarianceSummary].[SumOfRegPay]) AS [Reg Stand
Cost], Sum([qryWeeklyTimesheetVarianceSummary].[SumOfOvtPay]) AS [OT
Standard Cost], Sum([qryWeeklyTimesheetVarianceSummary].[SumOfOvt2Pay])
AS [OT2 Standard Cost], Sum([sumofregpay]+[sumofovtpay]+[sumofovt2pay])
AS [Total Standard Cost],
Sum([qryWeeklyTimesheetVarianceSummary].[ActReg]) AS [Calc Reg Cost],
Sum([qryWeeklyTimesheetVarianceSummary].[ActOvt]) AS [Calc OT Cost],
Sum([qryWeeklyTimesheetVarianceSummary].[ActOvt2]) AS [Calc OT2 Cost],
Sum([actreg]+[actovt]+[actovt2]) AS [Total Calc Cost],
First([qryPayrollPaid].[SumOfAmountPaid]) AS FirstOfSumOfAmountPaid,
First([qryPayrollDiffSummary].[SumOfHrs]) AS FirstOfSumOfHrs,
First([qryPayrollDiffSummary].[SumOfWageDiff]) AS FirstOfSumOfWageDiff "
+ _
"FROM qryPayrollDiffSummary RIGHT JOIN (qryPayrollPaid RIGHT JOIN
qryWeeklyTimesheetVarianceSummary ON
([qryPayrollPaid].[Year]=[qryWeeklyTimesheetVarianceSummary].[Year]) AND
([qryPayrollPaid].[Month]=[qryWeeklyTimesheetVarianceSummary].[Month])
AND
([qryPayrollPaid].[Entity]=[qryWeeklyTimesheetVarianceSummary].[Entity])
) ON
([qryPayrollDiffSummary].[Entity]=[qryWeeklyTimesheetVarianceSummary].[E
ntity]) AND
([qryPayrollDiffSummary].[Month]=[qryWeeklyTimesheetVarianceSummary].[Mo
nth]) AND
([qryPayrollDiffSummary].[Year]=[qryWeeklyTimesheetVarianceSummary].[Yea
r]) " + _
"WHERE [qryWeeklyTimesheetVarianceSummary].[Entity] = "00" And
[qryWeeklyTimesheetVarianceSummary].[Month] = 10 And
[qryWeeklyTimesheetVarianceSummary].[Year] = 2006 " + _
"GROUP BY [qryWeeklyTimesheetVarianceSummary].[Year],
[qryWeeklyTimesheetVarianceSummary].[Month],
[qryWeeklyTimesheetVarianceSummary].[Entity]; "

Me.RecordSource = strSQL




Made no difference.  I still got the Syntax Error.

:(

Keith E. Williamson | Assist. Controller| kwilliamson at rtkl.com

RTKL Associates Inc. | 901 South Bond Street | Baltimore, Maryland
21231-3305

410-537-6098 direct | 410-276-2136 fax | www.rtkl.com


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gary Kjos
Sent: Wednesday, December 20, 2006 1:51 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Unbound Subform Revisited

There is a line length limit in the VB editor. you have to use a quote
and a continuation terminator at the end of each line for long SQL
when you build it. I would usually DIM a string field like strSQL (DIM
strSQL as String) and then populate that wih something like this

   strSQL = "INSERT INTO tblExtItemCost ( INVENTORY_ITEM_ID,
ORGANIZATION_ID, ITEM_COST ) " + _
    "SELECT BOM_CST_ITEM_COSTS.INVENTORY_ITEM_ID,
BOM_CST_ITEM_COSTS.ORGANIZATION_ID, BOM_CST_ITEM_COSTS.ITEM_COST " + _
    "FROM BOM_CST_ITEM_COSTS INNER JOIN INV_MTL_SYSTEM_ITEMS_B ON
(BOM_CST_ITEM_COSTS.ORGANIZATION_ID =
INV_MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID) AND
(BOM_CST_ITEM_COSTS.INVENTORY_ITEM_ID =
INV_MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID);"

Of course email is gonna wrap thos lines eve SHORTER but you can see
that the " + _ is the end of each line

Then the strSQL gets loaded into your record source with the
me.recordsource = strSQL or whatever.

GK