Total Formula

[louisa]

Sorry i dont no anything about code, could you advise where i need to place this?

Many thanks for all your help

 

[vbaInet]

Open your form in design view. Unde the properties, click the Event tab and click the button that reads "...". Select "Code Builder" from the pop-up list, which will open a new window. Select all the text and paste the code above in there.

By the way, I noticed you're pulling data into those text boxes from a query, but you mentioned you would like to enter data into the boxes and get the totals as well?

 

[louisa]

Am i? thats right i want to enter the data in to the boxes and get a total at the end.

 

[vbaInet]

Yes, I just noticed you've set the Control Sources of each of your 7 text boxes. If you're going to use them for manual entry then leave the Control Source's blank (found under the Data tab in properties sheet).

 

[louisa]

sorry to be a pain, all of the options have ... next to it, which one is it as before update has one and after update???

 

[louisa]

I have added the code but on many occasions the price could be £17.99 and the total is being rounded up. Is there anyway i can avoid this? Many thanks for your help other than that it worked brilliantly.

 

[Brianwarnock]

Sheez what a mess, Rich almost had it right but you also need Val

Then the the controlsource of your last textbox is just
=Val(Nz([Box1],0)) + Val(Nz([Box2],0)) ...etc.

Brian

 

[vbaInet]

I see what you mean. Change this line of code:

Dim ctlTotal As Currency

Also set the Format property of your Text89 box to Currency (£).

 

[louisa]

Thank you so much vba it has worked lovely. Do you no how i would do the same for the monthly price???

 

[Rich]

Sheez what a mess, Rich almost had it right but you also need Val

Then the the controlsource of your last textbox is just
=Val(Nz([Box1],0)) + Val(Nz([Box2],0)) ...etc.

Brian

You don't need the Val function for this Bri, but then again you don't need reams of vba either

 

[Brianwarnock]

You don't need the Val function for this Bri, but then again you don't need reams of vba either

You do if the boxes are defined as Text otherwise the + acts as a concatenation, however it looks as though the Formatting is wrong, it should all be currency then no problems would arise.

Brian

 

[vbaInet]

That's true, you need the + sign. I did point out to the OP that she needs to format the boxes as Currency.

Doing it in code runs faster than the Val(Nz([],0)... option though

 

[louisa]

I set the format as currency and the code works brilliantly so thank you once i figure out how to do exactly what you advised for the monthly price then my db will be completed.

 

[vbaInet]

That would depend on which method you wish to apply? The code or Rich's/Brian's suggestions?

 

[louisa]

I would prefer the code as it worked so successfully beforehand.

 

[vbaInet]

Have a look at the code and see if you can find any pattern in relation to control names and the flow of events. Have a look at your text box names and compare it with the code. If you're still stuck after a good think, just post back

 

[louisa]

I tried that yesterday but it kept taking me back advising of an error. I placed it underneath the Setup Price code, was i right in doing that as it took me to that page automatically.

 

[vbaInet]

Post your db as it is now and I'll do it and post back.

 

[louisa]

Thank you for helping

 

[vbaInet]

There you go.

Piece of advice, to accomplish more in Access you would need to learn a bit of vba, at the very least the basics (declarations, variables, modules, functions etc). Giving you code isn't going to help if you get stuck. Google is your friend or have a look at some books.

Good luck with the rest of your db