Table structure boggling mind

[armychick86]

Hello all,
Using Access 2003, I am creating a db that tracks computers along with what switch and port they are connected to.
I have 5 different networks along with 31 switches.
I have a cascading combobox, in my form, that gives me a list of what switches correspond with the network chosen.
What I would like it to do is give me a list of ports that are open/available based on the switch (available ports ranging from 24 to 48 based on the switch).
Creating 31 tables is a little excessive and I really don't want to have to do that.

Tables I have so far:
tblComputerMaster -
tblNetwork
tblSwitch
along with a few others that don't pertain to the question.

I can dummy down my db if someone needs an example. If anybody has any ideas on how this would be possible I would really appreciate it.

 

[namliam]

Just make a junction table (additional table) for the ports??
Which lists per switch the ports available/used or somesuch?

 

[armychick86]

Thanks for your input mailman,
I have successfully made a cascading combobox for network> switch> available ports.

I created the table named tblPort (I was an idiot for not thinking of that before), which has the fields:
PortID (P)
SwitchID
Port
Active - yes/no

Now all I need it to do is change the value of "Active" to "No" after I select in the combobox. Does anybody have any sample code that I could look at? If so I would greatly appreciate your help.

I love Access

 

[boblarson]

Depending on how much time it will take you to create all of this, you may be better off purchasing an already existing product. The company I work for has exactly what would work for you with this.

It is called TRAX (Relationships Traceability Modeler). Just an FYI for you to determine what is more cost-effective.
http://www.advisicon.com/application_development.htm

 

[armychick86]

Well thank you Bob, but no I would prefer to do this myself (with the help of those willing).

 

[boblarson]

Well thank you Bob, but no I would prefer to do this myself (with the help of those willing).

I understand why you would want to...

There's one thing that most people forget about though. Let's say you are making $20 an hour (that may be high or low but just as an example).

If you work on this for 40 hours - The item just cost your business at LEAST $800 (more if they have to pay taxes and all above the amount you get).

If you work on it two weeks (80 hours), it just rose to $1600, etc.

That's just a little tidbit that tends to escape folks at times.

 

[armychick86]

Let me put it to you this way... my username isn't armychick86 for no reason. I am currently deployed with the Army in Iraq. No matter what I do with the time I am here, I will still get paid the exact same. Doing this not only occupies the year I have left to spend over here but it also makes my work more profient. So the answer is still no, I prefer to do this myself.

But while your replying... do you have any suggestions for me?

 

[boblarson]

In the after update of the combo box, you would use something like:

If Me.ComboBoxNameHere = "WhateverYouWantHere" Then
   Me.Active = "No"
Else
   Me.Active = "Yes"
End If

 

[armychick86]

Thanks for all your help... but its still not working like I want it to... it keeps coming back with an error: "You can't assign a value to this object".
Now... the checkbox is not part of the form that i'm working with, its part of tblPort. It allows me to filter what ports are active and which aren't.
I know i'm missing something but I don't know enough VBA to fix it. I've looked through the books I have and other posts but still can't figure it out.

 

[boblarson]

What code exactly are you trying? Can you post it?

 

[armychick86]

Here is the code that I have:

Private Sub cboPort_AfterUpdate()
If Me.cboPort >= 1 Then
Me.chkActive = 1
Else
Me.chkActive = 0
End If
End Sub

I placed a checkbox on the form and changed the visibility to "no" just to see what it would do.. thats where the "chkActive" comes in...

 

[boblarson]

Try this:

If Me.cboPort >= 1 Then
   Me.chkActive = True
Else
   Me.chkActive = False
End If

 

[armychick86]

I tryed that, it still didn't work.

 

[boblarson]

Are you sure that the combo is returning a numeric value like you think. Try adding either a message box

MsgBox Me.cboPort

or

Debug.Print Me.cboPort

to the after update event and look at the message box or the Immediate window, depending on which you chose.

 

[pbaldy]

I am currently deployed with the Army in Iraq.

My daughter and son-in-law are both in the Air Force. Daughter recently returned from deployment in Afghanistan, son-in-law is there now. You have my gratitude for your service and prayers for a safe return.

 

[jdraw]

Hello all,
Using Access 2003, I am creating a db that tracks computers along with what switch and port they are connected to.
I have 5 different networks along with 31 switches.
I have a cascading combobox, in my form, that gives me a list of what switches correspond with the network chosen.
What I would like it to do is give me a list of ports that are open/available based on the switch (available ports ranging from 24 to 48 based on the switch).
Creating 31 tables is a little excessive and I really don't want to have to do that.

Tables I have so far:
tblComputerMaster -
tblNetwork
tblSwitch
along with a few others that don't pertain to the question.

I can dummy down my db if someone needs an example. If anybody has any ideas on how this would be possible I would really appreciate it.

Can you show your tables and structure to help with context?
Seems a Computer is attached to a Port which is part of a Switch belonging to a Network.

Minimum tables to me at first glance are
Computer
Port
Switch
Network, and as others have said there are Junction tables. Some of these may be replaced once a better understanding on needs develops.


In the longer run, I think identification will be along this:

ComputerId
Portid
SwitchId
NetworkId
Active Yes/No

 

[armychick86]

Ok, my tables are as follows:

tblComputerMaster
ComputerName(P)
Network
Switch
Port

tblNetwork
NetID(P)
Network
Description

tblSwitch
SwitchID(P)
NetID
Switch

tblPort
PortID(P)
SwitchID
Port
Active (Yes/No)