List all assigned Static IP's

[scouser]

Hi guys, back on the development trail!!

I have a database, the 3 tables of current interest are:
tblStaticIP (IP)
tblComputerIPAllocation (ID - IP - ComputerID)
tblHardwareIPAllocation (ID - IP - HardwareID)

Realtionship via IP (no RI enforced).

I would like to be able to list all allocated IP's in a single query, even better prevent allocation of IP's allocated elsewhere?

Can anyone advise?
Thanks,
Phil.

 

[scouser]

Update

Just had a play and a Union query appears to list all IP's that have been allocated.

So can I then go a step further and prevent allocation of an IP already allocated?

Hope I am making sense!
Thanks,
Phil.

 

[WayneRyan]

Phil,

As you found out, the Union query will get you the superset of all
IP numbers.

To check for dups, I'd just use:

If DCount("[IP]", "tblComputerIPAllocation", "[IP] = '" & Me.IP & "'") > 0 Or _
   DCount("[IP]", "tblHardwareIPAllocation", "[IP] = '" & Me.IP & "'") > 0 Then
   MsgBox("IP Number Already In Use.")
End If

You could also apply the DCount to your Union query ...

hth,
Wayne

 

[scouser]

Thanks

Wayne thanks for the reply. I will give that a whirl, shall I stick the code on 'After Update'?
Many Thanks,
Phil.

 

[scouser]

Thanks

Thanks that works a treat.

When I have ironed out one last issue I will post to the sample DB area (Audit Database), labour of love............
Thanks,
Phil.

 

[neileg]

Doesn't this highlight the fact that you shouldn't have three tables, just one?

 

[scouser]

Tables

Hi Neil. The problem I have is I have a list of static IP's. I have tables for Suppliers & SupplierProducts. I then have tables for Computers & Hardware. IP's can be assigned to either computerID or hardwareID that's why I created the allocation tables?

Hope that makes sense!!
Phil.

 

[neileg]

Rereading your posts, understand a little better. Your computer and hardware tables should be combined into one. You could impose an index on the field that holds the IP ID and set this to no duplicates to prevent allocating the IP twice.

 

[scouser]

Combine Tables

Hi Neil. The database has evolved over a period of time (I am no expert but have made adjustments as my knowledge has improved). I have separate tables for Computers & Hardware as hardware is also classified as switches / printers etc......I have tables for RAM / Operating Systems blah...blah...different fields are required (that was my logic).

I do however have a major challenge on how the database handles software / software licences when Open Licence is involved. Here I do have too many tables as like I said, the database has evolved!!

I posted a very old version of the database in the sample database forum a while back (I got some positive feedback via PM regards it's usefulness).

I fully intend to post this version when I iron out the license issue (as I see it). I really feel it will be of use to others.

If you fancy a challenge (or anyone who can code!!) let me know and I will post a sample databse and explain how I would like it to work (others will benefit.)
Thanks,
Phil.