"free" ip number when I remove device from database, not delete?

[Sandroid]

Win 7, access 2007 , tables: device and ip, relation (device.ipIP,
foreign)one-to-one(ip.ID, primary)

when I delete an device from device table using form it is also deleted
from IP table. This should work such as the IP address remains to be
used in other device . How to resolve this? If I change the relation
there is no more validatation that one IP address is used once.

I want to "free" ip number when I remove device from database so IP number is
not deleted and can be used later. How to accomplish this?
In relation the cascading is turned off - no help
referential integrity turned off - no help
relation deleted, it still removes row from IP table when I remove one device
using the form.

In the form the dropdown control looks for "free" addresses as follows:
SELECT tblIP.ID, [ip] & "." & [ip2] & "." & [ip3] & "." & [ip4] AS
[IP-
number], tblDevice.ipID
FROM tblIP LEFT JOIN tblDevice ON tblIP.ID = tblDevice.ipID
ORDER BY tblIP.ip, tblIP.ip2, tblIP.ip3, tblIP.ip4
WHERE (((tblDevice.ipID) Is Null));

I don't want to delete IP address from tblIP, tried to make lookup
table from IP table without relation, no help? how to make make sure that
each ip address is used only once then?

tblIP structure: ID(primary),ip, ip2, ip3, ip4.


I deleted the relation and drag from IP table (ID, primary key)
to device table (ipID, foreign key) but it still removes the related IP
row when I remove row from device table. When I try to drag from device
table it gives me error (not originally in english) like "field name
in index or relation definition "id" is not valid" What to do?

Help me, Please!

 

[BruceM via AccessMonster.com]

There is an active thread on this same topic in the Forms group.

Win 7, access 2007 , tables: device and ip, relation (device.ipIP,
foreign)one-to-one(ip.ID, primary)

when I delete an device from device table using form it is also deleted
from IP table. This should work such as the IP address remains to be
used in other device . How to resolve this? If I change the relation
there is no more validatation that one IP address is used once.

I want to "free" ip number when I remove device from database so IP number is
not deleted and can be used later. How to accomplish this?
In relation the cascading is turned off - no help
referential integrity turned off - no help
relation deleted, it still removes row from IP table when I remove one device
using the form.

In the form the dropdown control looks for "free" addresses as follows:
SELECT tblIP.ID, [ip] & "." & [ip2] & "." & [ip3] & "." & [ip4] AS
[IP-
number], tblDevice.ipID
FROM tblIP LEFT JOIN tblDevice ON tblIP.ID = tblDevice.ipID
ORDER BY tblIP.ip, tblIP.ip2, tblIP.ip3, tblIP.ip4
WHERE (((tblDevice.ipID) Is Null));

I don't want to delete IP address from tblIP, tried to make lookup
table from IP table without relation, no help? how to make make sure that
each ip address is used only once then?

tblIP structure: ID(primary),ip, ip2, ip3, ip4.

I deleted the relation and drag from IP table (ID, primary key)
to device table (ipID, foreign key) but it still removes the related IP
row when I remove row from device table. When I try to drag from device
table it gives me error (not originally in english) like "field name
in index or relation definition "id" is not valid" What to do?

Help me, Please!