JOIN and concatenate between tables and UPDATE
Does this work?

UPDATE KeyInventory 
SET KeyInventory.Productcode = concat(SFXVencode, partnumber)
FROM KeyInventory 
   INNER JOIN Keybrands ON keybrands.vencode = keyinventory.vencode


Might need a little tweaking once you post your DDL...