Access Forms: Calculated field updates after each entry. HELP!!

[memberley]

I am probably so annoyed that it must be easy, but I just can't figure it out!! I am trying to create a data entry form and want a field in there to automatically calculate the total of what is entered in the 13 fields that would have data entry in them. Sounds like a reasonably easy thing to be able to do, but I don't know why it isn't?? I just spent $90 on books for Access, and VB6, and neither of them can help me. I have the fields based on the table, as it did not work with the query. When I based the fields on the query, I could not add more records, but my total field in the query did work. I put in a text box field and had the expression to equal the query, but it displayed &name. I assume VB code would be the way to go, if someone could walk me through this I would be soo sooo happy!!!

THE GOAL: To have a total field that automatically updates after each entry of data on the form.

[mgiambrone]

Do you want the the total to change automatically when you change one of the fields? Like when you change an entry on a spreadsheet? Or do you want to click on a button and have the total update based upon the values of the fields.

Let me know how you want to handle it and I will supply some sample code for you.

[mgiambrone]

Create a form with one text box and one label on it
set the textbox name to txtEntries and set the label name to
lblEntries
Put another lablel down in the righgt hand corner of the form
and name it lblTotal

Set the Index property of the textbox to 0
and set the Index property of the label to 0
Paste the code below into your form.

This is what it sounds like you are looking for. Let me know

Option Explicit
Private cTotal As Currency 'Used to hold total
 
Private Sub Form_Load()
Dim I As Integer
 
lblEntries(0).Caption = "Entry Number 1" 'Name the first Label
'**************Set up a Loop to create control arrrays********
For I = 1 To 12
Load txtEntries(I) 'Sets up a control array of 12 more Text Boxes
Load lblEntries(I) 'Sets up a control array of 12 more labels
 
'*************Line Everything up under each under*****************
txtEntries(I).Top = txtEntries(I - 1).Height + txtEntries(I - 1).Top
lblEntries(I).Top = lblEntries(I - 1).Height + lblEntries(I - 1).Top
txtEntries(I).Left = txtEntries(I - 1).Left
lblEntries(I).Left = lblEntries(I - 1).Left
txtEntries(I).Visible = True
lblEntries(I).Visible = True
lblEntries(I).Caption = "Entry Number " & I+1 'Changes Label Caption
Next
 
End Sub
 
Private Sub txtEntries_GotFocus(Index As Integer)
txtEntries(Index).SelStart = 0
txtEntries(Index).SelLength = Len(txtEntries(Index))
End Sub
 
Private Sub txtEntries_Validate(Index As Integer, Cancel As Boolean)
Dim I As Integer
cTotal = 0 'Reset Total
For I = 0 To 12 'Loop through 13 text boxes
  'If text box is empty then assign 0 to it and format it
  If txtEntries(I) = "" Then
  txtEntries(I) = 0
  txtEntries(I) = Format(txtEntries(I), "Currency")
  Else
  txtEntries(I) = Format(txtEntries(I), "Currency")
  
 
  End If
Next
For I = 0 To 12
cTotal = cTotal + CCur(txtEntries(I)) 'Calculate Total
lblTotal.Caption = Format(cTotal, "Currency") 'Place Total in Label
Next
End Sub