Solved

Problem with Filter

Posted on 2011-09-08
34
354 Views
Last Modified: 2013-11-27
Great Day, I am trying to open a report with the following condition:

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

As noted above, I only want records that include the RepairOrderID for a specific customer. However, I am getting the following error when the form loads:

'Compile error: Method or data member not found'

If I shorten the filter to only include CustomerID, I do not get the error.


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.tblRepairOrder.RepairOrderId               
    
    'Open report
    DoCmd.OpenReport stDocName, acPreview, WhereCondition:=stFilter

End Sub

Open in new window

0
Comment
Question by:yddadsjd95
  • 12
  • 11
  • 9
  • +1
34 Comments
 
LVL 75
ID: 36503833

try this:

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

Expert Comment

by:Nick67
ID: 36504106
I'll agree with @mx
It's highly unlikely that your form has a control that will correspond to

Me.tblRepairOrder.RepairOrderId

So, what is the actual control's name that has the value you want to filter on?
And is it on a main form or a subform (just guessing like @mx)?

If you have a subform on the go and RepairOrderId is on the subform the proper syntax is

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

 If that is indeed the case, tblRepairOrder is a pretty confusing name for a subform control!
0
 
LVL 75
ID: 36504205
Nick ... the OP is opening a Report, so doubtful there is any Sub Form involved :-)

mx
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36504352
@mx

The OP is opening a report from a button click, which pretty much means the button is on a form.
The structure of that form is a mystery to you and to me.

We both agree that there's no way in the seven bells of hades that
Me.tblRepairOrder.RepairOrderId  
is right

So what is the OP trying to do with that, given that Me.CustomerID was good and right.
I was guessing that one constraint came from the main form and one from a subform
That may explain the wonky syntax.  But I am only guessing :)
0
 
LVL 75
ID: 36504362
"The structure of that form is a mystery to you and to me."
I think you are reading between the lines too much :-)

But hey ....
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36504409
I'm just confused why you can't just use:
stFilter = "RepairOrderID=" & Me.RepairOrderId


...since the CustomerID is already included in the Repair order...


0
 
LVL 26

Expert Comment

by:Nick67
ID: 36504446
@boag2000
You know what they say about assume  ...  ass u me
Maybe RepairOrderId is a unique Access AutoNumber and therefore CustomerID won't be relevant.
or maybe not.

But yeah, on the face of it, CustomerID might be redundant
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36504623
Nick,
Yeah...
Point taken...

...But that might be part of the issue.
If one Customer can have many RepairOrders, then CustomerID "Should" be in the Repair Order...
If it is not, then we may have other issues here...
...


Jeff
0
 

Author Comment

by:yddadsjd95
ID: 36505319
Yes Jeff, CustomerID is in RepairOrders

As I try to logically think this through:

1. A customer has a repair order processed for vehicle service
2. The service can have more than one service item - brakes, oil change, etc, so I placed service items in a separate table called tblRepair-RepairOrder.
3. Also, the service can require more than one part, so I placed the parts for the service in a seperate table name tblParts-RepairOrder

I know the 9 tables that I have in the query for the rptInvoice is a lot and is obviously confusing me, but I need some information from each table.

Still stuck :-).
0
 
LVL 75
ID: 36505571
yddadsjd95:

Did you try my first post ?

mx
0
 

Author Comment

by:yddadsjd95
ID: 36505645
Boag2000, I tried your suggested code - stFilter = "RepairOrderID=" & Me.RepairOrderId, but I have 3 RepairOrderID:  tblRepairOrder.RepairOrderID (table of all customers’ vehicle repairs); tblRepair-RepairOrder .RepairOrderID(table of repair items on a particular customer vehicle repair – brakes, oil change, etc.); and tblParts-RepairOrder.RepairOrderID (table of all parts for a particular repair – spark plugs, oil, brake pads, etc.), and the three tables are related by RepairOrderID

I tried stFilter = "RepairOrderID=" &  Me.tblRepairOrder.RepairOrderId       

And I got the following error message:

Compile error
Method or data member not found

Which was referring to tblRepairOrder

And I tried

I tried stFilter = "RepairOrderID=" &  Me.tblRepairOrder.Form!RepairOrderId

And received the same error

Not sure how to solve this.




0
 
LVL 75
ID: 36505675
yddadsjd95: Please answer my question:

Did you try my first post ?
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36505768
The filter is being built with the value of some control on your form
There is no way in Hades that you have a control named
tblRepairOrder.RepairOrderId
Hence Me.tblRepairOrder.RepairOrderId makes absolutely no sense to Access and you are getting your error message

What is the name of the control that contains the value you want to filter by?
I have no doubt that it is ControlSource may be tblRepairOrder.RepairOrderId but that isn't its name

You need
stFilter = "RepairOrderID=" &  Me.WhateverTheActualNameOfTheControlInQuestionIs
 
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36505803
I stand corrected
<There is no way in Hades that you have a control named
tblRepairOrder.RepairOrderId >
Having just created controls with exactly that kind of name.

My bad

Now let me try and use such a monstrosity in a filter....
Hang tough
0
 

Author Comment

by:yddadsjd95
ID: 36505832
DatabaseMX, yes I did and and I received the following error message

Compile error
the specified field '[RepairOrderID]' could refer to more than one table listed in the from clause of your SQL statement

Which was referring to RepairOrderID

 so I tried

      stFilter = "CustomerID=" & Me.CustomerID & " AND [RepairOrderID] = " & Form![frmRepairOrder-Select4Report2].RepairOrderId

And Access gave me an error message saying that it could not find frmRepairOrder-Select4Report2 that was identified in my esxpression.


0
 
LVL 26

Expert Comment

by:Nick67
ID: 36505844
Ahhhhhhhhhhhh...
tblRepairOrder.RepairOrderId will be the name of the control

BUT

That period isn't going to fly in VBA code (and probably not in SQL either.
Try
stFilter = "RepairOrderID=" &  Me.tblRepairOrder_RepairOrderId

The VBA needs that naughty period gone.
Question: did you build that string by typing or by Intellisense.
Because Intellisense catches that problem

0
 
LVL 75
ID: 36505881
"DatabaseMX, yes I did and and I received the following error message"

Then make these changes:

1) Give the Text box that holds RepairOrderId a Name of txtRepairOrderId    
Make the 'correct' RepairOrderId the Control Source for this text box
2) Give the Text box that holds CustomerID  a Name of txtCustomerID    
Make the 'correct' CustomerID  the Control Source for this text box

Then make this change
stFilter = "CustomerID=" & Me.txtCustomerID & " AND [RepairOrderID] = " & Me.txtRepairOrderId    

0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:yddadsjd95
ID: 36505912
Whew! Nick67, when I use your suggestion, Access is now saying that it cannot find CustomerID.

Here is the database. Open frmRepairOrder-Select4Report2 and select a car and then click the 'Open Report' button. The click event is in the 'Open Report' button.
PrintInvoice.zip
0
 
LVL 75
ID: 36505944
please try what I suggest @ http:#a36505881

mx
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 500 total points
ID: 36506216
Try the attached mdb. The report now opens ...
Note - you have to select a line item on the Form wherein data actually exists in the tables ... to get the report to so data ...

mx
PrintInvoice-MX01.zip
0
 

Author Comment

by:yddadsjd95
ID: 36506240
DatabaseMX, I tired your suggestion and Access is now asking for me to enter a parameter value:

tblRepair-RepairOrder.RepairOrderID; and
tblRepair-RepairOrder; and
Part-RepairID; and
tblRepair-RepairOrder.RepairOrderID.

I keep clicking 'OK' and when I got to the last parameter value, the report - rptInvoice - opens to a blank blue screen, and the parameter value 'tblRepair-Order.RepairOrderID  just stays on the screen, whether I enter a value or not.

FYI, I went through the query record source for the one form and the report and 2 subreports and deleted all RepairOrderID fields from all tables except the Repair Order table.

Stuck again :-).
0
 
LVL 75
ID: 36506247
see image
Capture1.gif
0
 
LVL 75
ID: 36506258
See attached db @ http:#a36506216 ...

mx
0
 

Author Comment

by:yddadsjd95
ID: 36506333
Thank you for hanging in there with me DatabaseMX. I tried your solution and I can't get the report to open from the form frmRepairOrder-Sect4Report2. Nothing happens when I click the 'Open Report' button. I do see result however, when I opn prtInvoice, however, it appears as if it is displaying some of the same records over and over again.
0
 

Author Closing Comment

by:yddadsjd95
ID: 36506458
This has been a tough oneDatabaseMX, but I see that it works. I notice that as I was creating test records, I only placed repair order data in less than half the records. One question that immediately jumps out at me is how to display a message on the invoice when no records are available, but I'll work with that and if I have any questions, I'll bring it under a separate question. The solution might bring other questions as weel, but I will address under separate question.

I want to thank everyone who helped with this problem
0
 
LVL 75
ID: 36506459
"I tried your solution and I can't get the report to open from the form frmRepairOrder-Sect4Report2."
Are you using the exact db I uploaded?  It's certainly working for me ... see screen shot.

mx
0
 
LVL 75
ID: 36506486
"I only placed repair order data in less than half the records. "
Yes.  At first I wasn't getting any data in the report - finally click on one that had data.

Note that ... among other things, I renamed controls per >> http:#a36505881.

mx
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36511354
I was looking at your sample when @mx fixed it.
If I might makes some suggestion, I hope you would consider them.
Looking at your relationships, your data is mostly well-constructed.

I'd deep-six the relationship between vehicle and customer though--
What if one customer sells a car to another customer?
What if one customer pays the bill for repairs done to another's car?

You biggest grief is with naming
You use the word 'repair' way too many times and the confusion that resulted led to your problems and this question.
I'd re-name tblRepairOrders to tblJobs, and RepairOrderID to JobID
One set down

I'd rename tblRepairs-RepairOrders to tblScopeOfJob.  It's the table where you list out what you are going to do
RepairRequestID to WorkRequestedID
RepairOrder to RepairItemPerformed

I'd rename  tblParts-RepairOrder to tblPartsUsed
Part-RepairOrderID to PartUsedID

I'd rename tblCustomerVehicles to tblVehicles
CustomerVehicleID to VehicleID
VehicleMakeID to MakeID
VehicleModelID to ModelID

I'd rename  tblVehicleMakes to tbMakes
VehicleMakeID to MakeID
VehicleMake to Make

I'd rename  tblVehicleModels tblModels
VehicleModelD to ModelID
VehicleModel to Model

Why do this?
So that all your table and field names are short and distinctive
Then you can't get confused when building your queries with WAY too many of the foreign key fields
And then your controls won't wind up with evil names that cause grief
JobID will only need to happen ONCE on the invoice

After a whack of beating the result is attached.
Look it over.
Beating it into shape took at bit.
Do me the courtesy of examining it!

Nick67


PrintInvoice-Nick67.mdb
0
 

Author Comment

by:yddadsjd95
ID: 36514715
Thank you Nick67, I have examined your database and considered your suggestion. It makes sense to me; however, I have one question before I make these changes: When I change the names of the tables, will that one change alter the name of the table everywhere in the DB? I hope I am asking the question correctly. I just want to know if I will have to find every occurrence of a table name and alter it as well.

Also, DatabaseMX, I have been looking at your solution to this issue, and I'm trying to make sense of why you gave RepairOrderID a new name - RepairOrderIdXX. I see it and it makes everything flow properly. I later discovered that the parts subReport was not printing all of the parts for a particular job, so when I altered the Link Master Field from PartID to RepairOrderIdXX, it cleared up the problem.

I have run into another slight problem that I will address in a different question, but although everything is displaying correctly, it is displaying numerous Identical invoices.  I've got Access Fatigue, so I probably will not address it until tomorrow.

0
 
LVL 26

Expert Comment

by:Nick67
ID: 36515519
Access has something called auto-correct.
It generally performs well at propogating changes.
I did take the time to clean up everything that was in your sample.
You could use it for production if you wished.

Two fundamental things that come back to bite you are improper data design--which you have for the most part avoided--and poor naming conventions.  Life is fundamentally easier if you give tables and primary keys short and distinctive names, so you don't confuse them.  You should google up some tips on VBA naming conventions and SQL naming conventions.  The periods in your control names were the proximate cause of your problem.  The ultimate cause of your problem was the query you created had fieldnames in multiple tables that were not foreign key and not unique--or you put unnecessary foreign keys into your query.

Make a copy of your db.  Then try to make all the changes, one at a time.  Compile your code after each change.  Opne each object.  It took me some doing.  It'll take you some time too--but in the long term, you'll be much better off
0
 

Author Comment

by:yddadsjd95
ID: 36516007
Nick67, I have completed all of your suggestive changes; however, there was one unanticipated issue. I lost the relationships with all of my tables, but no problem...I think. I'll open the copy that I made at your very wise suggestion and open the relationship window and use that as a guide to make life much easier.

I'll keep you posted.
0
 

Author Comment

by:yddadsjd95
ID: 36516088
Nick67, I also had to go through and change all of my VBA code to reflect the new names of tables, forms and reports. I do have one problem that is probably unassociated with these changes, but when I go to create a new 'Job Order', formally known as 'Repair Order," although the fields for customer name and vehicle are in the query for this form and associated subforms, the Job Order form does not have any customer data displayed. I expected name, address, vehicle make, model,year, etc.

I suspect that I need to place an if statement in the On Current event that asks if this is a new record and if so, populate the field with code. Just a guess, but I'll work on it and if I have problems I will address under a new question.
0
 

Author Comment

by:yddadsjd95
ID: 36546436
Whoa Nick67! Your suggestion regarding renaming all of the tables to something short and meaningful made sense to me. I started on it a week ago when you made the suggestion. My documentation was lacking so I ran into a number of problems regarding the joins that I could not figure out. I persevered without asking any questions (patting myself on the back). Although it took me a week to figure it all out, the results are much more meaningful because I could see what my problems were.

Thanks a million. Although I had to move my first trial run with the customer a few days back, this lesson on db design was well worth it. I appreciate you taking the time to go through and make these suggestions.

Have a great rest of the day today and an even finer one tomorrow.

r/David
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36546664
Thanks man.

I am glad that the effort I put into your sample was worthwhile.

Nick67
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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 …

760 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now