Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Location
    St. John's, NFLD
    Posts
    18

    Question Unanswered: help with adding/subtracting quantity from an inventory DB

    Hello to all,

    I'e searched the web but have not found any great resources for Inventory databases using Access.

    The basic principle I'm trying to accomplish is this:

    I have the following:

    Table name: Consumables
    Fields: consumableid, itemid, quantity, quantityused

    On my consumable form I have a text box where the user would enter the quantity used and that gets sent back to the database. Then the user would click a button and update the quantity field with the new amount.

    Or in other words quantity=quantity-quantityused

    I've tried something similar to this but have had no luck

    Code:
    buttonclick()
    dim dbs as database,rst as recordset, strsql as string 
    set dbs=currentdb 
    strsql="SELECT * FROM [Consumables] WHERE [itemid]=" & Forms![order 
    form![itemid] 
    set rst=dbs.openrecordset(strsql,dbopendynaset) 
    with rst 
    .edit 
    ![quantity]=![quantity]- Forms![orderform]![quantityused] 
    .update 
    .close 
    end with
    end sub
    Is there anyone out there who could point me in the right direction. Is there an easier solution for what I'm proposing to do to track inventory?


    Thanks for any help I can get I'm really stuck here.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Generally speaking, it's not a good idea to store a calculated value. Here's a nice discussion:

    http://allenbrowne.com/AppInventory.html
    Paul

  3. #3
    Join Date
    Mar 2004
    Location
    St. John's, NFLD
    Posts
    18
    I've come across this example in my google searches, so I guess I'll try and replicate and then modify whats been done to get a better idea of what I need to do.

    Thanks, I'll repost if I get anywhere with it.

    Cheers

  4. #4
    Join Date
    Mar 2004
    Location
    St. John's, NFLD
    Posts
    18
    Thanks Paul,

    The example from the link seems to provide a VERY good basis for exactly what I want to accomplish. Great stuff! Cheers to you and Allen Browne! hehe

    Regards,

    Will

Posting Permissions

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