Show only free IP addresses when selecting address to certain device?

[Sandroid]

There is one-to-one relationship between IP table and Device table.

IP table has ID (primary key) and four ip fields.
Device table has ipID field (foreign key) and other fields which are
not relevant to this matter.

The probles is that in the device form the ip dropdown now only shows
the reserved IPs, not the free IPs from IP table.

The query in dropdown is as follows:

SELECT tblIP.ID, [ip] & "." & [ip2] & "." & [ip3] & "." & [ip4] AS [IP-
number], tblDevice.ipID
FROM tblIP INNER JOIN tblDevice ON tblIP.ID = tblDevice.ipID
ORDER BY tblIP.ip, tblIP.ip2, tblIP.ip3, tblIP.ip4;

the result set shows only IP numbers that has assigned to device.

if I add condition
WHERE (((tblDevice.ipID) Is Null)), so I want a "negation" (show all
records in IP table that has not assigned to device (free), the result
set is empty)

Now what?

btw. Is it right to have IPs in four separate fields? Is there a
better way?

Sandroid

 

[Douglas J. Steele]

Try changing

FROM tblIP INNER JOIN tblDevice ON tblIP.ID = tblDevice.ipID

to

FROM tblIP LEFT JOIN tblDevice ON tblIP.ID = tblDevice.ipID

(with the WHERE tblDevice.ipID Is Null condition)

Assuming you want to be able to sort the addresses, having them in four
separate fields is appropriate. If you don't care about sorting, it may not
be necessary.

 

[Sandroid]

Thanks Doug that worked!

Now what?

Now there is problem in frmDevice. There is IP combo box (cboIP) on
the form. When I update it (it's lookup and it shows only ip numbers
that are not reserved) it saves id field value from tblIP to the ipID
field in tblDevice as it should, but when I move to the next record in
the form the previously selected ip is still in the combo box list. I
tried VBA code (after update event) and I coded cboIP.requery, but it
is still there.

I found one solution though that is when I select the IP from combo
box I press the "update" (not update all) button( which has green
arrows) from the ribbon. Then it works as it shoud, but it is not user
friendly way to do it.

If I press update all or use macro like Requery the form jumps to the
first record so that won't work either. But what I press the button in
the ribbon the record stays the same as it should.

Is there same command that the update button does in the code or
macro?

Help me, please!

Try changing

FROM tblIP INNER JOIN tblDevice ON tblIP.ID = tblDevice.ipID

to

FROM tblIP LEFT JOIN tblDevice ON tblIP.ID = tblDevice.ipID

(with the WHERE tblDevice.ipID Is Null condition)

Assuming you want to be able to sort the addresses, having them in four
separate fields is appropriate. If you don't care about sorting, it may not
be necessary.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

There is one-to-one relationship between IP table and Device table.

IP table has ID (primary key) and four ip fields.
Device table has ipID field (foreign key) and other fields which are
not relevant to this matter.

The probles is that in the device form the ip dropdown now only shows
the reserved IPs, not the free IPs from IP table.

The query in dropdown is as follows:

SELECT tblIP.ID, [ip] & "." & [ip2] & "." & [ip3] & "." & [ip4] AS [IP-
number], tblDevice.ipID
FROM tblIP INNER JOIN tblDevice ON tblIP.ID = tblDevice.ipID
ORDER BY tblIP.ip, tblIP.ip2, tblIP.ip3, tblIP.ip4;

the result set shows only IP numbers that has assigned to device.

if I add condition
WHERE (((tblDevice.ipID) Is Null)), so I want a "negation" (show all
records in IP table that has not assigned to device (free), the result
set is empty)

Now what?

btw. Is it right to have IPs in four separate fields? Is there a
better way?

Sandroid

 

[Douglas J. Steele]

You say you put VBA code in the after update event. Which after update
event?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks Doug that worked!

Now what?

Now there is problem in frmDevice. There is IP combo box (cboIP) on
the form. When I update it (it's lookup and it shows only ip numbers
that are not reserved) it saves id field value from tblIP to the ipID
field in tblDevice as it should, but when I move to the next record in
the form the previously selected ip is still in the combo box list. I
tried VBA code (after update event) and I coded cboIP.requery, but it
is still there.

I found one solution though that is when I select the IP from combo
box I press the "update" (not update all) button( which has green
arrows) from the ribbon. Then it works as it shoud, but it is not user
friendly way to do it.

If I press update all or use macro like Requery the form jumps to the
first record so that won't work either. But what I press the button in
the ribbon the record stays the same as it should.

Is there same command that the update button does in the code or
macro?

Help me, please!

Try changing

FROM tblIP INNER JOIN tblDevice ON tblIP.ID = tblDevice.ipID

to

FROM tblIP LEFT JOIN tblDevice ON tblIP.ID = tblDevice.ipID

(with the WHERE tblDevice.ipID Is Null condition)

Assuming you want to be able to sort the addresses, having them in four
separate fields is appropriate. If you don't care about sorting, it may
not
be necessary.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

There is one-to-one relationship between IP table and Device table.

IP table has ID (primary key) and four ip fields.
Device table has ipID field (foreign key) and other fields which are
not relevant to this matter.

The probles is that in the device form the ip dropdown now only shows
the reserved IPs, not the free IPs from IP table.

The query in dropdown is as follows:

SELECT tblIP.ID, [ip] & "." & [ip2] & "." & [ip3] & "." & [ip4] AS [IP-
number], tblDevice.ipID
FROM tblIP INNER JOIN tblDevice ON tblIP.ID = tblDevice.ipID
ORDER BY tblIP.ip, tblIP.ip2, tblIP.ip3, tblIP.ip4;

the result set shows only IP numbers that has assigned to device.

if I add condition
WHERE (((tblDevice.ipID) Is Null)), so I want a "negation" (show all
records in IP table that has not assigned to device (free), the result
set is empty)

Now what?

btw. Is it right to have IPs in four separate fields? Is there a
better way?

Sandroid

 

[Risse]

Thanks Doug that worked!

Now what?

Now there is problem in frmDevice. There is IP combo box (cboIP) on
the form. When I update it (it's lookup and it shows only ip numbers
that are not reserved) it saves id field value from tblIP to the ipID
field in tblDevice as it should, but when I move to the next record in
the form the previously selected ip is still in the combo box list. I
tried VBA code (after update event) and I coded cboIP.requery, but it
is still there.

I found one solution though that is when I select the IP from combo
box I press the "update" (not update all) button( which has green
arrows) from the ribbon. Then it works as it shoud, but it is not user
friendly way to do it.

If I press update all or use macro like Requery the form jumps to the
first record so that won't work either. But what I press the button in
the ribbon the record stays the same as it should.

Is there same command that the update button does in the code or
macro?

Help me, please!

Try changing

FROM tblIP INNER JOIN tblDevice ON tblIP.ID = tblDevice.ipID

to

FROM tblIP LEFT JOIN tblDevice ON tblIP.ID = tblDevice.ipID

(with the WHERE tblDevice.ipID Is Null condition)

Assuming you want to be able to sort the addresses, having them in four
separate fields is appropriate. If you don't care about sorting, it may
not
be necessary.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

There is one-to-one relationship between IP table and Device table.

IP table has ID (primary key) and four ip fields.
Device table has ipID field (foreign key) and other fields which are
not relevant to this matter.

The probles is that in the device form the ip dropdown now only shows
the reserved IPs, not the free IPs from IP table.

The query in dropdown is as follows:

SELECT tblIP.ID, [ip] & "." & [ip2] & "." & [ip3] & "." & [ip4] AS [IP-
number], tblDevice.ipID
FROM tblIP INNER JOIN tblDevice ON tblIP.ID = tblDevice.ipID
ORDER BY tblIP.ip, tblIP.ip2, tblIP.ip3, tblIP.ip4;

the result set shows only IP numbers that has assigned to device.

if I add condition
WHERE (((tblDevice.ipID) Is Null)), so I want a "negation" (show all
records in IP table that has not assigned to device (free), the result
set is empty)

Now what?

btw. Is it right to have IPs in four separate fields? Is there a
better way?

Sandroid