how to generate a form using data from another table.

[liviandi]

i have an item list table containing this data
itemID1 banana
itemID2 orange
itemID3 potato
...
itemID20 spinach

i also have another transaction table that takes in
orderID itemID quantity

i want a form that pulls out all these items and has an input for me to create an order

i.e. the form should look like this from the start
banana BLANKTEXTBOX
orange BLANKTEXTBOX
potato BLANKTEXTBOX
...
spinach BLANKTEXTBOX

SUBMIT

when i click submit it should insert new data records into the transaction table

how do i do the prepopulation bit?

i know how to do a blank one that i fill in one at a time using a subform, but i don't know how to get the subforms to already have all the items inserted so that the user only has to key in the quantities.

[computerfox]

Something like this?

http://bytes.com/topic/access/answer...-another-table

[liviandi]

no it's different.

okay, maybe i re-explain.

table 1 - item data
fields: itemID itemName someOtherFields
it has x number of records
1 banana yellow
2 orange orange
3 potato brown
...
20 spinach green

table 2 - transactions
fields: orderID itemID quantityPurchased
1 1 3
1 2 3
1 19 7
2 7 5
2 5 5
etc

instead of having a form that has a subform that i can insert the item one by one
e.g.
i select from dropdown - potato, then fill quantity 4
i select from dropdown - spinach, then fill quantity 7

i want the form to appear immediately as a grid containing all the items in the item table

banana textbox
orange textbox
potato textbox
...
spinach textbox

submit button

then immediately if the quantity is not 0, the record is inserted into the transaction table.

[liviandi]

so it's not

i select banana

then the form becomes

banana
yellow
textbox

that one i can do.

[Rabbit]

Wouldn't the form be a bit unwieldy to use? Usually order forms are done by selecting what item they are purchasing, not listing every item available for purchase. Makes the form a bit difficult to use when there's a ton of irrelevant controls the user doesn't need to use.

[liviandi]

it's a stock checking form, so all the inventory data needs to be there.

[Rabbit]

I see, I was confused because the table had a field named order id.

One way to do it is to insert all the items into the transaction table when they decide to initiate an inventory count.

But if you don't want to include the 0's in the transaction table, then insert them first into a temporary table. Then when they click submit, insert the non-zero rows into the transaction table.