Product item list that expands based on number of items (???)

[sprtrmp]

Here goes nothing...
Alrighty, folks-that-are-better-than-I-am-at MS Access... I have a form that will be used to create a list of items that are issued to individual employees (one list per individual). each individual will have different number of items issued to them (anywhere from 1 to 20 (or more) items). Rather than create a form that has 20 (or more) spaces, or lines where an item can be input, I would prefer to create a form that initially starts off with one "space" for the first item, then some kind of button or something that would allow me to add another "space" to add another item, then another, and so on until I am done adding items for that individual. Does anyone have any idea how to accomplish this, or someplace they can send me to learn how to do it??
thanks in advance
Mike

[pbaldy]

Sounds like a form bound to the employee table and a subform bound to the table with their items. Master/child links will keep the subform in sync with the form. Visualize an invoice form, with invoice header info at the top and a variable number of detail items underneath.

[sprtrmp]

Sounds like a form bound to the employee table and a subform bound to the table with their items. Master/child links will keep the subform in sync with the form. Visualize an invoice form, with invoice header info at the top and a variable number of detail items underneath.

UUUH, OK. I think I get that, (not sure how to set that up exactly, but more on that later). I have the form set up with a query as the record source. all of the employees ARE on one table while the items are on another. Can I use the query as a source for the form and the sub form? can I use the SAME query for the source for both, or am I not seeing the forest through the tress?

[June7]

Main form bound to employees table. Subform bound to dependent table.

[sprtrmp]

...and a variable number of detail items underneath.

Alright, I understand the table thing now, but I guess my question is "How do you set up the variable number of detail items underneath"??

[June7]

Review https://support.office.com/en-us/art...8-C266FE084102

Just go to new record row and input data.

[sprtrmp]

I think I have adding subforms to a form figured out, but my original question was how do I add some type of input method that would allow me to add a variable number of items to an individual's list? or something like that. I don't want to add a form with 50 lines to it in an effort to cover "enough items for every possible situation", because I don't know how many that is. I want to add one input line and a button that would allow me to add "X" number of items for one person, then "Y" number of items for someone else.

the way the form is "structured" (and I use that term loosely) is that I have a table with an empoyeeID, then about 20 fields for items on a Products table, (fields are named Item1, Item2, etc) and a quantity field for each item (Quantity1, Quantity2, etc). So that when I go to the form, I can select an employee, then choose an Item, then input a number that indicates how many of that item the employee has. THEN, I want to click a button, saving that item (and quantity) to the employee's list, and have another line/box/whatever to add another item and related quantity, and so on.

Also, if anyone has a better idea on how to structure a table that would work better, I am all ears. what I have now is a table with an employeeID (tied back to a table listing of employees) and about 20 item fields and 20 quantity fields related to (and by "related" I actually mean "next to") each item field.
Would it be a better idea to create an individual table for each employee's list of assigned items so that I am not creating a bunch of blank fields on one massive table for an employee who only has one item?

[ssanfu]

You have designed your tables like an Excel spreadsheet. In Access this is known as "committing spreadsheet"
Excel spreadsheets are "short and wide".
Access tables are "tall and narrow".

then about 20 fields for items on a Products table, (fields are named Item1, Item2, etc) and a quantity field for each item (Quantity1, Quantity2, etc).

This is not a normalized table structure.
You should have a PK field, a field for the FK to the employeeID, a field for product and a field for Quantity. This allows you to have any number of products (items) - be it 1 or 1000, without having to change the structure of the tables, forms, queries or reports.

[sprtrmp]

You have designed your tables like an Excel spreadsheet. In Access this is known as "committing spreadsheet"
Excel spreadsheets are "short and wide".
Access tables are "tall and narrow".


This is not a normalized table structure.
You should have a PK field, a field for the FK to the employeeID, a field for product and a field for Quantity. This allows you to have any number of products (items) - be it 1 or 1000, without having to change the structure of the tables, forms, queries or reports.

YES, guilty as charged, but I couldn't see how else to do it. As it is, the table seems really bulky.
If what you say is true (and I am sure it is), doesn't that mean that I need to have a separate table listing items/products for each employee? (65 and counting).

Forgive me, I am under a time crunch with this thing and don't have time to learn Access backwards and forwards (and I am not all that bright to begin with). I guess my question is how do I store more than one item or product for each individual employee without having a separate field for each item, and without creating one table per employee?

Sorry, I am 100% SURE it is me that is missing it, and not anyone else's explanations.

[ssanfu]

Sorry, computer had a major melt down and only have A2000 until I can rebuild my main confuser.


Here is a quick and dirty example of what I understand you are trying to accomplish.


Access complains if you try to have both the main form and the sub-form in continuous forms view. But if you force it, both forms can be in continuous forms view.


A2000 format

[sprtrmp]

A-HA!!!!! You truly are a wise man!!
I get it now, it makes much more sense now that I see it.

I have 2 new questions though: first, from the looks of things, you are entering data onto the subform into a query, Right? why are you not entering data directly into the table from the form? (are you also getting your employee data on the main form thru a query as well, or does that come directly from a form?)

Secondly and most importantly, the subform where you would select the products "grows" after you select the item, another line pops up, where you can select another type of item. how do you get it to do that? that is CLOSE to what I am looking to do.

Thanks
Mike

[pbaldy]

Some people prefer binding forms to queries, Steve appears to be one of those people. It would work binding directly to a table as well.

The new record showing up at the bottom is normal behavior for a form in continuous or datasheet view. You don't have to do anything special to make it happen.

[sprtrmp]

Some people prefer binding forms to queries, Steve appears to be one of those people. It would work binding directly to a table as well.

The new record showing up at the bottom is normal behavior for a form in continuous or datasheet view. You don't have to do anything special to make it happen.

OK, I will play around with it and see if it works the way I want it to, but I would really like that new line to be "button-activated" just to give me and the other government-trained monkeys around here an opportunity to double check what we are doing and/or to keep them from just adding stuff all willy-nilly. Anyone have any ideas on that?

if not, I think I have enough info to get somewhere close, and my thanks (and apologies) to everyone who helped me out on this one.

[pbaldy]

You can try toggling the Allow Additions property of the form with your button. Another option is leaving the existing subform with Allow Additions = No, and stacking another subform above/below it with Data Entry = Yes, and toggling the visibility of that second subform with your button.

[sprtrmp]

You can try toggling the Allow Additions property of the form with your button. Another option is leaving the existing subform with Allow Additions = No, and stacking another subform above/below it with Data Entry = Yes, and toggling the visibility of that second subform with your button.

*POOF* <==That was the sound of my head exploding

I think I am in far enough over my head and I am just going to leave it where it is, I think I have done enough damage

Thanks again to everybody