After upsizing a query which updated 2 tables now fails

[EwanB]

I have upsized from Access 2007 to 2008 server and have linked tables in my
fe. It works fine expect for a query which used to insert a new row into 2
different tables, which have a 1 to 1 relationship, in one action.

I use an intermediate table to link serial numbers and a particular product,
which was then referenced in the desptach process. This I called the supply
table.

In access I used a subform, linked inside a form showing one of the product
levels. This subform would allow the user to add a new serial number row and
a new supply table row. This now fails, as the sql server does not seem to
handle moving the serial number table primary key, into the supply table.

How do I get round this, should I use triggers in the sql server, or VBA
code on the insert event, or something else?

If I use the local tables the query works, but of course the data is only
local. Any ideas anyone?