I've got two tables tblMedium (with primary key mediumID) and tblGallery (with foreign key mediumID). The Administrator can delete a Medium record. However, this leaves tblGallery records with a mediumID that no longer exists. Any ideas how I can set a default mediumID in tblGallery for all records that have a missing reference?

in access go into design mode of your table, find your mediumID field (or column) and click in it's name...now on the lower section of the screen is a panel with options for this column...insert default 1 or whatever.

in sql server open up enterprise manager, choose server instance and expand it, then choose databases en expand it select your database, expand that in the other window and choose tables, now in the browser section of the manager pops up your table names, right click appropriate table and select design it...same process as access, click in mediumID and set default value.

this will only work for inserts though.
so you do not want to use defaults, well you do but you will also want to add a

update tblGallery
set mediumID = default value here
where mediumID = deleted value here

and that won't work because the delete will fail because related records exist, so you need to run the update BEFORE the delete action runs and then you can output a message:

"Related items have been migrated and the medium is deleted"

HTH

with regards,

Dennis van Galen
Webmaster KPN Telecom Holland
Financial & Information Services

Studio MX / CFMX PRO / SQL 2000 / NT4 AND win2kPRO / IIS5