Database advice

[June7]

Forms are not setup as I suggested. Customer form should not be a subform. It should be the main form. Then Order form is on Customer form, then OrderLine form is on Order form. Three levels of forms, synchronized at each level with Master/Child properties of the subform containers. Anything else will require code or manually opening and closing independent data entry forms.

1. set the Default View property to something other than Continuous

2. fix the form arrangement before addressing this issue

3. OrderLine form in Datasheet view click the Totals (looks like sigma) button on the ribbon, otherwise will need code

[PRINCE SWAGG]

So do over the forms?

Create a customer, then order sub form, then orderline sub/subform?

[June7]

Delete Customer subform from Order form.

Rename Customer subform to just Customer.

Put a subform container control on Customer.

This container Source Object would be Orders Form.

[PRINCE SWAGG]

What do u mean by "Put a subform container control on Customer."??

[June7]

How did you create the Customer subform on the Order form? This is a container control.

Look at Access Help for guildelines on how to create a subform on a form.

Basically, you need to select the subform/subreport container control from the design tools and drag it to the form then set the Source Object property to the form the container will hold.

You can use design wizards to help build forms and set up controls, but I don't like the wizards. Again, Access help has info.

This is elementary Access designing. Sounds like you need to go through some basic tutorials.

[PRINCE SWAGG]

Well i know how to create the forms and sub/forms, i just was not sure what you meant by container.

[PRINCE SWAGG]

I got mixed and sort of found my way but i not able to get the total of the TOTAL ORDER COST.

Any assistance?

[June7]

Open OrderLine form in Design View set Default view to Datasheet View, save the form.
Switch to Form view.
Click Totals on the ribbon Home tab, Records section.
In the bottom row, under the ExtendedPrice column, select Sum from the dropdown.
Close the OrderLine form and it should save with this Totals setting and should be presented when you open the Customer main form.

[PRINCE SWAGG]

That actually helped BUT could you believe for the product total cost which was the extended column you created is only getting the option for a count and not a sum or average.

I am getting sum for quantity and unit price but not for product total cost hmmm.

[June7]

I've never used this Totals feature before so am learning too. Apparently, calculated result in textbox is seen as a string value. I tried using the Val function in textbox and that didn't help either. So I built a test query and did a calc there, then bound a textbox to this constructed field. Now the math functions are available for this field. I don't have your project with me to try but this should translate there.

[PRINCE SWAGG]

How did you do that query? Because i tried but i was not successful.

I have attached the database, Please help as soon as you can because i have to present it tomorrow.

[June7]

OrderLine form in design view
Open the RecourdSource query builder
Type this in a Field cell - ExtendedPrice: [Unit Price]*[Quantity]
Close builder and save changes
Change Product Cost textbox ControlSource to this newly constructed field (ExtendedPrice)
Save form
Open form in Form view
Click on any row
Click on Totals on the ribbon
Click on Total row under Product Cost column, set calc

[PRINCE SWAGG]

Thanks so much, i feeling so happy now, i was getting so worried but i have one more question.

I am getting the total for sure but in the ORDERS TABLE i have TOTAL ORDER COST. Is it possible to get the total in that field?

Or do you have another possible solutions, like after the employee save the cust details and order detail probably a report or something, can show the full order? eg, the cust details, order details with the total cost of the order.

THANKS SO MUCH FOR YOUR ASSISTANCE.

[June7]

Would require code and not recommended. Aggregate (summary) calculated results should not be saved. This value should be generated in a report (an Invoice or some other output) at run time and NOT saved to table. Use report Grouping & Sorting with summary calcs. And that is topic for a new thread if you encounter issues.

[PRINCE SWAGG]

Understood and thanks again.