I am trying to create a table from other tables within the same
Database in Access.  The tables are as follows:

ClassTbl
    Key ClassId   Numeric
           ClassDescr Text     Indexed

CatTbl
    Key CatId Numeric
           CatDescr Text Indexed

ClassCatTbl
  Key ClassId  Numeric
  Key CatId     Numeric

ClassCatProdTbl
  Key ClassId  Numeric
  Key CatID    Numeric
  Key ProdId  Numberic

ProductTbl
  Key ProdId Numberic
         ProdDescr Text
         ProdListPrice  Currency
        ClassDescr Text
        CatDescr Text

The ProductTbl and ClassTbl and the CatTbl are complete files with
data in them.

I am trying to create the ClassCatTbl and ClassCatProdTbl from Data
from the ProductTbl and ClassTbl and CatTbl.

The following is part of the code I am using:

Set rsProductTbl = mDbPricebook.OpenRecordset _
OpenRecordset ("ProductTbl", dbOpenDynaset)

Set rsClassTbl = mDbPricebook.OpenRecordset _
("Select ClassId from ClassTbl  Where ClassDescr = & _
rsProductTbl!ClassDescr")

Set rsCatTbl = mDbPricebook.OpenRecordset _
("Select CatId from CatTbl Where CatDescr = & _
rsProductTbl!ClassDescr")

With rsClassCatTbl
    .AddNew
    .Fields ("ClassID").Value = "rsClassTbl!ClassId"
    .Fields ("CatID").Value = "rsCatTbl!CatID"
    .Update
End With

Then I repeat the basically the same for the rsClassCatProdTbl with
adding in  .Fields ("ProductId").Value = "rsProductTbl!ProductId"

Is this the correct way to do this.

Follow is coding using the above tables whenever I get the data in.  I
use the same opening of database coding as I do in the about program.

The problem I am having with the Set command and having to use to
Wheres   Please see --------> which point to the coding I seem to be
having a problem with.

Thanks very much.

Option Explicit
Private mDbPricebook As Database

Private Sub SSTab1_DblClick()

    Dim rsClassTbl As Recordset
    Dim rsClassCatTbl As Recordset
    Dim rsCatTbl As Recordset
    Dim rsClassCatProdTbl As Recordset
    Dim rsProductTbl As Recordset

    Dim intIndex
    Set mDbPricebook = DBEngine.Workspaces(0). _
    OpenDatabase("G:\Y2KProject\Pricebook\Pricebook.mdb")

    If SSTab1.Tab = 0 Then
        'Set mDbPricebook = DBEngine.Workspaces(0). _
        'OpenDatabase("G:\Y2KProject\Pricebook\Pricebook.mdb")

        Set rsClassTbl = mDbPricebook. _
        OpenRecordset("ClassTbl", dbOpenDynaset)

        Do Until rsClassTbl.EOF
            Set mNode = tvwDB.Nodes.Add(1, tvwChild)
            mNode.Text = rsClassTbl!ClassDescr
            mNode.Tag = "Class"
            mNode.Key = CInt(rsClassTbl!ClassId) & " ID"
            mNode.Image = "closed"
            intIndex = mNode.Index

            Set rsClassCatTbl = mDbPricebook.OpenRecordset _
            ("select * from ClassCatTbl Where ClassId = " & _
            rsClassTbl!ClassId)

        Do Until rsClassCatTbl.EOF
                Set mNode = tvwDB.Nodes. _
                Add(intIndex, tvwChild)
                Set rsCatTbl = mDbPricebook.OpenRecordset _
                ("select * from CatTbl Where CatId = " & _
                rsClassCatTbl!CatID)
                mNode.Text = rsCatTbl!CatDescr
                mNode.Key = CInt(rsClassCatTbl!CatID) & " ID"
                mNode.Image = "closed"

                rsClassCatTbl.MoveNext
            Loop

            Set rsClassCatProdTbl = mDbPricebook.OpenRecordset _
 ******>         ("select * from ClassCatProdTbl Where ClassId = " & _
*******>        "rsClassCatTbl!ClassId)" & "Where CatId = " & _
******>           rsClassCatTbl!CatID)

            Do Until rsClassCatProdTbl.EOF
                Set mNode = tvwDB.Nodes. _
                Add(intIndex, tvwChild)
                Set rsProductTbl = mDbPricebook.OpenRecordset _
                ("select * from ProductTbl Where ProductId = " & _
                rsClassCatProdTbl!ProductID)
                mNode.Text = rsProductTbl!ProductID
                mNode.Key = CInt(rsProductTbl!ProductID) & " ID"
                mNode.Image = "closed"

                rsClassCatProdTbl.MoveNext
            Loop

            rsClassTbl.MoveNext

        Loop

        ' Expand top node.
        tvwDB.Nodes(1).Expanded = True
    End If