How to use 2 separate qrys in 1 form?



I'm still stuck on a problem posted earlier. I have an inventory db: tracks
in-stock materials, outstanding orders. All lab staff can order items using
corp. custom software but often confusing as orders are received. Creating
an inventory (vs. updating a running calc.) as no in-stock inventory stats
currently exist.
Product + Order tables related by autonum field 'Itemnum'. I need to track
items currently on order in Order table; delete Order record as that item
received (the custom software will store history). A form will allow order
entry; hope to copy solution to a 2nd form to verify the orders received + to
add the received items to the lab inventory.
I need the 'new order' form to: first list all current orders (should canel
an order for an item if someone already ordered it) [list box -no edit- from
Order table] before entering new order stats starting with selecting the item
name from Product, if it exists (no entry, spelling errors; can also see that
10 - 250ml beakers already in stock!) [Combo box from Product table] and I
need to add the selected 'Product' ItemName, Itemnum (or new item if it's
not in Product) to the Order table when we exit the form.

How can I display both the current 'Order' table or query and the 'Product'
table on the same form? I thought better to split Product, Order data;
orders often have > 1 item & we could get > 1 person ordering different #'s
of same item (new projects). Is one very wide table needed?
I hope to convince coworkers this idea has merit. Thanks for all help to
date. Still trying Northwinds & many great links but not quite there.
Also trying to learn VBA. Any ideas appreciated.


Use subforms for each would be one way.

I am concerned about your design. In a typical order entery system, the
orders are stored in two tables. The Order Header table and the Order Detail
table. The Header table stores information about the order (who ordered from
whom and when, etc). The Detail table has a row for each item ordered thant
includes information about the item (quantity ordered, unit price, etc).

The form is constructed so that the Header is the record source for the form
and there is a sub form that has the Detail as the record source.

For the Product table, it is often the row source for a combo in the sub
form so the product info can looked up easily.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question