Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Location
    near Kansas City, MO
    Posts
    6

    Question Unanswered: Bill of Materials in Access 97

    I am trying to build a Bills of materials report from an MS Access 97 database.
    So far I can get a single level report. The report needs to be able to go up to approximately 10 levels deep.
    A couple of tables I have are;
    __ITEM_MASTER which has these fields plus several others;
    ____ITEM_NUMBER
    ____ITEM_DESCRIPTION
    __BILLS_OF_MATERIALS
    ____ITEM_PARENT
    ____ITEM_COMPONENT
    ____QUANTITY

    The format I am after is
    Level Part number Qty Description
    1........ 1111111 1 Some Really Big Object
    .2....... 1211111 1 Assembly 1
    .2....... 1221111 1 Assembly 2
    ..3...... 1222111 2 Hardware item 1
    .2....... 1231111 1 somthing else
    ..3...... 3123123 2 .........
    ...4..... 4325235 1 .........
    ...4..... 6534643 1 .........
    ....5.... 6346346 1 .........
    ..3...... 1222111 1 Hardware item 1
    .2....... 6436346 2 .......

    Does this make sense?
    I have a decent knowledge of VB6. I have experimented with vb6 on this as well, but got lost too fast since I have not worked with databases in VB before.

    Any comment or suggestions would be greatly appreciated.

    Thank you,
    Paul
    Last edited by PNEaster; 01-22-04 at 16:24.

  2. #2
    Join Date
    Nov 2003
    Posts
    267
    I would do something similar to this

    Create rsDAOLevel1 based on select Base Parent Item
    do while rsDAOLevel1 not EOF

    Create rsDAOLevel2 based on current record in rsDAOLevel1
    Do while rsDAOLevel2 Not EOf

    Create rsDAOLevel3 based on current record in rsDAOLevel2
    Do while rsDAOLevel3 Not EOf

    ....

    Loop

    Loop

    Loop


    Syntax is not correct, but this would be how I would orgainize it and the I would push the data back out to a Temp Table that would list

    Level#
    Qty
    ItemNum
    Desc


    And then use a query and report to organize it to look clean when printing it out.


    There is probably better ways, but off the top of my head I would do it this way.

    S-

  3. #3
    Join Date
    Jan 2004
    Location
    near Kansas City, MO
    Posts
    6
    I'm not sure what you mean by rsDAO....#

    I understand the loop on the levels, but is the rsDAO a query, temp table, or what?

    Please excuse my ignorance, I am new at Access and this large of a database.

  4. #4
    Join Date
    Nov 2003
    Posts
    267
    rsDAO....# is a DAO recordset

    I would recommend learning about using DAO recordsets in VBA code (with Access 97)

    If you want to code ths using VB6.0 the I would recommend learning about ADO recordset (it is suppose to be faster)


    Here is some VBA DAO sample code (VBA - Visual Basic Access)



    'Declare Variables
    Dim DAOdb As Database
    Dim DAOrsLevel1 As Recordset
    Dim DAOrsLevel2 As Recordset
    Dim DAOrsLevel3 As Recordset
    Dim sSQL1 As String
    Dim sSQL2 As String
    Dim sSQL3 As String

    'Set connection to database
    Set DAOdb = CurrentDb()
    'Specify the SQL statemetn to execute
    sSQL1 = "Select * from tblItem where tblItem.ItemNum = " & ParentNum & ";"
    'Set the recordset
    Set DAOrsLevel1 = DAOdb.OpenRecordset(sSQL1)

    Do while Not DAOrsLevel1.EOF

    '
    'Code to Add Master Level 1 Item to Temp Table
    '

    sSQL2 = "Select * from tblItem where tblItem.ItemNum = " & DOrsLevel1.fields("ComponentNum") & ";"
    'Set the recordset
    Set DAOrsLevel2 = DAOdb.OpenRecordset(sSQL2)

    Do while Not DAOrsLevel2.EOF

    '
    'Code to Add Level 2 Item to Temp Table
    '


    sSQL3 = "Select * from tblItem where tblItem.ItemNum = " & DAOrsLevel2.fields("ComponentNum") & ";"
    'Set the recordset
    Set DAOrsLevel3 = DAOdb.OpenRecordset(sSQL3)

    Do while Not DAOrsLevel3.EOF

    '
    'Code to Add Level 3 Item to Temp Table
    '

    ....

    Loop

    Loop

    Loop


    S-

  5. #5
    Join Date
    Jan 2004
    Location
    near Kansas City, MO
    Posts
    6
    Wow!

    Looks like I have some more reading to do.

    Thank you,

    Paul

  6. #6
    Join Date
    Jan 2004
    Location
    near Kansas City, MO
    Posts
    6

    Lightbulb

    This looks more like what I would like to achive eventally.

    http://www.dbforums.com/showthread.p...ight=tree+view

    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •