Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Recordset not updateable (2000)

    Any idea why the following query is not updateable?

    SELECT tbl_LHR_SalesHistory.*, qry_TillOperators.OpName
    FROM tbl_LHR_SalesHistory LEFT JOIN qry_TillOperators ON tbl_LHR_SalesHistory.sales_person = qry_TillOperators.SPNumber;

    tbl_LHR_SalesHistory is a table. I am displaying all the fields in a form, hence the *.
    qry_TillOperators is a query that enables me to link the table tbl_TillOperators to the table tbl_LHR_SalesHistory.
    The linking fields between the tables are both text, but the field in tbl_LHR_SalesHistory has leading zeros and the field in tbl_TillOperators does not, so I have used qry_TillOperators, which adds the leading zeros to the linking field, rather than tbl_TillOperators in the problem query.
    qry_TillOperators is blow:

    SELECT [Last Name] & ", " & [First name] AS OpName, tbl_TillOperators.[Employee no], tbl_TillOperators.[Cashier number], Right("00000000000" & [Cashier number],11) AS SPNumber
    FROM tbl_TillOperators;

    If it is the Right("00000000000" & [Cashier number],11) expression on the linked field that is causing the trouble, is there another way to connect the tables and still have an updateable query?

    Thanks,

    Ian

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Recordset not updateable (2000)

    You must link directly on the Cashier number field, and there must be a unique index on this field. To make direct linking possible:

    Option 1: change the sales_person field in tbl_LHR_SalesHistory and the Cashier number field in tbl_TillOperators table to number fields.
    - or -
    Option 2: update the Cashier number field in the tbl_TillOperators table to include leading zeros.

    This is probably not what you wanted to hear, sorry.

Posting Permissions

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