By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,235 Members | 1,600 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,235 IT Pros & Developers. It's quick & easy.

how to generate a form using data from another table.

P: 4
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


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.
May 23 '15 #1
Share this Question
Share on Google+
6 Replies

P: 278

P: 4
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

instead of having a form that has a subform that i can insert the item one by one
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.
May 23 '15 #3

P: 4
so it's not

i select banana

then the form becomes


that one i can do.
May 23 '15 #4

Expert Mod 10K+
P: 12,430
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.
May 23 '15 #5

P: 4
it's a stock checking form, so all the inventory data needs to be there.
May 23 '15 #6

Expert Mod 10K+
P: 12,430
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.
May 23 '15 #7

Post your reply

Sign in to post your reply or Sign up for a free account.