[AccessD] Unbound Subform Revisited

Keith Williamson Kwilliamson at RTKL.com
Wed Dec 20 12:35:17 CST 2006


Okay...this is getting more bizarre.  I keep pasting the sql statement
into the VB window....and it keeps automatically putting the statement
on (4) lines....with and end quote at the back of the first line (that
line only).  I can't get rid of the quote....nor will it let me try to
put the entire statement on one line.


grrrrrrrrrrr

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 12:01 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Unbound Subform Revisited

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
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list