[AccessD] Unbound Subform Revisited

Gary Kjos garykjos at gmail.com
Wed Dec 20 11:00:51 CST 2006


Perhaps you need quotes around the sql string?

GK

On 12/20/06, Keith Williamson <Kwilliamson at rtkl.com> wrote:
> Yeah.  That is what is funny.  That is how I got the Sql statement in
> the first place.  I had a bound form that worked, and simply copied the
> recordsource.  I used the same recordsource statement in my event for
> the click button.
>
> And when I copy that same sql statement into a new query....it works
> fine.
>
> ??
>
> 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 Beach Access
> Software
> Sent: Wednesday, December 20, 2006 11:33 AM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Unbound Subform Revisited
>
> Keith:
>
> Sometimes when I can't spot a problem in a lengthy SQL statement like
> this I
> paste it into the SQL view of a new query and then switch to the design
> view.  Sometimes you can spot the problem that way.
>
> I would also paste the original record source SQL and the modified SQL
> into
> WordPad and eyeball them for differences.  The line breaks should occur
> in
> roughly the same places so you can start by checking the length of each
> and
> the end of each line to find the differences.
>
> HTH
>
> Rocky Smolin
> Beach Access Software
> 858-259-4334
> www.e-z-mrp.com
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Keith
> Williamson
> Sent: Wednesday, December 20, 2006 8:14 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Unbound Subform Revisited
>
> Here is the sql statement:
>
> me.form.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] = "00" And
> [qryWeeklyTimesheetVarianceSummary].[Month] = 10 And
> [qryWeeklyTimesheetVarianceSummary].[Year] = 2006
> GROUP BY [qryWeeklyTimesheetVarianceSummary].[Year],
> [qryWeeklyTimesheetVarianceSummary].[Month],
> [qryWeeklyTimesheetVarianceSummary].[Entity];)
>
> Me.Form.Requery
>
>
> Anyone?  Anyone?
>
> :)
>
> 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 Beach Access
> Software
> Sent: Wednesday, December 20, 2006 10:37 AM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Unbound Subform Revisited
>
> Keith:
>
> Put a debug statement to print the SQL string right before the
> me.recordsource = and post it.  Someone will see that there's probably a
> syntax/punctuation error.
>
> Rocky Smolin
> Beach Access Software
> 858-259-4334
> www.e-z-mrp.com
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Keith
> Williamson
> Sent: Wednesday, December 20, 2006 7:05 AM
> To: Access Developers discussion and problem solving
> Subject: [AccessD] Unbound Subform Revisited
>
> Hey Guys,
>
>
>
> If I want to change a bound form to an unbound form, am I going about it
> correctly if I do the following:
>
>
>
> 1)       Add (3) unbound text fields for variables in form header;
>
> 2)       Delete Record Source for form;
>
> 3)       Add button to form header;
>
> 4)       Add Event to button, to set form.recordsource = (original sql
> statement for record source, deleted in step 2, but also add where
> statements for the (3) variables in the unbound text fields)
>
>
>
> Shouldn't this run?  I keep getting a syntax error on my sql statement,
> when I click the button.  Even if I change the sql statement to exactly
> equal the original recordsource statement....I get the syntax error.
>
>
>
> ??
>
>
>
> 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/>
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.5.432 / Virus Database: 268.15.25/593 - Release Date:
> 12/19/2006
> 1:17 PM
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.5.432 / Virus Database: 268.15.25/593 - Release Date:
> 12/19/2006
> 1:17 PM
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>


-- 
Gary Kjos
garykjos at gmail.com



More information about the AccessD mailing list