Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    c.d.power's Avatar
    c.d.power is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Location
    Florida
    Posts
    44

    Designating a Value in a Quantity Field as an Inventory Addition OR Subtraction

    Good morning!



    I have a simple form to track the issuance of one controlled item, but I would also like for it to track additions to inventory of that item.

    The simple answer is to enter additions to inventory as positive numbers and subtractions as negative numbers.

    But I would prefer for the user to be able to check a box or boxes which would change that quantity to a negative or positive number.

    Then again, I don't want to be hung up on the negative/positive numbers if there is a more effecient way to do it.

    Thank you in advance for any help.

  2. #2
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    I think the figure for how many you have in stock should be a calculation (not one number updated). It seems you are thinking along these lines also. I wouldn't enter + or - I would have a field for "in/out" call it whatever you like.

    on the table would be a record of the transaction. so.. if 2 items were going "out" it would be 2 in the quantity field and "out" in the in/out field.

    you can then separate the ins from the outs in a query and calculate your stock levels. Hopefully that makes sense.

    Andy.

  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
    I may be lazy, but I would use positive/negative numbers. It would make the calculation of balance at any given point a simple Sum(). With a separate field, you have to add logic to determine whether to add/subtract each value. It would certainly work that way, just not how I'd do it personally. You could use a checkbox to control which way a value gets saved (+/-).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    c.d.power's Avatar
    c.d.power is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Location
    Florida
    Posts
    44
    Thanks for the replies. I like the idea of using the simple Sum() function. It's actually what I already have it set up for, but I don't have the checkbox that saves the value as positive or negative. How would I go about doing that?

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Presuming you have a bound form, you can make the field bound to the table hidden and let the user enter the amount in an unbound textbox. It the after update events of that textbox and the checkbox something like:

    Code:
    If Me.CheckboxName = True Then
      Me.BoundTextbox = Abs(Me.UnboundTextbox)
    Else
      Me.BoundTextbox = Abs(Me.UnboundTextbox) * -1
    End If
    Totally untested air code warning, no warranties expressed or implied. You'd probably want to make sure there's an amount present first. Since you'd have the code in 2 places, I'd probably create a form-level function and call it from both.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    c.d.power's Avatar
    c.d.power is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Location
    Florida
    Posts
    44
    Thank you for the suggestion. Tried to implement this but I'm running into trouble. Would I add that code to the Checkbox or to another object? I tried to add to the Save button but no luck.

    I'm also not sure what you mean by after update events and having the code in two places.

    I do understand the ability to add Event Procedures/Macros associated with After Update events, but not sure how or what object that would apply to in this scenario.

    For referrence, one of the errors I seem to be running into is that the code is changing all of the quantities in my table to the same quantity.

    I apologize in advance as these issues most like have fairly simple solutions. If it's easier, I am happy to upload the Access file here.

    Thanks in advance.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'd be using the after update event of the textbox they type a value into and the checkbox they determine +/-. Can you attach the db here if you're still having trouble? If all values are affected, that sounds like an unbound textbox.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    c.d.power's Avatar
    c.d.power is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Location
    Florida
    Posts
    44
    Thanks for your help. I've attached the zipped db file so you can have a look. I have the "Home Page" form set to open automatically, so you may want to open in design view.

    The form in question is named "frmCoinLog".

    Also, if you're willing and curious, feel free to have a look at the database as a whole and let me know if there's anything I can do to make it more effecient or any best practices that would help me out. I wouldn't expect you to make any changes as I'm trying to learn a viable process for future projects, but any suggestions or feedback are greatly appreciated.

    Thank you for your time!
    Attached Files Attached Files

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I don't see that code anywhere, or the unbound textbox. Does this version not contain the attempt to implement this?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    c.d.power's Avatar
    c.d.power is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Location
    Florida
    Posts
    44
    I’m sorry! I completely forgot I made a copy of the form but deleted it after it didn’t work. I will resend after lunch.

  11. #11
    c.d.power's Avatar
    c.d.power is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Location
    Florida
    Posts
    44
    Sorry about that. Here is a db file with my attempt at cracking the code.

    Thanks in advance.
    Attached Files Attached Files

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You're referring to different controls, should be the same one:

    Code:
    If Me.Coin_Log_Inventory = True Then
        Me.Coin_Log_Quantity = Abs(Me.txtUnboundCoinLog)
    Else
        Me.Coin_Log_Inventory = Abs(Me.txtUnboundCoinLog) * -1
    End If
    I haven't used the split form, but I had to change the cycle property to get the textbox update event to work. I have this in both, though as I mentioned I create a little function:

    Code:
    If Me.Coin_Log_Inventory = True Then
        Me.Coin_Log_Quantity = Abs(Me.txtUnboundCoinLog)
    Else
        Me.Coin_Log_Quantity = Abs(Me.txtUnboundCoinLog) * -1
    End If
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    c.d.power's Avatar
    c.d.power is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Location
    Florida
    Posts
    44
    Thanks for your help. I've added the code and everything appears to be working. The issue I'm having now is that when I choose to run a report, my report is blank and doesn't select any of the records.

    You may recall that you helped me with the code for my "Run Report" command/button, but here is exactly what I have in VBA for the command:

    Private Sub cmdCoinRunReport_Click()
    DoCmd.OpenReport "rptCoinLog", acViewReport, , Me.Filter
    End Sub

    If I open the report manually, it does display the records and the Access Totals is correct which is what I was ultimately going for. Any thoughts on why the Me.Filter function isn't working now that I've added the code for the +/- count?

    Thanks in advance.

  14. #14
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Add this:

    Debug.Print Me.Filter

    and see what the filter is in the Immediate window. If needed:

    http://www.baldyweb.com/ImmediateWindow.htm


    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  15. #15
    c.d.power's Avatar
    c.d.power is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Location
    Florida
    Posts
    44
    That did it. Thanks for your help!

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Field that can handle addition
    By albzn in forum Forms
    Replies: 15
    Last Post: 10-06-2015, 01:35 PM
  2. Replies: 9
    Last Post: 07-21-2014, 11:57 AM
  3. quary addition subtraction
    By agyapong isaac in forum Queries
    Replies: 2
    Last Post: 12-28-2012, 12:07 PM
  4. Replies: 1
    Last Post: 12-28-2011, 11:37 AM
  5. Replies: 38
    Last Post: 05-21-2010, 11:56 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