Results 1 to 15 of 15
  1. #1
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168

    Negative Value format based on option box

    Working with entering transaction data into a checkbook ledger.


    I have a form that resembles a Check (like from a checkbook) but it also has an option box to select weather it is a Payment or a deposit.
    Now based on the value selected I would like the amount field to automatically be formatted to a positive or negative value.

    note: getting it to just select the default, or starting value to be pos or neg would work, but I would like it hard coded so that if payment is selected it would automatically make any value entered a negative value

    Thanks for any help

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Can't you use the minus "-"?

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    In the after update event of the amount textbox:

    Code:
    If Me.OptionBox = Whatever Then
      Me.AmountTextbox = -1 * Abs(Me.AmountTextbox)
    End If
    The Abs() protects you from the user entering a - and changing the value to a +.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168
    Yes, but if someone forgets to enter a transaction with the “-“ minus symbol to make it negative it will mess up the books, so I’m trying to force the issue so mistakes can be avoided

  5. #5
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168
    Thanks pbaldy, i think thats what Im after, ill plug it in and see if I can get it to work.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem, post back if you get stuck.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168
    Alright,

    So I went with entering the code for the on click event for the TransAmount field.

    and I think the problem I am having is I dont really understand how the option group thing works

    I have the option group that shows

    TYPE
    o Deposit
    o Payment

    Type = Lablel37
    o (next to deposit) = Option39
    Deposit = Label40
    o (next to Payment) = Option41
    Payment = Label42

    Deposit option Value =1
    Payment option Value = 2


    The Code I wend with is

    Code:
    Private Sub TransAmount_Click()
    
    
    If Me.Option41 = 2 Then
      Me.TransAmount = -1 * Abs(Me.TransAmount)
    End If
    
    
    End Sub

    I have tried various
    If Me.Option41 = True
    If Me.Option41 = Payment
    If Me.Option41 = 1


    but I keep getting the
    Run Time error 2427
    You entered an expression that has no value

    any thoughts?

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    On what line? Also, I mentioned the after update event, not the click event. You may need the Nz() function to handle an empty textbox.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168
    Ha! Yes you did say after update, vicoden and vba don’t mix well (shoulder surgery)
    Ill try it again in the morning

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Ouch! Okay, let me know.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    And get well soon!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168
    Thanks Pbaldy, sleep is the most difficult part of it, getting only a couple of hours here and there. and unfortunately lack of sleep, even more so than Vicodin, really cuts down on cognitive reasoning abilities.

    anyway I moved it over to the after update event and still keep getting hung up with the same error

    Run_Time Error 2427 "You entered an expression that has no value."


    Code:
    Private Sub TransAmount_AfterUpdate()
    If Nz(Me.Option41 = 2) Then               <----error highlights this line
      Me.TransAmount = -1 * Abs(Me.TransAmount)
    End If
    End Sub

    I tried fixing with Nz and that dosn't seem to be the problem (or at least not yet)
    would it be better/easier if I changed the Option group to a comboBox for making the selection?

    Thanks for helping me figure this one out

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Oh sorry, you have to test the frame, not an individual option. The frame has the value of whichever option is selected.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #14
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168
    Ahhh! interesting, it makes sense (now) that we are testing the frame for the options inside of it. I had thought the frame was just a frame and inconsequential to the operation of it.

    Works great now, Thanks

  15. #15
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Requery sub form based on combo box option
    By enginerdUNH in forum Programming
    Replies: 2
    Last Post: 02-26-2018, 04:39 PM
  2. Replies: 3
    Last Post: 07-22-2017, 12:03 PM
  3. Choosing Criteria based on option Box Value
    By todmac in forum Queries
    Replies: 3
    Last Post: 10-02-2015, 11:51 AM
  4. Yes or No based on Positive/Negative
    By kenkencoco in forum Programming
    Replies: 1
    Last Post: 07-17-2013, 04:38 PM
  5. Replies: 3
    Last Post: 06-01-2013, 10:56 PM

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