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

    Why is this query trying to update (2003)

    It's late in the day (here in the UK) and I'm sure that my mind has gone numb, but I can't work out why the following query is telling me that the it has failed because 'ODBC--update on a libnked table 'dbo_stkmaster' failed'.

    SELECT tbl_3P_Stock.PackagingCode, dbo_stkmaster.full_desc, tbl_3P_Stock.SupplierID, tbl_3P_Stock.ContactID
    FROM tbl_3P_Stock LEFT JOIN dbo_stkmaster ON tbl_3P_Stock.PackagingCode = dbo_stkmaster.sku_code;

    The ODBC connection to this SQL Server table uses a user that has read only rights, so I can understand that an update to this table would fail. The thing is, I wasn't trying to update it; I was trying to update the Access table tbl_3P_Stock. I thought I was just using stkmaster as a lookup so that the users could see the stock description as they edit tbl_3P_Stock. sku_code is the primary key of stkmaster and I'm sure I've done this many times before.

    Please use gentle language if I've done something really dumb.

    Ian

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

    Re: Why is this query trying to update (2003)

    Queries based on more than one table are tricky - they often end up being not updatable. Is there a unique key on dbo_stkmaster.sku_code?

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

    Re: Why is this query trying to update (2003)

    sku_code is the primary key of the table dbo_stkmaster.

    Ian

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

    Re: Why is this query trying to update (2003)

    Could you use a form based on tbl_3P_Stock only, with a combo box for PackagingCode that has dbo_stkmaster as row source?

  5. #5
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Why is this query trying to update (2003)

    I thought of that, but wouldn 't the combo box display either the code or the description when it wasn't selected? The practice here is to see both. Is it possible to set up a combo box to show two fields when it's not dropped down?
    I wonder if this is the difference between queries where this autolookup (is that the correct term?) works, and the current problem query, where it doesn't. In queries where it works, the field that holds the lookup code (the packaging code in the curent query) is not the primary key of the table that I am trying to update. In the current query, PackagingCode is the primary key of tbl_3P_Stock. If that is the problem, is there a way round it?

    Thanks

    Ian

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

    Re: Why is this query trying to update (2003)

    A combo box will show only one of the two, but you could use two combo boxes, or two text boxes, one of which uses DLookup to get the description.

    But if PackagingCode is the PK of tbl_3P_Stock and sku_code is the PK of dbo_stkmaster, you have a one-to-one relationship. What is the purpose of that?

  7. #7
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Why is this query trying to update (2003)

    I've already gone down the two combo boxes bound to the same field route. Is there any way of surpressing the dropdown arrow in a combo box?
    stkmaster is part of a third party product that I cannot change. I am effectively writing an add-on for it, hence the one to one relationship between the tables.
    Regards
    Ian

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

    Re: Why is this query trying to update (2003)

    You can place a little rectangle on top of the dropdown arrow of a combo box.

    If you use DLookup, you don't need combo boxes.

Posting Permissions

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