[AccessD] Unbound Subform Revisited

Keith Williamson Kwilliamson at RTKL.com
Wed Dec 20 10:51:44 CST 2006


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




More information about the AccessD mailing list