[AccessD] Speaking of Unbound Forms

Keith Williamson Kwilliamson at RTKL.com
Tue Dec 19 16:07:22 CST 2006


I have a form, with a very complex (and time consuming query) as the
datasource.  What I am trying to do is add a couple fields in the form
header, to allow the user (me) to input Period, Year, Entity.....then
click a button to populate the form for only those records.  This way, I
don't have to sit through the query calculation for all records.

 

So....not being the expert that you guys are, I added the unbound text
boxes, and deleted the original RecordSource property.  I then modified
that same RecordSource to select "where" the fields equal the values in
the text boxes, and added to the click event, for a button:

 

me.RecordSource =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] = Me.EntityTag And
[qryWeeklyTimesheetVarianceSummary].[Month] = Me.MonthTag And
[qryWeeklyTimesheetVarianceSummary].[Year] = Me.YearTag

GROUP BY [qryWeeklyTimesheetVarianceSummary].[Year],
[qryWeeklyTimesheetVarianceSummary].[Month],
[qryWeeklyTimesheetVarianceSummary].[Entity];

 

 

Unfortunately, when I then open the form, enter info for YearTag,
PeriodTag, and EntityTag (the unbound text boxes)...and click the
button....I get a "Compile Error   Syntax Error."  Could someone stear
me into the right direction?  What am I doing wrong?

 

Thanks,

 

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
<http://www.rtkl.com/> 

 




More information about the AccessD mailing list