Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    317
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Update Inventory [In Stock] Amount (Access 2003/SP

    How can I update the 'In Stock' amount in a 'Products' table when I am entering orders. For example, 'Products' could have 50 apples 'in stock'. I enter an order of 10 apples on an 'Orders' form and the 'in stock' would automatically be updated to 40. Is this done through an update query?

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 15 Times in 15 Posts

    Re: Update Inventory [In Stock] Amount (Access 2003/SP

    You can either run an update query or use DAO or ADO code to open a recordset on the Products table, locate the relevant record and edit it.

    You must be very careful how you do this. For example, you don't want to update the Products table immediately when the user enters a number in the Orders form - the user might have made a mistake, and have to change the number. You should also provide a mechanism for changing the size of the order after it has been confirmed - the stock should then only be changed by the difference between the old and new order size.

    If I were to design an inventory database, I'd use unbound forms to have complete control over the way data are stored.

  4. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    317
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Update Inventory [In Stock] Amount (Access 200

    OK, that sounds like standing on the seashore and seeing a goal as a speck in the ocean.

    I'm not sure what you mean by an unbound form. My order entry is made on a subform. You can't have a subform be an unbound form, can you?

  5. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 15 Times in 15 Posts

    Re: Update Inventory [In Stock] Amount (Access 200

    An unbound form is a form whose Record Source is blank. This means that you will need code to load existing data into the form, and also to save the entered data into a table (or tables).

    A subform can be unbound, but it cannot be a continuous or datasheet form.

  6. #5
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    317
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Update Inventory [In Stock] Amount (Access 200

    I'm 'trying' to design a database for a charity group that works with used and new clothing. It needs to be something very simple, the users aren't highly computer literate. "I don't seem to be either". That's one thing they want is an updated Inventory status. I understand the need to be cautious about changing the order. Should I run an update query based on order numbers at the end of the day? ADO and DAO are not my specialty, although if I had help I'd be willing to give it a try.

  7. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 15 Times in 15 Posts

    Re: Update Inventory [In Stock] Amount (Access 200

    An update query is probably the easiest solution.

  8. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,869
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Inventory [In Stock] Amount (Access 200

    If your still in the design stage you may want to Check out:

    <post#=362044>post 362044</post#>
    <post#=526954>post 526954</post#>

    HTH, John

  9. #8
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    317
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Update Inventory [In Stock] Amount (Access 200

    Thanks for that tip. I'll see if I can wade through that.

  10. #9
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    317
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Update Inventory [In Stock] Amount (Access 200

    Ok. I am trying to wade through this code and I think I will like it, but I'm not sure I understand it all. Is this supposed to create a table, a form, or where do I see the calculated values?

  11. #10
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,325
    Thanks
    1
    Thanked 12 Times in 12 Posts

    Re: Update Inventory [In Stock] Amount (Access 2003/SP

    You might want to reconsider trying to store the current inventory level. It is frought with potential problems; that is, there is always uncertainity as to whether you have properly updated the level in every situation that might occur. Usually a better solution is to derive the current inventory level by using queries of inventory ins and outs.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  12. #11
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,869
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Inventory [In Stock] Amount (Access 200

    Find attached InvCtrl.zip, contains

    Calculate Stock by Tom Pugh code and
    Inventory Control: Quantity on Hand by Allen Brown code

    ----------------------------------------------------------------------
    Tom Pugh Code: Google "Tom Pugh" Inventory bluvision

    Open frmPart, Open Inventory, Received and Invoice and study the inventory detail records

    Enter Part Number 123 or 234 and click Calculate Stock

    Study the code behind Calculate Stock

    -----------------------------------------------------------------------
    Allen Brown Code:

    Open Module mod_InventoryControl-Quantity_On_Hand, run and study code

    HTH, John

  13. #12
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    317
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Update Inventory [In Stock] Amount (Access 200

    I just got back to work after the weekend. I'll check into that. Thanks a lot.

Posting Permissions

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