Form to Show Kit Options

[Prysmian001]

Hi again,
Been awhile. I've been working on setting up tables and forms per the recommended best pratices but ran into an issue with Relationships. I have my table "tblConductorSize" and it has a "ConductorSizeID" and "ConductorSize" field. The "ConductorSize" field is what I'm really interested in. This outputs the size in terms we normally deal with (2/0AWG, 3/0AWG, 4/0AWG, 250kcm, etc.). These sizes are not limited to a cable's conductor though. Items like the Splice Bodies drain wire or shielding mesh are all sized using the same values. I can make a One-To-Many relationship easy enough from the "ConductorSizeID" field to the "DrainWire" field in table "tblSpliceBodies", but when I do the same for the "ShieldingMesh" field a "tblConductorSize_1" is created. I don't see this new table in my navigation pane, but it's visible in the Relationships screen. Is this the correct way to handle making a relationship with a field that can be linked to multiple tables or fields in a single table?


Bryan

[ArviLaanemets]

This is correct way! In case relationships for same record of tblSpliceBodies refer to different records of tblConductorSize, you must have a separate instances of tblConductorSize for every such relationship.

And when you create a query based on those tables, you have to link tblConductorSize several times using aliases. Like

SELECT sp.UnitedStatesSAPCode, ..., wire.ConductorSize AS WireSize, mesh.ConductorSize AS MeshSize, ...
FROM tblSpliceBodies sp
LEFT JOIN tblConductorSize wire ON wire.ConductorSizeID=sp.DrainWire
LEFT JOIN tblConductorSize mesh ON mesh.ConductorSizeID=sp.ShieldingMesh

[Prysmian001]

Ok. It's been awhile since I was here and I think I've made some decent progress. That said I hit a wall and I'm about to loose my mind. I decided to work on the file one product at a time. I started with Splice Kits and moved to Terminations where I'm currently stuck. I have the Splice Report working but when I followed a similar logic for the Terminations I'm not getting any results and I can't for the life of me figure out why. I tried to breakdown the process and provided the file. If anyone has the time to tinker with this and figure out where I went wrong it will be a great help.

How to use the file:
Users are to start on Form "formCableTypeSelection". Right now I only have 4 buttons enabled that work for the splice report. The same should apply for the terminations but no results are appearing. For now click the "1C Copper Tape Shielded Cable Kits" button and this will do several things. It creates TempVars "cableTypeSelected" and "cableShieldPrefix". These are needed to help populate the next form and filter later. Then the button closes the form "formCableTypeSelection" and opens "formBasicCableDataEntry".

The form "formBasicCableDataEntry" is the engine where the user has to enter cable data from a spec sheet. For testing purposes I've been using the following data. After the data is entered click "Proceed" and the program should apply any remaining filters and open the reports.

• Cable Type: This field is autofilled - no user entry needed.
  
• Conductor Material: Copper
  
• Conductor Type: Compact
  
• Conductor Size: 750 kcm
  
• Voltage (kV): 15
  
• Min Insulation Diameter: 1.36
  
• Max Insulation Diameter: 1.46
• Metallic Shield Code: CTS-2.5 MIL
  
• Nominal Jacket Only: True (Check the box)
  
• Nominal Jacket Diameter: 1.65

For reference the other TempVars created on "formBasicCableDataEntry" are:

• conductorMaterial
  
• conductorType
  
• conductorSize
  
• cableVoltage
  
• nominalCableInsulationDiameter
  
• minCableInsulationDiameter
  
• maxCableInsulationDiameter
  
• cableShieldSelected
  
• nominalCableJacketDiameter
  
• minCableJacketDiameter
  
• maxCableJacketDiameter
  
• maxOverallCableDiameter

When the "Proceed" button is clicked the first thing it does is check to make sure all data has been entered. If not a message appears and the user is directed to enter the data prior to proceeding. This is working fine. In the Else statment after checking the data entry I have the IF statements to determine the cable specific filters regarding the conductorType and cableShieldPrefix. Most of the filters I need are hard coded into the queries that the reports are generated from (for example: qry1CBasicSpliceKits - basic1CSpliceKitMaxVoltage has a criteria in the query stating ">=[TempVars]![cableVoltage]). The filter for "conductorType" is needed to so I can target the correct column to filter by so only connectors that can be used on cable are shown (example: If we are working with a 750 kcm Compact conductor then a 2-250 ALSB shear bolt connector should not be listed as it's too small. Also note that compression connectors may not have the same range depending on the conductorType). For the Splice Report the "conductorType" filter is all thats needed. If you enter the data as detailed you will see the same as what I'm getting in the screenshots.

Splice Results:


For the termination results, another filters is needed based on the "cableShieldPrefix" for data provided this will be "CTS". This is because there are some kits that cannot be used on a Copper Tape Shielded (CTS) cable so for this example only entries with a check under "CTS" should be populated. Currently I'm getting no results though. The other thing I need to be warry of are duplicate connectors showing up. It's ok if a Catalog Number is duplicated but each entry under a kit should have a unique "Connector SAP Code".

Termination Results:


Latest Database Revision:
MV Accessory Database 2.7.zip

[Prysmian001]

Ok so I solved the mystery for no entries. Looks like I don't have a Shear Bolt option and due to missing dimensions for Compression connectors there were no results to display. Long story short I changed to using the following cable data. This is for a 250 KCM cable which will display results. That said I'm getting too many results. Showing shearbolts that do not support the conductorSize range. For instance the connectors like 750-1000ALSB2 and 1000-1250-ALSB2 should not be listed.

Ok. It's been awhile since I was here and I think I've made some decent progress. That said I hit a wall and I'm about to loose my mind. I decided to work on the file one product at a time. I started with Splice Kits and moved to Terminations where I'm currently stuck. I have the Splice Report working but when I followed a similar logic for the Terminations I'm not getting any results and I can't for the life of me figure out why. I tried to breakdown the process and provided the file. If anyone has the time to tinker with this and figure out where I went wrong it will be a great help.

How to use the file:
Users are to start on Form "formCableTypeSelection". Right now I only have 4 buttons enabled that work for the splice report. The same should apply for the terminations but no results are appearing. For now click the "1C Copper Tape Shielded Cable Kits" button and this will do several things. It creates TempVars "cableTypeSelected" and "cableShieldPrefix". These are needed to help populate the next form and filter later. Then the button closes the form "formCableTypeSelection" and opens "formBasicCableDataEntry".

The form "formBasicCableDataEntry" is the engine where the user has to enter cable data from a spec sheet. For testing purposes I've been using the following data. After the data is entered click "Proceed" and the program should apply any remaining filters and open the reports.

• Cable Type: This field is autofilled - no user entry needed.
  
• Conductor Material: Copper
  
• Conductor Type: Compact
  
• Conductor Size: 750 kcm
  
• Voltage (kV): 15
  
• Min Insulation Diameter: 1.36
  
• Max Insulation Diameter: 1.46
• Metallic Shield Code: CTS-2.5 MIL
  
• Nominal Jacket Only: True (Check the box)
  
• Nominal Jacket Diameter: 1.65

For reference the other TempVars created on "formBasicCableDataEntry" are:

• conductorMaterial
  
• conductorType
  
• conductorSize
  
• cableVoltage
  
• nominalCableInsulationDiameter
  
• minCableInsulationDiameter
  
• maxCableInsulationDiameter
  
• cableShieldSelected
  
• nominalCableJacketDiameter
  
• minCableJacketDiameter
  
• maxCableJacketDiameter
  
• maxOverallCableDiameter

When the "Proceed" button is clicked the first thing it does is check to make sure all data has been entered. If not a message appears and the user is directed to enter the data prior to proceeding. This is working fine. In the Else statment after checking the data entry I have the IF statements to determine the cable specific filters regarding the conductorType and cableShieldPrefix. Most of the filters I need are hard coded into the queries that the reports are generated from (for example: qry1CBasicSpliceKits - basic1CSpliceKitMaxVoltage has a criteria in the query stating ">=[TempVars]![cableVoltage]). The filter for "conductorType" is needed to so I can target the correct column to filter by so only connectors that can be used on cable are shown (example: If we are working with a 750 kcm Compact conductor then a 2-250 ALSB shear bolt connector should not be listed as it's too small. Also note that compression connectors may not have the same range depending on the conductorType). For the Splice Report the "conductorType" filter is all thats needed. If you enter the data as detailed you will see the same as what I'm getting in the screenshots.

Splice Results:


For the termination results, another filters is needed based on the "cableShieldPrefix" for data provided this will be "CTS". This is because there are some kits that cannot be used on a Copper Tape Shielded (CTS) cable so for this example only entries with a check under "CTS" should be populated. Currently I'm getting no results though. The other thing I need to be warry of are duplicate connectors showing up. It's ok if a Catalog Number is duplicated but each entry under a kit should have a unique "Connector SAP Code".

Termination Results:


Latest Database Revision:
MV Accessory Database 2.7.zip