Addition problem

[JaredNJames]

hi, i have two subforms on a main form, both have a total field on.

i have set a field on the main form called subtotal so that its control source is =[frm_invoice subform].[Form]![Total]+[frm_service final subform].[Form]![Total]

the problem is, when i start typing on the main form, the subtotal box goes from showing £0.00 to being blank. if i update both subforms it gives a total as appropriate. but if i only update one of them as sometimes necessary, it doesnt update the subtotal box.

i have set both total boxes with default values of 0.

help please

jared james

 

[JaredNJames]

come on guys, i need help on this one desperately.

jjames

 

[Newman]

You won't get help faster by demanding it. Don't forget that we help you when we know the answer and that we do that for free in our spare time. And today is saturday, 90% of the users are away with their family.

If you could post a copy of that form, like I asked you to in another thread, maybe I could help you.

 

[ajetrumpet]

Jared,

Sounds like you are needing a lot of different information about Access very quickly. Some of these issues that you've asked about are not very complex in terms of database programming. Why not invest in a book or two on Access, or even SQL?? I bought a book on SQL a while back so I could learn how to write the language in general. If you are going to be using programs like Access a lot, I recommend buying some material on it. Search engines, forums, and even Microsoft's support site are all good tools, but they don't even come close to the organized material you would get in a program-specific book written by a professional.

 

[JaredNJames]

hi, i went to staples the other day to see what the had, but there were none there and the sales guy looked at me stupid. the nearest PC world is 35 miles away and i dont particularly want to use the net as i want to read it to see if its what i want.

can you let me know where to get them.

i understand some of the stuff is fairly simple, however, the post above and some others are problems i have read about in other forums and they have no answers. that is why i ask them.

jjames

 

[Oldsoftboss]

look up IsNumeric in vba help

 

[MarkK]

Seems to me that your totals using a calculation like this are going to be pretty fragile. I assume your subform total fields are sums on a datasheet or continuous form.
But if a subform has no records this yields an #Error result, which when added to the total from a different subform, has to fail. Not sure how a datasheet Sum() handles Nulls, etc...
What I'd be tempted to do is set the control source of the control on the main form to calculate its totals independently. Maybe use DSum(), which might look something like...

=DSum("LineTotal", "tInvoiceDetail", "InvoiceNum = " & Me.InvoiceNum) + DSum("ServiceTotal", "tServiceFinal", "CustomerID = " & Me.CustomerID)

With this approach, both your subforms can crash and burn and your main form still shows something meaningful. It conforms too to the general rule that you minimize the dependencies that objects have on each other. Get each one to handle it's own data and you can reuse them more often in more places.

 

[JaredNJames]

hi, thats what im looking for, but could you just explain the dsum bit again.

im not a wizz with access but i can do a bit. ie never used dsum before and not sure what each item is int he formula.

jjames

 

[RexesOperator]

hi, i went to staples the other day to see what the had, but there were none there and the sales guy looked at me stupid. the nearest PC world is 35 miles away and i dont particularly want to use the net as i want to read it to see if its what i want.

can you let me know where to get them.

i understand some of the stuff is fairly simple, however, the post above and some others are problems i have read about in other forums and they have no answers. that is why i ask them.

jjames

Try Chapters on-line. I've never found any really useful texts at Staples - they're more into hardware.

 

[MarkK]

I fabricated the identifiers in the DSum() calls. They're syntactically correct but you'll need to replace those names with the ones that actually exist in your DB.
For details on using DSum, search Access VBA help or this forum. Searching "DSum" on this forum returns 411 threads.
Cheers,

 

[JaredNJames]

nice one mate,

start searching now finish in about a week.

cheers

 

[MarkK]

Ya, well, thanks for the opportunity to cater to your every need.

 

[ajetrumpet]

I don't know where you live James, but I'm in the midwest. Lots of "Barnes and Noble" stores here. I buy a lot of my books there. As for computer stores, I don't know if they would be good or not. I buy books that offer superior explanations of the content, not just directional or technical methods. For me, learning "why" you do something is just as important as learning "how" to do something. Very rarely do you find someone who can offer great answers to both of these questions, usually its one or the other. As far as programming is concerned, you'll probably find a lot of professionals in the field who can explain the "how", but not the "why". Sometimes though, its just to taxing on the brain, and sometimes its not even relevant.

Too bad your working with a subject matter that takes a lot of knowledge, and sometimes insanity, to master...
Good luck with it though.

 

[JaredNJames]

cant say ive ever seen a barnes and noble store. i agree with you, i like to know the why as well, i can explain in great detail many topics concerning physics and geography and mathematics, subjects i enjoy very much. however, in a field such as this where the "professionals i talk to only know the how and not the why", i cant be expected to pick up the knowledge i require from them.

i seem to have a thing with getting stuck, asking a question here and then somehow managing to solve it myself.

i live in south wales.

jared james

 

[Rich]

Have you tried the Access help file or the Microsoft knowledge base, both have examples?

 

[stopher]

cant say ive ever seen a barnes and noble store. ....i live in south wales.

Waterstones do a pretty good range of computing (including Access & VBA) books. There are quite a few in south wales. If you search these forums for books you'll get some good recommendations.
Stopher