Problem with Filter

try this:

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

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!

Nick ... the OP is opening a Report, so doubtful there is any Sub Form involved :-)

mx

@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 :)

"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 ....

I'm just confused why you can't just use:
stFilter = "RepairOrderID=" & Me.RepairOrderId


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

@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

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

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 :-).

yddadsjd95:

Did you try my first post ?

mx

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.

yddadsjd95: Please answer my question:

Did you try my first post ?

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
 

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

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.

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

"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    

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

please try what I suggest @ http:#a36505881 

mx

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 :-).

see image
Capture1.gif

See attached db @ http:#a36506216 ...

mx

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.

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

"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

"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

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

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.

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

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.

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.

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

Thanks man.

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

Nick67