Totaling text boxes

[Danielle17]

I already have a post on here about this form but no one seems to no how to answer it. So I guess I'll ask a smaller question first.

I have 4 text boxes on a form. Each will have a number in them. I also have another text box on the form named Totals. How do I get it so that the numbers in the other 4 text boxes add up and go into the Totals text box? I got a code from someone...:

public sub AddTogether()
dim total as double
total = nz(me.txtLodging) + nz(Me.txtMeals) + nz(Me.txtGas) + nz(Me.txtMisc)
me.unboundTotalTextBox = total
end sub

I have it on the AfterUpdate of each of the text boxes. It does not work. If I enter 45 in each of the text boxes the Totals txt box looks like 45454545. It doesn't add them up it just puts them next to one another. Does someone see something wrong with this code. I'm getting really frustrated....I need some answers. Could someone please help?

 

[THoey]

Because your textboxes are strings. Try the VAL function:

total = nz(VAL(me.txtLodging)) + nz(VAL(Me.txtMeals)) + nz(VAL(Me.txtGas)) + nz(VAL(Me.txtMisc))

Better yet, do it right the first time and set these fields of type number...


Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[JoeMiller]

Change your calculation line to this:

[tt]
total = nz(val(me.txtLodging)) + nz(val(Me.txtMeals)) + nz(val(Me.txtGas)) + nz(val(Me.txtMisc))
[/tt]

The problem is your controls are text data type and when you add them up because they are text, access concatenates them (like using the "&" operator). Joe Miller
joe.miller@flotech.net

 

[Danielle17]

Thanks!
The new code looks like this:

Private Sub txtMeals_AfterUpdate()

Dim total As Double

total = CDbl(Nz(Me.txtLodging)) + CDbl(Nz(Me.txtMeals)) + CDbl(Nz(Me.txtGas)) + CDbl(Nz(Me.txtMisc))

Me.txtTotal = total

End Sub

It works perfect!

 

[Danielle17]

I guess my new question is does anyone know how to make changes to an existing record? A better explanation can be found either further down on this 'Form Forum' or on the 'General Discussion' forum.
Thanks!

 

[JoeMiller]

Make an update query that goes through your DB and performs the same calculation except rather than control names you'll use field names.

Joe Miller
joe.miller@flotech.net

 

[Danielle17]

Could you explain to me how to use an update query. I haven't had to do one in a while.

 

[JoeMiller]

Make a query, select the field you want. Make it an update query using Query menu. Select the field you want to modify (Total), change the "Update To" field to your calculation and add some criteria (for instance "Is Null&quot so that you only get records you want updated. Hit the big Exclamation Point and your done. MAKE BACKUPS!! before you do anything.

HTH Joe Miller
joe.miller@flotech.net

 

[Danielle17]

Thanks, but I don't think that's what I need. I need to select a certain record based on the JobID number that the user types in the text box. I then need it to find that record and put the new expense info on the form into the field for that particular record.