Total of subform in form achieved but how do I set the row source to update table?? (1 Viewer)

eddix99

Registered User.
Local time
Today, 21:57
Joined
Jul 7, 2016
Messages
32
Hello all,

I have just posted over in the welcome sub, and this is my first proper post so here goes..(i'm sure the answer is very simple)

I am currently trying to create an invoice form using a subform for the invoice line items. I want to be able to total the subform in the main form, and have found a couple of solutions in the web.

I have managed to carry this out, and can calculate the subtotal on the main for using an unbound textbox. The solution involves a hidden calculated text box in the sub-form footer.

The issue I have is that I cant figure out how to now update the relevant table fields with the result.

When I use a combobox I have the 'control source' and the 'row source' properties to pull data from and write data to. With a text box I dont have the 'Row source' to configure??

I would like to have the sub-total, GST (VAT) and Grand-Total stored in the table rather than calculating it everytime on the fly.

Is this the right approach??

Many thanks in advance.
 

eddix99

Registered User.
Local time
Today, 21:57
Joined
Jul 7, 2016
Messages
32
I think I might have figured it out. I'm going to try:

Creating a calculated field in the table itself which draws the hidden line-item total text box in the sub form. From there I will dynamically calculate the GST/VAT and Grand Total in separate fields. Hopefully this will update the Form text boxes without a hitch.

I need to keep track of the GST/VAT amount for reporting purposes so will keep the value in the table itself for future referencing by another report.
 

eddix99

Registered User.
Local time
Today, 21:57
Joined
Jul 7, 2016
Messages
32
Just to say that I'm still a bit stuck with this. I don't mean to come across as a complete noob!

The problem with my above theory is that when I create a calculated field in the table design view I can't reference any objects that are not part of the table itself.

At the moment I'm looking at solving my problem using queries. I have created one which sums the invoice line items, which works fine and gives me my subtotal. From there I'm not sure what to do and how to get this subtotal into my table.

I'm mulling over the advantages of storing calculated values in tables versus calculating them on the fly. I'm concerned that if I choose to calculate on the fly then a situation will arise where I have to generate a report with a few months of invoices, and the computer will grind to a halt summing up all of the line items and then adding tax etc.

Perhaps someone can enlighten me. In the mean time I'll keep trying my end.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 22:27
Joined
Jan 20, 2009
Messages
12,849
I'm mulling over the advantages of storing calculated values in tables versus calculating them on the fly. I'm concerned that if I choose to calculate on the fly then a situation will arise where I have to generate a report with a few months of invoices, and the computer will grind to a halt summing up all of the line items and then adding tax etc.

A rule of thumb with databases is to never store a calculation unless in use you find that it is unacceptably slow. Even then, poor performance can usually be tracked to other reasons such as inadequate indexing, inefficient queries or just the wrong approach to the problem. Only store results after you are completely sure that the problem is the sheer size of the job.

I can almost guarantee that the size of the task where this will become necessary will be a lot bigger than you currently expect. Generally, the performance of a computer processing records in a well constructed database far, far exceeds human expectations. I am still often in awe of what they can do.

I remember at times being disappointed when a huge iterating process I was developing exited after a five seconds. I would presume that my procedure must have had an error and skipped most of its work, only to find that it had indeed done exactly what I meant it to do.
 

eddix99

Registered User.
Local time
Today, 21:57
Joined
Jul 7, 2016
Messages
32
Thanks for the reply Galaxiom,

I've been thinking about what you have said and it makes complete sense to me. Storing calculations does mean additional space in the db and it means that the data could become inaccurate if the 'argument' for the calculation changes.

What i'm still unsure of is whether this applies to financial records. For example, when storing debtor invoices data, I would hesitate to calculate Sub-total, GST and Grand Total on the fly. The reason is that If the gov here decide to raise GST (which is a likely occurrence) then any application of an amended query would change the historic records, which is something that you definitely don't want to do.

Perhaps you could offer some thoughts on this matter.

Many Thx.

Ed
 

eddix99

Registered User.
Local time
Today, 21:57
Joined
Jul 7, 2016
Messages
32
Just been having a look around about the above, but have generalised my search and found a few more posts on the issue.

I'm now thinking that I will store the individual line items and their value AND the tax rate on that particular transaction. That way, any query will calculate the correct tax rate on a historic record, even if the tax rate has subsequently changed.

Does this seem like a good idea?

Thx.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 22:27
Joined
Jan 20, 2009
Messages
12,849
There are definitely grey areas and it depends how much you want to do to normalize the data. A fully normalized GST system has a tax table that records the date when the rate changes so calculation remain consistent. It does increase the complexity of the system.

Is it worth it? Opinions and practices differ, even for the same developer.;) While most of us freely spout normalization in our posts, if we are honest, we all have databases that are not fully normalized.

There are a number of ways to store the results of calculations but they all involve VBA. A Control (eg textbox) on a form can have a ControlSource expression instead of the name of the Bound field that is the default when Access Wizard makes a form from a table or query. But then you need to write to the result to the recordset with VBA.

Alternatively an Event is used to put the result of the calculation into a bound control which then automatically saves when the form updates because it is bound. Usually this will be triggered by one of the Update events.

BTW By default, the form wizard will name the control the same as the field it is bound to. When referring to a name, the default points to the control. When no control by that name is found it points to the field by that name in the recordset. You can change the name of the control if you like.

There are many other form design variants, including those which don't save new records and updates automatically, such as unbound forms and Transacted Bound Forms. No wizard for them though and a substantial learning curve.

I would strongly suggest you put all you focus on the data model first so you get table structure sorted before you even think about forms. Get this wrong and it can really make for hard work.

Don't be frightened of VBA, thinking the macros are an easier option. Macros are very clunky and it is worth the steeper learning curve to get into VBA, because there is literally no limit to what can be done in VBA once you get going.
 

eddix99

Registered User.
Local time
Today, 21:57
Joined
Jul 7, 2016
Messages
32
Glaxiom, Since your post a month or so ago I have been plugging away at Access and VBA and feel alot more comfortable with them both. I followed your sage advice and worked hard on the data structure before spending time on the reports. In fact I have created more 'proof of concept' forms and tested the data structure thoroughly.

There will probably be some data errors when I start to put test data through it but I am happy so far.

I do apologise for not not responding to this post sooner.(Holds head in hands)
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 22:27
Joined
Jan 20, 2009
Messages
12,849
One thing I should have mentioned with VBA. Make sure to to include the declaration Option Explicit in all modules. This forces all variables to be declared and avoids a lot grief if you happen to misspell a variable name in the code.

If will be included automatically if you tick "Require Variable Declaration" in the Options of the VB editor. (Tools menu).
 

eddix99

Registered User.
Local time
Today, 21:57
Joined
Jul 7, 2016
Messages
32
Yes I have done this. And it has saved me a couple of times already. Thanks for the tip.

Its quite difficult to learn the first programming language. I read alot before even writing my first sub. Initially I thought that I had wasted my time as I still didn't get it when actually writing stuff. But on more than one occasion my mind was 'jogged' by something I had previously read. So I guess that I need to do both theory and practical work to get the hang of VBA.

Regards

Edward
 

Users who are viewing this thread

Top Bottom