Clear Value of a field after delete records

[herverob]

Hi all,
I have the following problem of update/refresh

in my DB, I have 3 Tables, 2 Queries, 2 Forms and 1 SubForm.

1 Field in "tbl_Equipment" is a lookup in "tbl_Sub",
1 Field in "tbl_Equipment" is a lookup in "tbl_Bus".

If I delete a record in "tbl_Sub" or "tbl_Bus", I think in the "tbl_Equipment", the lookup field should become blanks or something like #NA as the record is deleted! can someone confirm?

actually, this doesn't happen in my DB, can someone help me on this matter?

Thanks in advance
Herve

 

[herverob]

I forget to attached the DB...

 

[herverob]

I am thinking of using the "Replace" function.
Basically, replacing all values that is equal to the deleted record by "blank" value
Is it the only way and fist, will it work?

thanks