Print Rows verticaly

[wjburke2]

Here’s the problem, I have two tables The Product table and the Parts table. The number of parts can very from one to ten and even have duplicates. I need to print the Parts out vertically across the page instead of horizontally. The column heading is the Part-Type stored in the Parts table and will remain the same for all configuration of the product. The column heading is printed once at the beginning of each product. The parts may vary inside the product creating Product-1, configuration a, b, c..

Product table - (Product Id, description)

Component Table - (Product Id, Part name, Part type)

Product-1desc
Type1 Type2 Type3 Type4 Type5 Type6
Product-1a Part-1 Part-2 Part-3a Part4 Part-5 Part-6
Product-1b Part-1 Part-2 Part-3b Part4 Part-5 Part-6
Product-1c Part-1 Part-2 Part-3 Part4a Part-5 Part-6

Product-2desc (product)
Type1 Type2 Type3 Type4 Type5 (Column heading)
Product-2a Part-1 Part-2 Part-8 Part-9a Part-10
Product-2b Part-1 Part-2b Part-8c Part-9 Part-10
Product-2c Part-1 Part-2c Part-8 Part-9b Part-10
Product-2d Part-1 Part-2 Part-8a Part-9 Part-10

 

[georgedwilkinson]

Sounds like you should base your report off a crosstab query.

 

[wjburke2]

Not working but thanks

Maybe I need learn more about CrossTab queries. I tried that, it gives one row for each Part type, with a sum of the parts, or the name of the first part of that type. I need to list all the parts across. Say you are building computers. Your customer wants two hard drives. They are differant drives. I can't tell the builder they just want two drives. I need to tell them the name of the two drives.

Lets look at it like this
Product 1 (single mb)
Case ,Mtherbrd,Floppy,Storage,,,,CD
Blue Case,P4mb ,mini ,flash drive,DVDR
, ,080g drive,DVD
Red Case,P4mb ,3.5 ,128g drive,DVDR
, , ,080g drive,HS RW


Product 2 (dual mb)
Case ,Mthbrd ,floppy,HD ,CD
Red Case,P4mb ,Floppy,HD1 drive,DVDR
, ,P4mb , ,HD2 drive,DVD

 

[KeithG]

Copy and paste the below function into a module and call it from your query. The function will create a comma delimeted string of the given Product ID. I have not tested it becuase I dont have a copy of the db. Also I would add some error handling.

Public Function ListCom(ProductID As Long) As String

    Dim myRec As DAO.Recordset
    Dim sSQL As String
    Dim sList As String
    
    
    sSQL = "Select * from [Component Table] Where ProductID=" & ProductID
    
    Set myRec = CurrentDb.OpenRecordset(sSQL)
    
    myRec.MoveFirst
    
    Do Until myRec.EOF
        sList = sList & myRec.Fields("Part Name")
        myRec.MoveNext
        If myRec.EOF = False Then
            sList = sList & ", "
        End If
    Loop
    
    
    ListCom = sList
    myRec.Close
    
End Function

 

[georgedwilkinson]

So this is a kind of Bill of Materials report?

 

[wjburke2]

This looks real close

It's a production report produced weekly. For scheduling mostly. Right now it is manually entered into a Excel spreadsheet. They want a program so they can do scheduling and track inventory. Now if I can figure out how to put this into a array with the first record column headings.
As you can tell I am kind new to Access. I programmed in FoxPro several years ago. Been doing Main Frame stuff for last 10 years. Thanks, a bunch I owe you one.

 

[KeithG]

Why do you need the data in an Array? What are you going to do with the Array? If you explain we can help you out.

 

[wjburke2]

I had a reply typed and I lost it during posting. I was thinking I would load array with the results of the function then use a loop to populat the report fields.
i = 1
Do until IsNull(compentName(i))
Rptcompoent(i) = arraycompoent(i)
lood next i

 

[wjburke2]

I still have a problem

I used KeithG's code (see below). I put in a module and call it from the Detail.Onformat event of my report. I have tried everything to get it to work. But it hangs on the OpenRecordset statment.
Set rsCompoent = dbs.OpenRecordset(sSQL)
The error is:
Runtime error '3061'
Too few parameters. expected 1.

I have tried many variation and researched this all day. It looks like it shoud work. but does not.
Variables list
sPkgid = ADB
sSQL = "Select * from Component Where [PkgId]=ADB"
dbs = "E:\Fulfillment Bible 10\Fulfilment 7.mdb"

--------------------------------------
Function ListCom(sPkgId) As String
Dim dbs As DAO.Database
Dim rsCompoent As DAO.Recordset
Dim sSQL As String
Dim sList As String

Set dbs = CurrentDb
sSQL = "Select * from Component Where [PkgId]=" & sPkgId

Set rsCompoent = dbs.OpenRecordset(sSQL)

rsCompoent.MoveFirst

Do Until rsCompoent.EOF
sList = sList & rsCompoent.Fields("CompName")
rsCompoent.MoveNext
If rsCompoent.EOF = False Then
sList = sList & ", "
End If
Loop

ListCom = sList
rsCompoent.Close

End Function