Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004
    Posts
    173

    Unanswered: Datasheet Dilemma

    I have a regular form with a Datasheet Subform on it that shows a list of Inventory Items..

    the textboxes in the datasheet are bound to a table - tblStockItems

    the fields are...

    StockID - stockno - description - price - adjustmentdate - adjustment - comments

    ----

    the goal is to edit the adjustmentdate - adjustment and comments fields

    this works as long as the recordsource is the actual table tblStockItems or a query based on just that table....

    ----

    Here is the problem....

    what I need is to also display on the datasheet are values from a query that calculates the inventory on hand...

    qryOnHand

    the fields are named: stockID - onhand - backordered

    ----

    if I link the tblStock to a query based on StockID, I can see the onhand and backordered info, but then I cannot edit any of the fields on the datasheet.

    --------------------

    is there a way to get the calculated values from qryOnHand to show on the datasheet and still allow me to edit the data in the textboxes bound to the table tblStockItems?

    Note: this is an an Access ADP, but an Access Solution can be made to work as well...

    any help is appreciated - AB

  2. #2
    Join Date
    Nov 2003
    Posts
    16
    I'm puzzled - I've tried doing it in Access 2002 and it works fine. I can update the underlying tables despite having two tables in the query - or am I missing something?

  3. #3
    Join Date
    Aug 2004
    Posts
    173

    Same Here - I can get it to work in Access but not in the ADP??

    It looks like I need to do some research, as i can get it to work in Access .mdb, but the same data and queries, or Views are not updateable in the .adp....

    AB


    Quote Originally Posted by jklawrence
    I'm puzzled - I've tried doing it in Access 2002 and it works fine. I can update the underlying tables despite having two tables in the query - or am I missing something?

  4. #4
    Join Date
    Aug 2004
    Posts
    173

    Problem Solved!!

    A SQL Guru that I work with suggested that any query, or view involved should always include the primary key, whether it is used or not.

    When I added the primary key to this, and to other queries, or views used I was able to edit the recordset!!!!!

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Your Guru is on the money, if you want a query to be updateable then the primary key of the affected table(s) must be included.

Posting Permissions

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