search/find/select record before delete/update

[wtolentino]

i have a lookup form that i called room list and on this form i want to prevent/stop from deleting/updating when the same record is being used from the other tables. let say for example

select count(*) into vCnt
  from tblBuilding tb
where tb.room_code = [forms].[room_code]

if vCnt > 0 then
  stop
end if;

how do i programmatically code it on ms access?

thanks.

[ranman256]

Not sure what you're asking.
you can prevent records from being deleted from child tables using the relationship rules. No code needed.
Do not set cascade delete. Then users cannot delete parent records if children exist.

[wtolentino]

thanks. because it is only a lookup table i do not want to create relationship rules. i only want to prevent the lookup table from deleting/updating the data if the same data is being used in another tables.

[ssanfu]

You would need to execute the code for every table that the data could be saved in.

Maybe

select count(*) into vCnt
  from tblBuilding tb
where tb.room_code = [forms].[room_code]

if vCnt > 0 then
  Exit sub
end if

select count(*) into vCnt
  from tblRentals tb
where tb.room_code = [forms].[room_code]

if vCnt > 0 then
  Exit sub
end if
.
.
.

(just one way to write the code)