[Access 2k3 + VBA] SQL UPDATE add pk_id instead of customers name

[iboumiza]

Hi,

I have a product table with a bound combobox to customer name table;, and when I try update:

UPDATE product
SET customer_name_clone=customer_name;

the update function add customer PK_ID instead of customers name!!!

How can I fix this, please?

Regards

 

[jzwp22]

First, I would get rid of the combo box at the table level. Although Access has the ability to have Lookup fields (combo and list boxes) at the table level, they are generally not recommended because of the problems they can cause as detailed in this site.

Out of curiosity why would have the customer information in the product table? The product table should have only information about the product. If you want to associate the products that a customer buys, you would need a table like the following:


tblCustomerProducts
-pkCustProdID primary key, autonumber
-fkCustomerID foreign key to customer table
-fkProductID foreign key to product table.

 

[gemma-the-husky]

BUT = the behaviour you quote is what you DO want for a lookup.

a table should carry the ID of an associated table as the FK.