×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Data entry with cascading boxes on a continuous form

Data entry with cascading boxes on a continuous form

Data entry with cascading boxes on a continuous form

(OP)
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?

CODE --> vba

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 

RE: Data entry with cascading boxes on a continuous form

(OP)
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.

RE: Data entry with cascading boxes on a continuous form

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

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

so your other table would look like this:
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  

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


---- Andy

There is a great need for a sarcasm font.

RE: Data entry with cascading boxes on a continuous form

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.

RE: Data entry with cascading boxes on a continuous form

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.

RE: Data entry with cascading boxes on a continuous form

(OP)
Andy you wrote:

Quote (Andrzejek)

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)

RE: Data entry with cascading boxes on a continuous form

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


---- Andy

There is a great need for a sarcasm font.

RE: Data entry with cascading boxes on a continuous form

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

RE: Data entry with cascading boxes on a continuous form

(OP)

Quote (dhookom)

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,

RE: Data entry with cascading boxes on a continuous form

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

RE: Data entry with cascading boxes on a continuous form

(OP)
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...

RE: Data entry with cascading boxes on a continuous form

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

RE: Data entry with cascading boxes on a continuous form

(OP)
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...

RE: Data entry with cascading boxes on a continuous form

(OP)
Duane.... I finally got it figured out

RE: Data entry with cascading boxes on a continuous form

thanks for the update

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close