jlcannon
asked on
Error running report in Access 2007
I have a form that has a listbox on it and a button that says generate report. When I amke a selection and click the button it run a sub that is posted below.
Private Sub BMLreport_Click()
On Error GoTo Err_BMLreport_Click
Dim stDocName As String
stDocName = "rptBMLAgreementSummary"
DoCmd.OpenReport stDocName, acPreview, "qryBMLReportQuery", "[BusinessName Field] = '" + List1.Value + "'"
Exit_BMLreport_Click:
Exit Sub
Err_BMLreport_Click:
MsgBox Err.Description
Resume Exit_BMLreport_Click
End Sub
When I click the button if gives me the following error " The Specified field '[tblMain-Link].Active' could refer to more than one table listed in the FROM clause of your SQL statement.
That being said if i go and open the query and run it manually it does show up with alot records without giving a single error.
Private Sub BMLreport_Click()
On Error GoTo Err_BMLreport_Click
Dim stDocName As String
stDocName = "rptBMLAgreementSummary"
DoCmd.OpenReport stDocName, acPreview, "qryBMLReportQuery", "[BusinessName Field] = '" + List1.Value + "'"
Exit_BMLreport_Click:
Exit Sub
Err_BMLreport_Click:
MsgBox Err.Description
Resume Exit_BMLreport_Click
End Sub
When I click the button if gives me the following error " The Specified field '[tblMain-Link].Active' could refer to more than one table listed in the FROM clause of your SQL statement.
That being said if i go and open the query and run it manually it does show up with alot records without giving a single error.
ASKER
SELECT qryBusinessList.[BusinessN
FROM qryBusinessList INNER JOIN [tblMain-Link] ON qryBusinessList.[BusinessN
WHERE ((([tblMain-Link].Active)=
FROM qryBusinessList INNER JOIN [tblMain-Link] ON qryBusinessList.[BusinessN
WHERE ((([tblMain-Link].Active)=
Is the field [tblMain-Link].Active used in the report for grouping?
Well ... seems the issue would be in bold below. You are kind of doing a self join it would seem ?
SELECT qryBusinessList.[BusinessN
[tblMain-Link].Site,
[tblMain-Link].Location,
[tblMain-Link].PlantName,
[tblMain-Link].[Related Utility],
[tblMain-Link].[Defect Type],
[tblMain-Link].[Final Agreement Date],
[tblMain-Link].[BML Summary],
[tblMain-Link].Document_Li
[tblMain-Link].[Notice Type],
[tblMain-Link].Active
FROM qryBusinessList
INNER JOIN [tblMain-Link]
ON qryBusinessList.[BusinessN
= [tblMain-Link].PartnerBusi
WHERE ((([tblMain-Link].Active)=Yes));
SELECT qryBusinessList.[BusinessN
[tblMain-Link].Site,
[tblMain-Link].Location,
[tblMain-Link].PlantName,
[tblMain-Link].[Related Utility],
[tblMain-Link].[Defect Type],
[tblMain-Link].[Final Agreement Date],
[tblMain-Link].[BML Summary],
[tblMain-Link].Document_Li
[tblMain-Link].[Notice Type],
[tblMain-Link].Active
FROM qryBusinessList
INNER JOIN [tblMain-Link]
ON qryBusinessList.[BusinessN
= [tblMain-Link].PartnerBusi
WHERE ((([tblMain-Link].Active)=Yes));
What is the recordsource preoperty of your report?
What do you have in the Report Grouping & Sorting dialog ?
mx
mx
Ignore what I said about self join ...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@DatabaseMW: Group on Notice Type then Sort by Location
then Sort by PlantName
@mbixup The Record source in the qry i posted in the previous post. its called qryBMLReportQuery.
@imnorie, not that I see.
then Sort by PlantName
@mbixup The Record source in the qry i posted in the previous post. its called qryBMLReportQuery.
@imnorie, not that I see.
Have you checked the grouping as I suggested?
Also, you could try the query without the WHERE clause or you could add a parameter in the query that takes the value from the listbox on the form.
Also, you could try the query without the WHERE clause or you could add a parameter in the query that takes the value from the listbox on the form.
ASKER
@imnorie I need the WHERE becuase I only want the ones that are active.
I think DatabaseMX has got it.
Oops, meant mbizup.
<@mbixup The Record source in the qry i posted in the previous post. its called qryBMLReportQuery.>
Okay - since it is already in the recordsource property, simply change your code to this:
DoCmd.OpenReport stDocName, acPreview, , "[BusinessName Field] = '" + List1.Value + "'"
Okay - since it is already in the recordsource property, simply change your code to this:
DoCmd.OpenReport stDocName, acPreview, , "[BusinessName Field] = '" + List1.Value + "'"
ASKER
on a side note if I take the where out it works. so how can I tell it I only want the ones where active = yes?
ASKER
Thank you all for the help and this one solved the issue completely. thank you...
Add the criteria for that field to the where clause in the code.
"[BusinessName Field] = '" + List1.Value + "' AND [tblMain-Link].Active=Yes"
"[BusinessName Field] = '" + List1.Value + "' AND [tblMain-Link].Active=Yes"
jlcannon,
The where clause is not a problem - with the query as you stated in the recordsource property of your report.
The WhereCondition parameter of your openReport statement (bolded below) simply suplies additional criteria:
DoCmd.OpenReport stDocName, acPreview, , "[BusinessName Field] = '" + List1.Value + "'"
That combination - your recordsource query with the Where Clause and the OpenReport statement with the addtional criteria - works in cases like this
The where clause is not a problem - with the query as you stated in the recordsource property of your report.
The WhereCondition parameter of your openReport statement (bolded below) simply suplies additional criteria:
DoCmd.OpenReport stDocName, acPreview, , "[BusinessName Field] = '" + List1.Value + "'"
That combination - your recordsource query with the Where Clause and the OpenReport statement with the addtional criteria - works in cases like this
mx