Best way suggestions requested

[thart21]

Would like some opinions on the best way to go about creating this form.

I have 4 tables

tblCables
cableid
partnumber
supplierid
other detail fields

SideA
cableid
conn1A through conn15A

Side B
cableid
conn1B through conn15B (same connector choices as Side A)

tblSupplier
supplierid
supplier


My form has a partnumber combo box (which I have filled from tblCables)that, when a p/n is selected, all of the fields pertaining to that p/n are automatically filled in. This includes all 15 "conn" fields from both SideA and Side B and up to six suppliers.

This form will be used to view as well as create new and update records so I have made all of my fields combo boxes and used the underlying tables to fill the data.

Right now I have my form based on a query using all 4 tables left joined to cableid in tblCables.

I will then use VBA to auto fill all of the fields. Since I can't get this to work, my questions are:

1) should I be filling the part number cbo with a SELECT statement from my query or from tblCables?

2) Are my joins correct? (left join on cableid in tblCables)

3) Should I even be using the same form for view and updating/adding?

4) In the After Update of the partnumber cbo, I was trying to use
i.e. Me.conn1A = Me.partnumber.Column(11) The number (starting with 0) of the conn1A field in my query.
Is this the best way to to this? I will need to do this 36 times to fill all of my cbo's.


Not looking for someone to do all of this for me, just wanted to see if I was heading in the right direction or if I should step back and reevaluate my plan.

Thanks,

Toni

 

[Pat Hartman]

Your tables are not normalized. They contain repeating groups. You don't need separate tables for each side either. You should have a single table:

tblConnection
CableID (primary key fld1, foreign key to tblCables)
ConnSide (primary key fld2)
ConnNum (primary key fld3)
other fields?

 

[thart21]

Thanks Pat, but could you explain a little further for me. The way I am following your idea is, if I use the cableid of the new table you suggested, as the foreign id in my tblCables, I could potentially have 30 records for the same part number in tblCables. I know that is not what you intended, so I must be misunderstanding your response or I did not explain very well what I am trying to do.

I have a main table, tblCables with the fields I earlier described and I need to show Side A and Side B for each with up to 15 connectors on each side that are used in each cable. So, one record could have 6 connectors for Side A and 6 for Side B, another could have 15 for Side A and 10 for Side B, etc.

Thanks!

 

[Pat Hartman]

tblConnection is the table that relates one side to the other. It replaces your SideA and SideB tables. The relationship is many-to-many that is why it is implemented as a separate table. The value of ConnSide is either Left or Right and the Value of ConnNum is 1 through 15. That makes potentially 15 rows in the connection table for each side rather than 1 row with 15 columns in each of two tables.

Also Suppliers should be a subform. That is as you have described it a 1-many relationship with 1 cable having many suppliers. Limiting the number of suppliers to 6 is arbitrary and unnecessary with properly normalized tables.

 

[tone21]

Thanks Pat, got it working-much cleaner this way.