Results 1 to 9 of 9
  1. #1
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670

    Perform Calculation On Bound Field

    I have a form where I need this equation to be in play textbox1 = textbox1 - textbox2. Now a few caveats on this are: 1) Both text boxes are bound 2) textbox2 has the potential to contain a NULL value (no not zero, but null)

    How can I perform this calculation to update textbox1 IF textbox2 contains a value with both fields being bound?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you already did. Set the ControlSource of the unbound text box = textbox1 - textbox2

    or if you want zero:
    =NZ(textbox1) - NZ(textbox2)

  3. #3
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    Quote Originally Posted by ranman256 View Post
    you already did. Set the ControlSource of the unbound text box = textbox1 - textbox2

    or if you want zero:
    =NZ(textbox1) - NZ(textbox2)
    I need this value to be stored in the database, and if it is unbound how can I store it?

  4. #4
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    First, you posted

    textbox1 = textbox1 - textbox2

    which doesn't seem to make a lot of sense...you want textbox1 to equal itself minus text2?

    Secondly, you very seldom store calculated values...you simply re-calculate them, as needed, as ranman256 suggested.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    Quote Originally Posted by Missinglinq View Post
    First, you posted

    textbox1 = textbox1 - textbox2

    which doesn't seem to make a lot of sense...you want textbox1 to equal itself minus text2?

    Secondly, you very seldom store calculated values...you simply re-calculate them, as needed, as ranman256 suggested.

    Linq ;0)>
    Yes, I want textbox1 to equal itself minus textbox2. I am needing the value to be calculated on the form so the user can review it one last time before clicking the submit button. I then need it stored in table for future reference. I am very clear this is bad practice, but I have no other option at this point.

  6. #6
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    I assume if the user decides not to save the Record, as it appears, with the calculation, you want the value in Textbox1 to return to its previously entered value...not its calculated value...for another try by the user? If so, try this:

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    Dim varTB1 As Integer
    
    varTB1 = Me.Textbox1
     
     Me.Textbox1 = Nz(Me.Textbox1) - Nz(Me.Textbox2)
     
     If MsgBox("Are You Ready To Save This Record?", vbQuestion + vbYesNo + vbDefaultButton1, "Save the Record ???") = vbNo Then
       Me.Undo
       Cancel = True
       Me.Textbox1 = varTB1
     End If
     
    End Sub

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Make a field. This still works.

  8. #8
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Two problems here -

    First, you can't have a textbox with an expression as its control source AND bind it to a table field - it has to be one or the other.

    Second, if you use an expression as a textbox control source, and you use the same textbox in that expression, you will get an error in that control when you open the form.

    This makes sense - if you allow a control to reference itself, you create a circular reference which (in theory) would cause an infinite evaluation loop.

    Your best option is to put an unbound textbox on the form, with =[textbox1] - [textbox2] as its control source.

  9. #9
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    The OP said noting about using an expression as the Control Source...that was ranman256's idea.

    His question was

    Quote Originally Posted by jo15765 View Post

    ...How can I perform this calculation to update textbox1 IF textbox2 contains a value with both fields being bound...

    ...I am needing the value to be calculated on the form so the user can review it one last time before clicking the submit button...
    and the code in Post #6, I believe, does just that.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 29
    Last Post: 06-05-2014, 09:34 AM
  2. Replies: 2
    Last Post: 02-02-2014, 07:19 PM
  3. Tricky calculation to perform
    By leeli67 in forum Access
    Replies: 122
    Last Post: 04-15-2012, 05:06 PM
  4. Replies: 1
    Last Post: 08-11-2011, 12:48 PM
  5. SQL expression to perform a calculation
    By springboardjg in forum Queries
    Replies: 1
    Last Post: 05-20-2011, 06:57 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums