borderAndreaVB free resources for Visual Basic developersborder

borderAndreaVB Visual Basic and VB.NET source code resources - Copyright © 1999-2007 Andrea Tincaniborder

AndreaVB | Forum | News | Downloads | Register | Help | Member List | Statistics | Search | PM | Profile

Print This Topic
Previous Topic (HELP on exporting data from excel to word)Next Topic (how to access field in excel in the program) New Topic New Poll Post Reply
AndreaVB Forum : VBA (Access, Excel, Word, ...) : Calculated Fields Solved Topic
Poster Message
bolendercm
Level: Scholar


Registered: 25-10-2005
Posts: 39

icon Calculated Fields




I have 11 fields on a form of which the eleventh field is designed to be the sum of the other ten. I have created a Sub Routine called AddHours:

Me![HoursForYear] = ((Me![StandardHours1]) + (Me![StandardHours2]) _
+ (Me![StandardHours3]) + (Me![StandardHours4]) + (Me! [StandardHours5]) + (Me![StandardHours6]) + (Me![StandardHours7]) + (Me![StandardHours8]) _ + (Me![StandardHours9]) + (Me![StandardHours10]))


I'am using an AfterUpdate routine to calculate the fields:

If IsNumeric(Me![StandardHours1]) Then
AddHours

Else
MsgBox "Enter a Valid Number"
End If


and so on through all 10 fields.

When I first used this method it worked for a little then boom.....it quit.

Its not working.............any suggestions or comments...............


____________________________
C.......................

22-03-2006 at 08:09 PM
View Profile Send Email to User Show All Posts | Quote Reply
Goran
Level: Moderator

Registered: 16-05-2002
Posts: 1681
icon Re: Calculated Fields

quote:
When I first used this method it worked for a little then boom.....it quit.

Its not working.............any suggestions or comments...............


Interesting, altough we could be needing for something more specifisk in order to help you, like is it rasing some errors, if not does it display anything in this "field". And by the way, what are this fields? Textboxes?

[Edited by Goran on 23-03-2006 at 03:27 PM GMT]

____________________________
If you find the answer helpful, please mark this topic as solved.
23-03-2006 at 02:21 PM
View Profile Send Email to User Show All Posts | Quote Reply
bolendercm
Level: Scholar


Registered: 25-10-2005
Posts: 39
icon Re: Calculated Fields

Goran,

Thanks for the reply.  In answer to your question.........nothing is displayed in the 11th field and there are no error messages.  Also, they are Textboxes.



____________________________
C.......................

23-03-2006 at 03:57 PM
View Profile Send Email to User Show All Posts | Quote Reply
GeoffS
Level: VB Lord


Registered: 29-09-2004
Posts: 536
icon Re: Calculated Fields

Hi,
Just a thought here, but are you sure that each field contains a valid number. I know you have tested in the AfterUpdate Event, but this Event fires for each one of the Textboxes individually, and then calls your routine which adds up ALL of the Textboxes without first testing that each one has a valid content.
I would suggest that you add a validation test for each one of your textboxes at the start of your "AddHours" Routine, and set the value of any invalid box to Zero (0) before you then add them all together.

Dim x As Integer
Dim dblTotal As Double

dblTotal = 0

For x = 1 to 10
If IsNull (Me.("StandardHours" & x)) Or IsNumeric (Me.("StandardHours" & x)) = False Then
(Me.("StandardHours" & x)) = 0
Else
dblTotal = dblTotal + (Me.("StandardHours" & x))
End If
Next x

Me.HoursForYear = dblTotal





____________________________
multi-tasking - the ability to hang more than one app. at the same time.

23-03-2006 at 04:59 PM
View Profile Send Email to User Show All Posts | Quote Reply
bolendercm
Level: Scholar


Registered: 25-10-2005
Posts: 39
icon Re: Calculated Fields

Thanks for the reply and example.  I solved it by using Val.

Me![HoursForYear] = (Val(Me.StandardHours1) +  (Val(Me.StandardHours2) and so on and so on.  

Set the type to number and the property to format Decimal 5,2,2.



____________________________
C.......................

29-03-2006 at 08:42 PM
View Profile Send Email to User Show All Posts | Quote Reply
AndreaVB Forum : VBA (Access, Excel, Word, ...) : Calculated Fields Solved Topic
Previous Topic (HELP on exporting data from excel to word)Next Topic (how to access field in excel in the program) New Topic New Poll Post Reply
Surf To:


Not Logged In? Username: Password: Lost your password?
Partners: Download Actual Software | Free Software Download
borderAndreaVB free resources for Visual Basic developersborder

borderAndreaVB Visual Basic and VB.NET source code resources - Copyright © 1999-2007 Andrea Tincaniborder