Question Subtotal on sub-form saving to table

[access_user]

Hi Dears ,

I am expecting someone can help me out of this , I am not good at MS access ,
please find the attached MS access DB for INVOICE printing . I am facing two issues there .

1. {form INVOICE } sub-total must be Calculated from line total on {INVOICE DATA Subform} , I know this can be accomplished by adding a new field at sub-form and calling that value to main form's sub-total field .

2. And the real issue is i want to save this value from subtotal on form INVOICE to table field name subtotal .

3.I wish to make a reporting format for each INVOICE NUMBER , whenever report is being generated , the report shows more than 1 invoice details .

please help me if you can , all the db is free to use for anyone.

 

[StarGrabber]

Hi, access user,

why do you want to save the "subtotal" value? I don't see the need. A "total" is, and should always be, the result of a calculation. Imagine, you have to change one of the usual values, let's say the quantity. Then - naturally - you have to recalculate the "subtotal", but not in the table. There you have to save the changed quantity only - and that is sufficient. One way to recalculate it would be in a query, another in the form. - So you should store only the data needed for the calculation (even if Access provides the feature of calculated table fields)! Try to avoid redundant data storage by all means (for further suggestions see links at the end of this post).

Relating to table "Currency": currencies and countries are two different things. But there is - of course - a relation between them. One currency can be used in several countries but not vice versa. This fact can be represented by two tables, related by an "1:n" relationship (one to many).

Generally, your data model has some inconsistencies. I tried to rectifiy those (e.g. the account number in tables "Company", "Bank Accounts" and "Contacts"). And your database looks like it was mainly "developed" by the Access wizards. Wizards are a good, useful thing, above all for beginners. But after the wizards have done their job, you should rework some parts of it, e.g. give the controls on your forms (or reports) names following a naming convention (in 3 of the forms this is still pending). If you don't, they will keep the same name as the corresponding fields of the table. This can cause trouble at the latest when you start writing VBA code.

Regarding your report, it has a query as data source now (as the subform "sfrInvoiceItems" has). This query has one parameter: the ID of the invoice. It will be taken from the textbox on "frmInvoice", if the form is loaded. If not, a parameter box opens. There you can enter an ID manually.

See the result of all that in attached file. - It will be diffcult for you to get along because I changed a lot (e.g. all lookup fields in the tables to normal fields). But don't worry, this file should give you an idea of how it could work, only. -

Right now your DB is clearly laid out. But almost all database applications tend to grow and get more complex. Therefore you may find some guidance at http://www.access-programmers.co.uk/forums/showthread.php?t=172363

If your SUB TOTAL field (or any other calculated fields taking values from other forms) shows "#Name?" then go to the control source property and change "[Formular]" to "[Form]" (I'm not working with an English Access version).

You've just taken the first steps in DB development. Now move on and take the next!

http://en.wikipedia.org/wiki/Normalization_(database)
http://www.databaseanswers.org/data_models/index.htm
http://www.dfpug.de/loseblattsammlung\Conline\Cworkshop\Cjim_booth/seite2.htm
http://www.bkent.net/Doc/simple5.htm

 

[access_user]

Thanks man for your help .