Orders form with subform, but need product details, and order hist

[Guest]

_Dear experts,

The underlying tables are orders, order_detail, and products. Order_ID is PK
and FK on detail. Part ID is PK in products. I have an orders form with
order_details subform.

I have been told I need more than just Prod_Number on the orders form. We
would like to have item description, size, and product image visible during
the order selection.

Do I create another subform from a query? If I have more than one line
(multiple products) for an order, how would the product detail be displayed?

I have also been asked to include recent orders history on the same form if
clients ask what was their last order, etc.

Your help is always much apreciated!
Thank you,
Ivan.

 

[Allen Browne]

Create a query to use as the RecordSource of your subform.
The query contains the Order_Detail and Products tables.
If Order_Detail.ProductID is not a required field, be sure to use an outer
join (i.e. double-click the join line in query design view, and choose the
option, "All records from Order_Detail, and any matches from Products.")
In the query grid, add the fields you wish to display in your subform.
In the subform, set these properties for these fields from the Products so
the user cannot change them:
Enabled: No
Locked: Yes
Tab Stop: No
BackStyle: Transparent

The Northwind sample database does give some example of storing pictures,
but this is really not practial. Instead, store a link to the picture.
Details:
http://www.mvps.org/access/forms/frm0030.htm

The "Recent Orders" would be another subform, or possibly another form you
can pop up by clicking a button. Its RecordSource would be a query that gets
the recent order data from several tables, and you probably need to make it
uneditable.

 

[Guest]

I am very weak with queries.
I have setup my query to be the record source for the subform.

The subform for order history is set up. It works well when the main form
pops up with order information, but the order history subform only displays
the data that is on the main form.

How do I set up the query, or the subform for that matter, that will show
order history for the Cust_ID on the main order form. Sometimes customers
call in for orders and question the amount of credit they have. I would like
the history subform to exist so that the user can review past orders for the
customer that is calling!

Thank you VERY much!
Ivan.

 

[Allen Browne]

The subform is now relating to the wrong field in the main form?

Open the main form in design view.
Right-click on the edge of the subform control, and choose Properties.
On the Data tab of the properties box set these properties:
LinkMasterFields
LinkChildFields
Beside LinkMasterFields, type the name of the field in the main form -
Cust_ID.
Beside LinkChildFields, type the name of the matching field from the
subform.

 

[Guest]

Thank you very much, Allen. My main form was based on a query. So I went
back and made a new subform that did not have a source from the query!

Thanks again, this site is God's gift to Access users.