Problem with creating a table in Access from existing table 
Author Message
 Problem with creating a table in Access from existing table

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

    Key ClassId   Numeric
           ClassDescr Text     Indexed

    Key CatId Numeric
           CatDescr Text Indexed

  Key ClassId  Numeric
  Key CatId     Numeric

  Key ClassId  Numeric
  Key CatID    Numeric
  Key ProdId  Numberic

  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 = & _

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

With rsClassCatTbl
    .Fields ("ClassID").Value = "rsClassTbl!ClassId"
    .Fields ("CatID").Value = "rsCatTbl!CatID"
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). _

    If SSTab1.Tab = 0 Then
        'Set mDbPricebook = DBEngine.Workspaces(0). _

        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 = " & _

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


            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 = " & _
                mNode.Text = rsProductTbl!ProductID
                mNode.Key = CInt(rsProductTbl!ProductID) & " ID"
                mNode.Image = "closed"




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

Sat, 21 Apr 2001 03:00:00 GMT  
 [ 1 post ] 

 Relevant Pages 

1. Create new field in existing table exactly like field in second table

2. How to modified existing field and add new field in existing table for Access database

3. HELP: Need help in creating a table from a table within Access

4. Error 3211: Problems with DROP Table after CREATE Table

5. Problem using DataGrid after Creating Table in Access

6. Problem Creating Access DB Tables

7. Problems creating a table based on a stored Query in an Access-MDB

8. Create an Index USING CODE on existing table

9. creating heirarchy chart in report from existing table

10. Creating Hierarchy chart in report from existing Table.

11. creating record sets in pre existing tables

12. Create New Document and Copy Table from Existing Document


Powered by phpBB® Forum Software