Multi Table Recordset Problem (Not Solved) 
Author Message
 Multi Table Recordset Problem (Not Solved)

Hi,

****************************************************************************
*************
*  Note: I don't want to Add Records in ItemMas becuase a Record Already
*   *  Exist in that Table.
*
****************************************************************************
*************

I am using following.
1.    VB6
2.    Microsoft ActiveX Data Object 2.1 Library
3.    Access 2000

I am generating recordset from two tables and providing that recordset to
DataGrid to Adding/Editing/Deleting etc.

My Table are as follows.
        Table Name            Primary Key                    Relationship
1.     ItemMas,                 ItemCode                         one
2.     InvoiceProduct       DocumentNo+Sno          Many

My Recordset Property are as follows.

    RsInvoiceProduct.CursorLocation = adUseClient
    RsInvoiceProduct.CursorType = adOpenDynamic
    RsInvoiceProduct.LockType = adLockBatchOptimistic

I am Generating recordset with following SQL command.

"SELECT InvoiceProduct.DocumentNo, InvoiceProduct.SNo,
InvoiceProduct.ItemCode, ItemMas.ItemName, ItemMas.Unit, InvoiceProduct.Qty,
InvoiceProduct.Rate, InvoiceProduct.Per, InvoiceProduct.Amount FROM ItemMas
INNER JOIN InvoiceProduct ON ItemMas.ItemCode = InvoiceProduct.ItemCode
WHERE InvoiceProduct.DocumentNo=' " & Me.DocumentNo & " ' "

Where Me.Document No. is Variable and it value contain anything like
"0000001" etc.

****************************************************************************
*************
*  Note: I don't want to Add Records in ItemMas becuase a Record Already
*   *  Exist in that Table.
*
****************************************************************************
*************

My Problem is as follows.
 1.    When User is Entering Item Code through DataGrid then the Value of
        ItemName & Unit Field of Recordset is not populated automatically.
        If I want to Populate automatically is there any method.

 2.   After Entering detail by DataGrid when I tried to Use "UpdateBatch"
       method then it Gives following message.
       "Index or Primary Key value should not be blank."

        If I use recordset only from one table it is working fine.

        I tried to Use ADO Dynamic Property
        "Unique Table" = "InvoiceProduct",
        "Unique Schema" = "Admin" (Name of Table Owner"),
        "Uniq Catalog" =  "db1.mdb"
        then I receive error message
        "Non-existent or under-specified  UNIQUE TABLE."

        In ADO help they have not given example how to set Property of
        Recordset.

Please help me.

Sanjay Shah



Fri, 06 May 2005 14:12:59 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. Newbie: problems using a multi-table Access DB and Recordsets

2. Multi Table Recordset Problem

3. Inserting into multi-table recordset with .updatebatch

4. Need help with multi-table recordset

5. IN and FROM problem solved, MSYS...tables discovered

6. VB Problem....Easy to solve, but not for ME -((

7. Multi tables vs multi mdb files

8. Not getting data using WHERE clause between table and recordset

9. recordset does not update table!

10. Connect report to recordset, not to table - crepor~1.txt [01/01]

11. How to select from another recordset, not from existing table

12. New Recordset - Not attached to a table?

 

 
Powered by phpBB® Forum Software