Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Report and subreport issues

Posted on 2011-09-08
12
Medium Priority
?
269 Views
Last Modified: 2013-11-27
Great Day! I am trying to print an invoice for a customer who has had repair work on his/her vehicle. The Report identifies customer information and other information relative to the repair: mileagein; datein; service writer; etc. I have 2 subforms:

One displays the repair items that were performed during the service: oil change; replace front brake pads; etc.

the other one displays the parts that were purchased for the repair. In the above example, it would be oil, oil filter, and brake pads.

When I use the following filer:

stFilter = "CustomerID=" & Me.CustomerID & "AND [RepairOrderID] =" & Me.RepairOrderId

the report pulls up the same invoice, no matter which CustomerID and RepairID is used as the filter, but it asks for a paramer to open the report: It is asking for a table, which seems odd to me, and the name of it is tblRepair-RepairOrder.

I've struggled with this for a few days, and once you look at it, you will see why.

I am enclosing the ClickEvent code along with the DB. To open the form that calls the Invoice (report), click on frmRepairOrder-Select4Report2, and then click on a repair order and click the "Open Report" button.

Thanks for your assistance in advance.
Private Sub btnOpen_Report_Click()

    Dim stDocName As String
    Dim stFilter As String
    Dim stDateRange As String
    
    'set conditions
    stDocName = "rptInvoice"
    stFilter = "CustomerID=" & Me.CustomerID & "AND [RepairOrderID] =" & Me.RepairOrderId
    
    'Open report
    DoCmd.OpenReport stDocName, acPreview, WhereCondition:=stFilter

End Sub

Open in new window

PrintInvoice.zip
0
Comment
Question by:yddadsjd95
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
12 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 36504618
There is almost certainly more to it than this but you need a space in front of AND

" AND .....

Just as a comment, your filter statement assumes that customerid and repairorderid are both numbers.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36504641
again, see the posts toward the end of your previous question...

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Coding-Macros/Q_27297948.html#a36504409


...Perhaps you should wait on this Q until that Q is resolved, ...to avoid contusion...


0
 

Author Comment

by:yddadsjd95
ID: 36505331
okay, I'm working on it.
0
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 

Author Comment

by:yddadsjd95
ID: 36522797
I haven't forgotten about this, but fixing some other errors of poor design had created new bugs that I must exterminate before getting back to this one.
0
 

Author Comment

by:yddadsjd95
ID: 36572927
Okay, I'm back on this issue. Since I submitted this question, it was recommended that I rename most of my tables for clarity's sake, which I did. I was hopeful that those modifications would make it easier to figure out the problem in this instance, but no such good fortune today. I did however get rid of the parameter issue, but now the report is displaying more than 100 reports, when there should only be 11.

I have enclosed a copy of the form with all associated tables and queries. Open rptInvoice2.

Thanks for your assistance,

David
Independent-Auto---EE001---Copy-.zip
0
 

Author Comment

by:yddadsjd95
ID: 36573105
FYI, I initially had 9 tables in the Query, but I tried to reduce the number of tables be creating a number of queries that could be used. Consequently, I went from 9 tables to two tables and 3 queries.
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 2000 total points
ID: 36574126
1. What size paper are you using?
The page setup is Portrait for orientation, and Letter for the paper size, yet the Report width is 10 inches...
Perhaps this is why you are getting extra pages.

2. There are quite a few things here that make this system more complex than it has to be.
a. First, for whatever design and/or relational reasons, One Job ID in the main report can encompass more than one vehicle.
(ex JobID 59, encompasses two cars under the same Vehicle ID (5)
YearMakeModel      ModYear      Color      VIN      Tag-State
1990 / MERCEDES / SDL350      1990      Beige      HMVD65298736TY78H      MDGWT730
1999 / MERCEDES / E350      1999      Sky Blue            MDBEV 821
...so it seems that you might want to start here in your troubleshooting.

b. The query for the main report contains data that is already in the subreports, and hence is realy not needed, and complicates things more than needed.
In Other words, the main Report Recordsource need only contain Customer and WorkOrder data.
This will be linked to the subreports via the JobID (WorkoderID or whatever you are calling it...)

Again, I stated early on that you should take a close look at the Northwind Sample DB.
It contains a solid example of this type of situation.
(One Order, many details...)

JeffCoachman
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 total points
ID: 36574983
FWIW, Here is the design approach I would take...
Access-EEQ27298173ReportCustomer.mdb
0
 

Author Comment

by:yddadsjd95
ID: 36576469
Thanks Jeff, It has been a long day, and I am only half through, but I'll get back to this in a little bit.

r/David
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36576540
just remember,...

You can't really take what I,(or any experts here), say as Gospel.
Remember, we don't know the full scope of your application's Design or its requirements, and/or constraints.
0
 

Author Closing Comment

by:yddadsjd95
ID: 36580273
Thank you Jeff for coming to my aid. Your solution answered my question, but I see now that I should have included the form that called the report, because now there are three records that are showing up in the report as not having in data in them. Anyway, you live and your learn. I will rephrase the question in a new question.

I am really learning a lot though, so all is not lost :-).

r/David
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36581615
ok
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question