Simple Question.. I hope :) 1

[Borian]

Currently I have built a Service Management Database for the trucking company I work for. I figured out how to muddle through creating the forms and querries to make it work the way I wanted it. Now for my current problem.

How do I make a report that will do the following.

Tables/Forms/Querries in use.
Tables:
1. Tractor/Trailer Table - Parent Table which holds information about the Equipment
2. Tractor/Trailer Service Orders - Child Table which holds the information about the Service work being performed.
3. Tractor/Trailer Parts - A Grandchild Table linked to the Service Order for all parts used in that Service Order. Currently I am also linking it to the Parent table but it requires a manual entry of the Unit Number (Parent Master Index)
*** The relationships are Parent Table (Primary Key = Unit Number) --> Child Table (Primary Key = Service Order Number (Auto assigned #) with a relationship to Parent Table using Unit Number)--> Grandchild Table (Primary Key = Part Number and relationships to Parent and Child Tables) ***

Queries:
I have two querries to pull everything into format which allowed me to created the forms.

Forms:
I finally have Three forms which are being used in a linked form format. There is the main form and two subforms. This allowed me the format I needed.

Finally the question at hand!
I need a report which will print the Unit information, Service Order information and the parts replaced on that service order. I only want to print the Tractor Information, Service order which is currently loaded and the parts associated with that service order.

I know I'm long winded but for those of you who have stuck with me thanks for reading and I hope someone can help me with this.

 

[Borian]

Too much information?
Not enough information?

 

[dhookom]

Did you know that you can use subreports exactly the same as subforms?
I have no idea what you mean by "Tractor Information, Service order which is currently loaded and the parts associated with that service order"

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Borian]

The information I am looking to print is as follows:

Tractor or Unit information (Including year, make, model, vin#, ect.) This should be only for unit associated with service order.

Service Order Information - This is the critical piece. I just finished entering in a service order and the associated parts. Now I need to print a service order report before continuing on to the next. So I want to be able to print all service information and parts associated with the service order.

*** No I didn't know about the subreports. Perhaps it might be the way to go, but I might still be looking at the same problem.

Unit Number (Key is unit# ) / Service Order Parts
(Key is SrvID)/
Parts
Need report to look like

SERVICE ORDER

Unit Information
------------------------
Service Order Information
-----------------------------
Parts Replaced # $$$$$$$
-----------
Total $$$$$$$$$$
=============

Again thanks for any help.

 

[dhookom]

Have you created a report?
Is your report based on the three tables combined in a single query?
Are you having trouble limiting the report to only the currently displayed Service Order?
Do you have a unique field/primary key in the Service Order table? If so, what is its type and field name?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Borian]

Yes report is created
Yes report is based upon single querry with all three tables
Yes I am having a terrible time limiting the report to just record being diplayed.
Yes there is a unique key for service order.
Repair ID - Auto number - Long Integer - Indexed no Duplicates.

 

[dhookom]

You can use a wizard to add a command button that will open the report. Once this is done, you can modify the code:
[blue]

    Dim strWhere as String
    'assuming your form has a text box "txtRepairID" _
        that is bound to the field [Repair ID]
    strWhere = "[Repair ID] = " & & Me.txtRepairID
    'Change the DoCmd.OpenReport line
    DoCmd.OpenReport "rptYourReport",acPreview, , strWhere

[/blue]

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[MakeItSo]

Do you have multiple service orders per tractor trailer?
If no: You could use a sub-subreport on the service orders subreport.
This way you could link from parent to sub to display service orders and from sub to sub-sub to get all your associated parts.

Will that apply?

Cheers,
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[Borian]

Thank you everyone and here's where I am at with it, though it is still not working. Perhaps someone can give me advice on what I am doing wrong.

Using Access 2000 in case it matters.

Private Sub Command15_Click()
On Error GoTo Err_Command15_Click
Dim strWhere As String
Dim stDocName As String
strWhere = "[Repair ID]=" & "'" & Me![Repair ID] & "'"
stDocName = "Tractor Service Order"
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_Command15_Click:
Exit Sub

Err_Command15_Click:
MsgBox Err.Description
Resume Exit_Command15_Click

End Sub

I am getting an error of Data Type Mismatch in Criteria Expression error.

Thanks again,

Paul

 

[dhookom]

Paul,
I thought you stated earlier that Repair ID was an autonumber. Why did you add all the quotes to your where clause? The sample I provided didn't use these. If RepairID is text you would need to use the quotes. Since it is numeric, you don't need them. If the where clause included dates, you would use "#" as dilimiters.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Borian]

I take the quotes out and it gives me a syntax error. I looked through some of the other parts of the program and saw that some of the fields were listed in quotes so gave it a shot.

Not quiet sure what I'm doing wrong or right at this point.

That and I'm a novice in Access.

Anyway, here's what I have now and what is giving me a syntax error.

Private Sub Command15_Click()
On Error GoTo Err_Command15_Click
Dim strWhere As String
Dim stDocName As String
strWhere = [Repair ID]= & "'" & Me![Repair ID] & "'"
stDocName = "Tractor Service Order"
DoCmd.OpenReport stDocName, acPreview, , strWhere

 

[Borian]

OOops misunderstood what you were saying.

Changed to this and think it's working, my report might not be working but I think the statement is.

Here's how it looks ...
Private Sub Command15_Click()
On Error GoTo Err_Command15_Click
Dim strWhere As String
Dim stDocName As String
strWhere = "[Repair ID]= " & Me![Repair ID]
stDocName = "Tractor Service Order"
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_Command15_Click:
Exit Sub

Err_Command15_Click:
MsgBox Err.Description
Resume Exit_Command15_Click

End Sub

This seems to be working. I'll do some work on the reports and see what happens

Thanks for your help.

Paul

 

[Borian]

Hmm seem to be getting 4 pages of same information.

printed same repair id 17 times.

Any ideas?

Paul

 

[Borian]

Ok..

Fixed the duplication of repair ID's. Now I'm getting all parts replaced on all service orders for the particular unit.

I'll check back tomorrow and see if anyone has any ideas about this one.

Thanks for all your help Duane. This project is comming to an end finally *<|

Paul

 

[dhookom]

Are you using a subreport? Have you checked for duplicates in your report's record source?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Borian]

I just wanted to thank Duane again for his help in this.

I figured it out.

My biggest problem was that the RepairID in Service Order Table was AutoNumber; RepairID in Parts Table was Text. They didn't behave the same.

Any idea how to default the Unit Number to the parts table?

Paul

 

[dhookom]

Why link the grandchild to the parent. This should available through the child. Storing the parent value in the grandchild table would be redundant.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Borian]

Well there is a method to my madness, not just madness.

The thing I am looking to provide is the ability to quickly look at a parent record and see all associated grandchild records.

Example why: Parent record = Unit 45 (a Tractor). Unit 45 needs a new clutch put in, but the mechanics think they just put a clutch in this tractor a few months ago. So they open Unit 45 Record and hit parts button. Sort parts by description, scroll down to clutch and see when the last time the clutch was replaced.

If there's an easier way to do this, please let me know.

Paul

 

[dhookom]

You can always get at the parts throught the service orders. I would not have a unit ID in the parts table.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Borian]

Looking at things through the service records will work for a while, however long term I'm not sure it will work.

One of the units has already had 4 service orders on it, and they have only been using the database for 2 weeks.

Longterm, A year down the road the service tech might have to look through dozens of service records to find what they are looking for.

I tried defaulting the information but all I managed to get out of it was a #Name error displayed in the cell.

This messageboard has been a wonderful resource for me and I'm hoping there is a way to make this happen.

Paul