Automatic update of field from data in other fields

[lmarles]

I have a table with seven (7) fields, A1, A2, ATOT, B1, B2, BTOT, TOTAL (all are data type Number). I have a form with controls (Text Boxes) for each field. I have set the form up so that when I enter data in A1 and/or A2, ATOT automatically gets updated. I did this, for A1, and similarily for A2, through the control's properties Event/After Update with the following Event Procedure:
Private Sub A1_AfterUpdate()
Me!ATOT = Me!A1 + Me!A2
End Sub
The controls for B1 and B2 are set up in the same way to update BTOT.
This works fine. I can enter data in A1, A2, B1, or B2, and ATOT and BTOT will update accordingly.
I want TOTAL to be the total of ATOT and BTOT. However, I have not been able to do this. I have tried the 'AfterUpdate' event and also the 'OnChange' event in the controls for ATOT and BTOT and I can't get TOTAL to update.
How can I get TOTAL to automatically update with the total of ATOT and BTOT?

 

[Psychpt]

just make the box =sum(atot + btot) in the control source

 

[lmarles]

I tried that with no luck.

The only thing that appears in the control for TOTAL is 'Name?'.

This is Access97 I should mention. I want the record field in the table to update as well as have the total appear on the form.

Lou Marles

 

[jfhewitt]

You may want to use the .AddNew and .UpDate method in VBA to update your table with the combined data.

 

[lmarles]

jfhewitt,

Can you give me some details on how to do that?

Thanks.

Lou Marles

 

[braindead2]

Normarize your database. There is no reason to have the fields ATOT, BTOT or TOTAL in your table.
each are calculated fields
to have it work on a form you need 3 unbound text boxes
Text1 control source =A1 + B1
Text2 control source =B1 + B2
Text3 Control source either one of the 2 works
=a1+a2+b1+b2
or
=[text1] + [text2]
use this same rational in queries, forms or reports

 

[braindead2]

Sorry for error in first post should read
Text1 control source =A1 + A2
Text2 control source =B1 + B2
Text3 Control source either one of the 2 works
=a1+a2+b1+b2
or
=[text1] + [text2]

 

[jfhewitt]

Imarles: Here is a memo I sent my daughter who needed to provide a means on the form to add permanent records to a table for her dropdown list box. It may give you some idea of the AddNew method. Good luck.

This routine will let you add data to a table on which a combo box is based, on the fly. For example, the "bates" table contains numbers and a name. To add a new name to the table while entering data on the form, this routine is attached to a command button (command5) which opens a dialog box for entry of the new name, then adds the new name to the existing table "bates" in the field "name". The combo box must be based upon the table "bates", and "name" must be selected as the listbox field. The same routine can be used to add data to any table in the selected database and recordset.

Private Sub Command5_Click()

Dim varName As Variant 'variable must be declared
varName = InputBox("Enter New Name&quot

Dim db As Database 'The "AddNew" function works with a recordset, so 'these
Dim rst As Recordset 'settings are required to make it work.
Set db = CurrentDb
Set rst = db.OpenRecordset("bates", dbOpenDynaset)
With rst
.AddNew
!name = varName
.Update
Me.cboList.Requery 'may not be essential sine this is a table not a query
End With 'probably not a bad idea to include just in case to use
End Sub 'a query rather than a table

 

[lmarles]

Thanks everyone for your suggestions. Here is what I did to get it to work. I finally clued in that I could update TOTAL in the same way as I was updating ATOT and BTOT. I was trying to update TOTAL using Control Events (AfterChange, AfterUpdate, etc.) for ATOT and BTOT. But since ATOT and BTOT were not in the tab sequence and never received Focus I could not get TOTAL updated. So I expanded the Event Procedures for A1, A2, B1, and B2 to update TOTAL as well as ATOT and BTOT.

Example:

Event Procedure:
Private Sub A1_AfterUpdate()
Me!ATOT = Me!A1 + Me!A2
Me!TOTAL = Me!ATOT + Me!BTOT
End Sub

Thanks again everyone.

Lou Marles
lmarles@trentu.ca