Auto Populating Sub Form

[joiry]

I’m fairly new to Access so I hope I can get the terminology correct. I’ve been working on creating a database which has a one to many relationship. With the help of the people in the table forum, I do have that working correctly, and even have a form and sub form where the data can be successfully input.

In short, I have a main item table to track my widgets. I have a reference or look up table of colors. Third, I have a relationship table that manages the one to many relationship. My main input form has all the fields that need to be plugged into the primary table. The Sub-Form is setup for continuous forms which allow me to input as many colors as I need. Again, this all works great.

Now, to go to the next level. There are times when I want to select many (lets say 8) different colors to my widgets (kept in the primary database and form). To select each all 8 colors each time is somewhat of a pain. It is my hope and intent to create a button on my sub form that would populate my subform with the static 8 colors that I want, thus bringing me down to a single click instead of 16. (1 for the drop down, 1 to select the color).

Is this at all possible? If so, can someone give me a shove in the right direction?

Structure as follows:
TblWidgets
• Widget – Text
• Value – Numeric
• Description – Memo
• Type – Text

TblColors
• Color - Text
• Description – Text

TblWidgetColorRelationship (one to many relationship)
• Widget – points to primary key in TblWidgets
• Color – points to primary key in TblColors

Thanks,

Joiry

 

[ThreeCrow]

What are the color relations to your widgets?

Are your widgets seporate products that come in different colors?

If Not, do you always want to choose one color or eight colors?
No other chooses like maybe three colors?

Need some more info as to what you are attempting to do.

 

[joiry]

colors

The widgets can come in multiple colors. As little as one and as many as 8.

As it is today, I have a continuous subform, so during input I give the base widget information, then in the sub form I select the first color. This creates a second drop down box for the second color and so on.

My quandry is that a significant number of my widgets have 8 of the same colors. Thus, performing this function 8 times is cumbersome. But it's not a significant enough number to make it a default.

Thus, i was hoping to add a button or something to the sub form, that when clicked, auto-selects the static 8 colors that I want. It's a time saving mechanism is all.

I hope this makes it more clear.

 

[ThreeCrow]

The widgets can come in multiple colors. As little as one and as many as 8.

I believe that you would better off createing one Table that is used as a Lookup Table. And use this as your RecordSource for your colors CombBox.

Something like:
ColorsID:
Color:
ColorDescription:

As an example, I will use four colors.

1;R;Red
2;G;Green
3;Y;Yellow
4;Bl;Black
5;RY;Red,Yellow
6;RBl;Red,Black
7;RGY;Red,Green,Yellow
8;RGYBl;Red,Green,Yellow,Black
and so on.

Or give the colors code numbers. Doing it this way in future (Always Look Ahead) you will be able to add colors and multiple colors.

Hope this helps a little more

 

[joiry]

Look Ups

Hmm.. I don't think I've been successful in communicating my issue. Let me try another way. Attached (I hope these aren't too large), I have some pictures of my forms.

Example 1:

A picture of my base form and sub form. As you can see, I have a drop down, which references a look up table for many different colors. I can select any color I wish and associate it to my widget.

Example 2:

As you can see, as I select colors, more rows are formed until I get as many as I want. I'm really only limited by the number of colors available, as you can't select two colors.

Example 3:

This is what I want. But, I'm hoping I don't have to perform these 8 selections every time i want this color combination. My intent and goal is to be able to have a button or some kind of key sequence embedded within the form, and have it automatically produce these results.

I know it's a bit lazy, but it can save a lot of time on data entry.

Thanks,

Joiry

 

[ThreeCrow]

Hmm.. I don't think I've been successful in communicating my issue.

No Sir, I understood, although a picture is worth a basket of words.

If you have to list the colors, how about a Pop Up Form that is activated from your Main Form via CommandButton. That has a MultiSelect ListBox displaying a listing of your colors (this will give some growing room), also a CommandButton. From the MultiSelect ListBox you choose your colors wanted. Then pressing the CommandButton, this runs a Macro or fires some Code, that appends the color chosen to the underlying Table, and then requeries the subForms underlying Query.

You can even do the for your materials, if you like.

You could even use a Pop Up Form with CheckBoxes.