Adding Data from a form into a table

[fatmcgav]

Hi, im not entirely sure where this belongs - could be tables, reports or VBA - so im guna post it here.

Right, heres the situation.
I have a form containg two sub-forms. The main form is used to input/check order details - name & order date. The first sub-form - sfrmPond is used to input the dimensions of the pond, and calculate the pond area. The second sub-form - sfrmPondOrderDetail - contains the kit iteniary - product, quantity and price. There are 6 products that are added to each kit, 2 of which have a fixed quantity and price, and the other 4's quantity and therefore price is affected by the dimensions of the pond.

I want to be able to save these quantites and prices into the tblPondOrderDetail table, so that i can use them to create a printable report.

Any ideas on how to do this?

A copy of the database is attached for you to have a look at, but i would prefer it if u could tell me any changes etc and how to implement, cus being a novice, i always want to learn.

Thanks Again.
Gavin

 

[fatmcgav]

Anybody got any ideas?
Come on guys, ure usually brillant at dispencing the solutions.

Thanks

 

[Pat Hartman]

There are some design issues. Although you've stored the pond components in a normalized structure, you are actually treating them as if they were flattened as in a spreadsheet. What happens if you have a new pond and that pond has a waterfall in addition to the fountain or instead of the fountain? You would need to change many things to incorporate a new component.

You need to store some of your parameters in the product table so that they are not hardcoded. You can also add codes to the product table to indicate whether the product's final cost is based on the area of the pond, its perimeter, or depth, or simply the quantity of the part. That will allow you to generalize the calculations and make it possible to make new parts without major rework.

The calculations need to be done with user defined functions rather than with nested IIf()s. That will give you more flexibility.

As long as you are storing the basic information needed to calculate the costs, you don't need to store the final results.

I would change the table structure slightly. I would add a new table that defines which products go with which pond. That allows you to define multiple pond types each including some list of products. You would then use that table to populate the order details rather than the product.

I would not allow new pond types to be added via this form. That is a separate operation.

Without knowing more about your requirements, I can't give you more specific advice.

 

[fatmcgav]

Thanks for the reply Pat
I think I get roughly where you're coming from, but some of it i havent go a clue, dam my Access noobiness.

i am actually designing the database for a GCSE ICT coursework project. All that i need to be able to do is enter a Width, Length and Depth, then click a button and it produces a invoice/iteniary of the kit.
The problem is that the form dosent store the quantities of the products reguired, so i cant easily design the report.
All i want is for the form to store the quantites of the plants, and i can use report formatting etc to add the fountain and water lilly. A quantity of 1 and the unit price is all that is needed for those . For the liners, i need the full price, although a quantity of 1 is ok. But i need the full quantites and prices for the Underwater plants and decorative plants, because it would be too complicated to calculate these in the report.

I dont need to add any new products etc.
If you dont get any of that, let me know and i'll try to clarify.

If you could give me a guide as too what i have to do, it would be greatly appreicated.

Many Thanks
Fatmcgav

 

[Pat Hartman]

You calculate everything in the query. That is why it is not stored. Use a similar query to simply calculate it again in the report.