Data entry with cascading boxes on a continuous form

[nikademous]

Hello, I have been trying to figure out how to set up a form that you enter a component and component Value for the chosen product. Now right now there are 11 components and each components value there could be anywhere from 2-10 you could select from. Example below for FeedWheel you have a selection of 125, 90, 84 and 146 but if you look in bl_ProductComponents Junction Table I selected the 90 AKA 2. What I really wanted was when you open the form it displayed the "Right Now" 11 ComponentName in a text box, reason being in order to not have duplicates from a drop down. Then for each row select what ComponentVar you wanted for that ComponentName/Product. Thoughts, Examples?

tbl_Product
(pk)ProductID   Product    IsInactive
-----------------------------------------
     1          Product1       No
     2          Product2       Yes
     3          Product3       No

tbl_ProductComponents Junction Table
(pk)ProductComponentsID   ProductID    ComponentVarID
-----------------------------------------------------------
            1                 1                 2
            2                 1                 7
            3                 1                 8
            4                 3                 6

tbl_ComponentSizes
(pk)ComponentVarID   ComponentName    ComponentVar
------------------------------------------------------------
           1            Feed Wheel          125
           2            Feed Wheel          90
           3            Feed Wheel          84
           4            Feed Wheel          146
           5            Cut-Off             84
           6            Cut-Off             90
           7            Cut-Off             125
           8            P Plate             2-1/2
           9            P Plate             3
           10           P Plate             2-1/16
           11           P Plate             3-1/2

 

[nikademous]

Ok, by looking at the way my tables are set up is that correct OR should I have a table for each Component Name and its Component Values? If I did that there would be 11 tables for components.

 

[Andrzejek]

should I have a table for each Component Name " in my opinion - No.

I would have:[pre]
tbl_Components
ID Name
1 Feed Wheel
2 Cut-Off
3 P Plate
...[/pre]

so your other table would look like this:[pre]
tbl_ComponentSizes
(pk)ComponentVarID ComponentName(fk) ComponentVar
------------------------------------------------------------
1 1 125
2 1 90
3 1 84
4 1 146
5 2 84
6 2 90
7 2 125
8 3 2-1/2
9 3 3
10 3 2-1/16
11 3 3-1/2 [/pre]

Also, [blue]this part[/blue] would be less confusing (to me )[pre]
tbl_Product
(pk)ProductID Product [blue]IsActive[/blue] or just [blue]Active[/blue]
-----------------------------------------
1 Product1 Yes
2 Product2 No
3 Product3 Yes[/pre]


---- Andy

There is a great need for a sarcasm font.

 

[Andrzejek]

About this IsInactive, IsActive, Active stuff. To me it is a Status. I know right now you have just the 2, active or not. But imagine your managers may want to have Active, Not Active, Cancelled, On-Hold, Finished, Forgotten, Imaginary, and whole lot more options to declare a status. That would call for another table (tbl_Status) to keep all of them.

Just a suggestion...


---- Andy

There is a great need for a sarcasm font.

 

[nikademous]

Andy, Do you mean like the relationship image I attached?

 

[Andrzejek]

Yes, looks good to me.
My approach to design a database is: I want to have any information in one and one place ONLY. If you have anything repeated (other than indexes, PK/FK relations, etc.) – you are doing something wrong.

I know that sometimes the data needs to be de-normalized, but that should not be the rule, that needs to be an exception, and needs to have some strong reason to be so.

It is a pleasure to work with well-designed data base. And you can do anything you desire.
It is a nightmare to work with bad design – believe me. And you have to bend backwards to accomplish even some small, easy tasks.



---- Andy

There is a great need for a sarcasm font.

 

[nikademous]

Andy you wrote:

I want to have any information in one and one place ONLY

After I created the tables and put some data in I dont understand why I have each "Size" for the component in two separate tables and even if I did how can I set up a entry form like the one attached? The entry form is an admin form its where I have 11 components and for each component there are several sizes.

(Attachment is new structure along with data for Product 1 and Product 2)

 

[Andrzejek]

I hope somebody else jumps in to help, because I am not an Access expert


---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

I don't fully understand you requirements but the file I provided allows you to select a product and then enter component sizes. It seems your Size field is free text so I'm not sure why you use a dropdown without having a separate table of just unique sizes.

I set up a subform for entering the component sizes using the link master child properties to the product combo box.

There isn't a method for entering a new product or new components. This would require a couple other forms. You could use the existing main form for adding product but you would want to change the link master/child and do a couple other tasks.

It isn't clear if only specific sizes should be available with specific components.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[nikademous]

I'm not sure why you use a dropdown without having a separate table of just unique sizes.

Duane, I do have unique sizes for each component and thats how I wanted the form but how do I set up my table structure to have unique sizes to each component AND work like it does on the form/subform?

Thanks,

 

[dhookom]

Did you look at the file I uploaded? It allows you to maintain a list of sizes based on products and components.

Can a component be associated with multiple products?
Are sizes related only to components?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[nikademous]

Yes I looked at the example... It allows you to select a product then in the sub you can select components for that product and for each component it allows you to select a size. What im looking for is all that BUT each component has its own size thats only available to that component. Example .txt Attached:

I cant figure out the table structure ect...

 

[dhookom]

Then you need a table of ComponentSizes that you fill with the data from your text file. Then you can use cascading combo boxes. There are references on the web that describe overlaying the combo box with a text box to display the appropriate values while still filtering the combo box row source.

I don't filter the combo box row source. I simply add a computed column that shows whether or not the record is valid. The valid records appear at the top of the combo box items.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[nikademous]

I been messing with this for a few days. Added a table for my sizes but I still cant figure out the form frm_AddDeleteProdComponents and its sfrm. All I wanted to do was select a product and choose a component and show the available parts/sizes for that component. I created a form (frm_EditAddComponentsORParts) that I can add and change parts/ sizes based on the component chosen now I need to for a component and show the available parts/sizes for that component. I manually entered the data for product 1. I can manually enter the data but just cant figure out how to save it by entering it in a form.

New example attached...

 

[nikademous]

Duane.... I finally got it figured out

 

[dhookom]

thanks for the update

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016