×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Automatic update of field from data in other fields

Automatic update of field from data in other fields

Automatic update of field from data in other fields

(OP)
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?

RE: Automatic update of field from data in other fields

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

RE: Automatic update of field from data in other fields

(OP)
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

RE: Automatic update of field from data in other fields

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

RE: Automatic update of field from data in other fields

(OP)
jfhewitt,

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

Thanks.

Lou Marles

RE: Automatic update of field from data in other fields

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

RE: Automatic update of field from data in other fields

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]

RE: Automatic update of field from data in other fields

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")

 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

RE: Automatic update of field from data in other fields

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close