Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2005
    Posts
    21

    Question Answered: Userform updating but not Access column

    I have a user form where I have three fields for numerical values. I want to subtract field 2 from from field 1 and show results in field 3. I use the expression builder and do the following in the control source of field 3:

    Code:
    [txtFallHrsCompleted] - [txtFallHrsUsed]
    The value shows up in the User form in the txtFallHrsBalance textbox but will not show up in the database. So I read that it will not show up in the database if you use a calculated value. That you have to use VBA. So I did the following in the Afterupdate field using the Code builder:


    Code:
    txtFallHrsBalance.Text = txtFallHrsCompleted.Text - txtFallHrsUsed.Text
    Now I don't get anything in the User Form txtFallHrsBlance and still don't get anything in the database. What am I doing wrong?

  2. Best Answer
    Posted by Sinndho

    "The Text property can only be used when the control has the focus. Instead of using:
    Code:
    txtFallHrsBalance.Text = txtFallHrsCompleted.Text - txtFallHrsUsed.Text
    Use:
    Code:
    txtFallHrsBalance.Value = txtFallHrsCompleted.Value - txtFallHrsUsed.Value
    "


  3. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    The which AfterUpdate event? Form_AfterUpdate? The AfterUpdate event of one of the Textboxes?

    Not that it really matters, because it's very, very seldom appropriate to store a Calculated Field in a Table! Calculated Fields should simply be re-calculated, in Reports, other Forms, etc., as needed.

    Bottom line...you should go ahead and use

    Code:
    =[txtFallHrsCompleted] - [txtFallHrsUsed]


    in the Control Source.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #3
    Join Date
    Dec 2005
    Posts
    21
    Quote Originally Posted by Missinglinq View Post
    The which AfterUpdate event? Form_AfterUpdate? The AfterUpdate event of one of the Textboxes?

    Not that it really matters, because it's very, very seldom appropriate to store a Calculated Field in a Table! Calculated Fields should simply be re-calculated, in Reports, other Forms, etc., as needed.

    Bottom line...you should go ahead and use

    Code:
    =[txtFallHrsCompleted] - [txtFallHrsUsed]


    in the Control Source.

    Linq ;0)>
    Thank you. I have read that in other places also. I was trying to update the database because I then export the data from the database to an EXCEL spreadsheet for other people to use. Should I try and do a report instead? Will it take all the data that is in the user form? Thank you for your help.

  5. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The Text property can only be used when the control has the focus. Instead of using:
    Code:
    txtFallHrsBalance.Text = txtFallHrsCompleted.Text - txtFallHrsUsed.Text
    Use:
    Code:
    txtFallHrsBalance.Value = txtFallHrsCompleted.Value - txtFallHrsUsed.Value
    Have a nice day!

  6. #5
    Join Date
    Dec 2005
    Posts
    21
    Thank you to both of you for your help. The .Value did the trick Sinndho. Both of you have a great day.

  7. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  8. #7
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    And FYI, you can actually drop the .Value, as Value is the Default Property for Textboxes, Comboboxes, basically any Control that can contain Data.

    So

    txtFallHrsUsed

    and

    txtFallHrsUsed.Value

    both mean the same, in VBA code.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  9. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Missinglinq,

    Be carful when relying on the default property, though: it does not always work.
    This adds the control object txtName to the collection (no Set instruction involved):
    Code:
    Dim colControls As Collection
    Set colControls = New Collection
    colControls.Add Me.txtName
    For adding the value of the control, you must use:
    Code:
    Dim colControls As Collection
    Set colControls = New Collection
    colControls.Add Me.txtName.Value
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •