complex totals

[ymiller]

I'm trying to create a total field using data from other fields, but assigning different values to those values:

field1: if the value entered is 0, I want 2 to be added to the total.
field2: if the value entered is 0, I want 5 to be added to the total.

How can I do this?

Thank you for your help!

 

[dpimental]

The complexity of this total depends on how many fields you have.

If you only have 4 fields you can do it like this.
Note: Fields 1-4 data entry (numeric) fields, total = total field.

field1 = 0
field2 = 0
field3 = 10
field4 = 25

After_Update on field4

Dim f1num as Integer, f2num as Integer
If field1 = 0 Then
f1num = 2
else
f1num = field1
End If

If field2 = 0 Then
f2num = 5
else
f2num = field2
End If

total = f1num + f2num + field3 + field4

Let me know if that helps

David
Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

 

[Terpsfan]

You just have to do some simple if statements. Run them on the Before Update event of the fields. There's probably an easier way to do it but this works without having to tab to the total field.

Dim A As Integer
Dim B As Integer
Private Sub field1_BeforeUpdate(Cancel As Integer)

If field1 = 0 Then
A = 5
Else
A = 0
End If
total = A + B

End Sub


Private Sub field2_BeforeUpdate(Cancel As Integer)
If field2 = 0 Then
B = 2
Else
B = 0
End If
total = A + B
End Sub

 

[ymiller]

I have all together 10 fields that I want to add.
I tried doing the "Before Update" method and for some reason, the Total field only shows the value I set for the field I just updated.
I have 2 fields that when the value is 0 should give Total a different value and when I load the form, even though all the fields have a value of 0, I get a total of 0 as well.

Where did I go wrong?

 

[dpimental]

Why don't you paste the code into a reply so that we can see what you've entered.

David Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

 

[ymiller]

Here's my code:

Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim E As Integer
Dim F As Integer
Dim G As Integer
Dim H As Integer
Dim I As Integer
Dim J As Integer

Private Sub txtAA_BeforeUpdate(Cancel As Integer)
If txtAA = 1 Then
C = 5
Else
C = 0
End If
txtTotal = A + B + C + D + E + F + G + H + I + J
End Sub

Private Sub txtArrest_BeforeUpdate(Cancel As Integer)
If txtArrest = 1 Then
J = 2
Else
J = 0
End If
txtTotal = A + B + C + D + E + F + G + H + I + J
End Sub

Private Sub txtDT_BeforeUpdate(Cancel As Integer)
If txtDT = 1 Then
G = 2
Else
G = 0
End If
txtTotal = A + B + C + D + E + F + G + H + I + J
End Sub

Private Sub txtFriend_BeforeUpdate(Cancel As Integer)
If txtFriend = 0 Then
B = 2
Else
B = 0
End If
txtTotal = A + B + C + D + E + F + G + H + I + J
End Sub

Private Sub txtHelp_BeforeUpdate(Cancel As Integer)
If txtHelp = 1 Then
H = 5
Else
H = 0
End If
txtTotal = A + B + C + D + E + F + G + H + I + J
End Sub

Private Sub txtHosp_BeforeUpdate(Cancel As Integer)
If txtHosp = 1 Then
I = 5
Else
I = 0
End If
txtTotal = A + B + C + D + E + F + G + H + I + J
End Sub

Private Sub txtLost_BeforeUpdate(Cancel As Integer)
If txtLost = 1 Then
D = 2
Else
D = 0
End If
txtTotal = A + B + C + D + E + F + G + H + I + J
End Sub

Private Sub txtNeglect_BeforeUpdate(Cancel As Integer)
If txtNeglect = 1 Then
F = 2
Else
F = 0
End If
txtTotal = A + B + C + D + E + F + G + H + I + J
End Sub

Private Sub txtTrouble_BeforeUpdate(Cancel As Integer)
If txtTrouble = 1 Then
E = 2
Else
E = 0
End If
txtTotal = A + B + C + D + E + F + G + H + I + J
End Sub

Private Sub txtYou_BeforeUpdate(Cancel As Integer)
If txtYou = 0 Then
A = 2
Else
A = 0
End If
txtTotal = A + B + C + D + E + F + G + H + I + J
End Sub

 

[dpimental]

First of all, it doesn't sound like you are stopping anyone from entering anything, so try doing the variable setting after_update.

Second, only set the variable value (A,B,C,ect) at this time. Don't do the totatl until you get to the last field, then try doing the total.

Let us know how that goes.

David

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

 

[ymiller]

It didn't work.
I then tried after setting the value for a, b, c, etc. to add the variables up to that point and that doesn't work either.

 

[ymiller]

I finally got it to work!! What I did was I made a text box to contain the value I want to be totaled for each field (using an iif statement), and then in the total field, I added up those fields.

It's probably the hard way... but at least I got it to work!

 

[MichaelRed]

the previous 'versions' all had the temp variables declared within the procedure, thus all were 'initalized' to Zero in/on each individual routine.

To make the earlier routine(s) work, you need to move/place the declarations where they remain in scope throughout the process.

On the other hand, after doing this, you have some un-necessary calculation. Since the declared variables all numeric types, they will be initalized to Zero, so there is little point to the 'Else' part of your routines above, unless someone would be changing the text box contents from the 'If' condition value to zero.

You also do not provide a context for the process. If this is a 'bound' form, the text boxes are initalized from the recordsource bound fields, including the txtTotal (results), only changes to the source text boxes will trigger the before update event. So, if the intent is to generate the value in txtTotal so that it can be 'saved' in the db, it won't happen unless/until some change is made to one of the source terms. That, however, implies that you are saving a calculated value, which is generally not a good thing, and should be discouraged, this -in turn- suggests that the calculation and display of txtTotal should occur in the form current event.

With so many potential issues surrounding this calculation, I think you must be quite new to programming in general and database work in particular. If that is the case, you should get some (serious) help in the concepts behind relational data base operations before proceeding. I don't mean to discourage your efforts, but retrofitting programming is generally exponentially more difficult (and expensive) than propper initial design, thus it may (probably is) cheaper to delay implementation until a propper (correct?) design than to implement a bad design and then 'fix' it with re-design.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places