In a CAN and can't find my way out

[easyrider]

I am working on a school project via an online class and ran into head
scratching problem trying to figure out how to tell MS access what I
want it to do.

Real world scenario:
A building has a main phone panel called an MDF. A MDF has many boards
called "tag blocks" and each "tab block" has several line
pairs. To break out the lines on each floor (very simple version) each
floor has a "CAN". Each CAN has one or more "tag blocks" and
each "tab block" has several line pairs.

I have created the following tables: (all fields are of type number)
MDF_TB: with the following fields
MDF_ID= number representing a specific MDF_tag_block and MDF_pair
combination
MDF_tag_block= represents a specific board
MDF_pair= represents which set of wire on a specific board

CAN_TB: the following fields
--------------------------------------
CAN_ID=unique number representing a specific CAN_number, CAN_tag_block
and CAN_pair combination

CAN_number=represents a specific floor in the building

CAN_tag_block= represents specific board

CAN_pair= represents which set of wire on a specific board.

MDF_ID= Fkey to MDF_TB.MDF_ID
To_FROM_connection_TB
To_connection = Fkey to CAN_ID, lookup combo box
From_connection=Fkey to CAN_ID, lookup combo box
PROBLEM:
----------------
How do I represent the following circuit in a form?.

MDF_ID=1
MDF_tag_block=1
MDF_pair=2

Connects to

CAN_num=1, tag_block=3, pair 2

Connected to
CAN_num=2, tag_block=1, pair 3

Connected to
CAN_num=3, tag_block=4, pair 1

Then CAN_num=3, tag_block3, pair1 goes to desk #3 on the second floor.
I have been able to make the relationship between the MDF tag_block,
pair and the first CAN tag_block, pair in the circuit via the lookup
combo box and I can establish the from_connection in a DATASHEET view.
I am able to set the next link in the chain, but when I live that MDF
record set (record #1) and return to that record, the datasheet appears
to have dropped all defined connection except for the first connection.

Is this even doable in MS Access?

[David Seeto via AccessMonster.com]

You've posted a lot of code, which is helpful, but it's hard trying to
follow what you're doing - I don't think you've got it quite right. I think
you'd be better off creating tables for the individual items:

Table MDF_TB
Column MDF_ID = ID for a specific MDF

Table CAN_TB
Column CAN_ID = ID for a specific CAN

Table TAG_TB
Column TAG_ID = ID for a specific tag block

Table LINE_TB
Column LINE_ID = ID for a specific line

You then want to set up a bunch of tables representing the relatinoships
between all of the above. I

Table MDF_TAG_LINK
Column MDF_ID
Column TAG_ID

Table CAN_TAG_LINK
Column CAN_ID
Column TAG_ID

Table CAN_LINE_LINK
Column CAN_ID
Column LINE_ID

Table TAG_LINE_LINK
Column TAG_ID
Column LINE_ID

What's confusing me is that it seems that lines connect to tag (tab?)
blocks which can connect to both CANs and to the MDF? Is that right?

--
Message posted via http://www.accessmonster.com