[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • Last Modified:

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.
0
jlcannon
Asked:
jlcannon
  • 5
  • 5
  • 4
  • +1
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Can you post the SQL for the query ?

mx
0
 
jlcannonAuthor Commented:
SELECT qryBusinessList.[BusinessName Field], [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_Links_2, [tblMain-Link].[Notice Type], [tblMain-Link].Active
FROM qryBusinessList INNER JOIN [tblMain-Link] ON qryBusinessList.[BusinessName Field] = [tblMain-Link].PartnerBusiness
WHERE ((([tblMain-Link].Active)=Yes));
0
 
NorieCommented:
Is the field [tblMain-Link].Active used in the report for grouping?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Well ... seems the issue would be in bold below.  You are kind of doing a self join it would seem ?

SELECT qryBusinessList.[BusinessName Field],
    [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_Links_2,
    [tblMain-Link].[Notice Type],
    [tblMain-Link].Active


FROM qryBusinessList
    INNER JOIN [tblMain-Link]
        ON qryBusinessList.[BusinessName Field]
                = [tblMain-Link].PartnerBusiness


WHERE ((([tblMain-Link].Active)=Yes));
0
 
mbizupCommented:
What is the recordsource  preoperty of your report?

0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
What do you have in the Report Grouping & Sorting dialog ?

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Ignore what I said about self join ...

0
 
mbizupCommented:
Make a backup and try this...

Use qryBMLReportQuery as the recordsource of your report, and change your open report statement to:

DoCmd.OpenReport stDocName, acPreview, , "[BusinessName Field] = '" + List1.Value + "'"


(Guessing that your query does not belong in the Filter parameter of the OpenReport statement)
0
 
jlcannonAuthor Commented:
@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.
0
 
NorieCommented:
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.
0
 
jlcannonAuthor Commented:
@imnorie I need the WHERE becuase I only want the ones that are active.
0
 
NorieCommented:
I think DatabaseMX has got it.
0
 
NorieCommented:
Oops, meant mbizup.
0
 
mbizupCommented:
<@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 + "'"

0
 
jlcannonAuthor Commented:
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?
0
 
jlcannonAuthor Commented:
Thank you all for the help and this one solved the issue completely. thank you...
0
 
NorieCommented:
Add the criteria for that field to the where clause in the code.

"[BusinessName Field] = '" + List1.Value + "' AND [tblMain-Link].Active=Yes"
0
 
mbizupCommented:
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
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 5
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now