Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2002
    Posts
    20

    Wink Unanswered: inventory control [UnitsInStock] = [UnitsInStock] - 1

    I have a seemingly simple question, but I've not been able to get the syntax right:

    In a form, once I select the [SeriesName] value from the field [Products], I want to subtract 1 from the value [UnitsInStock] from the same field and display this [UnitsInStock] value on the form.

    What would be the expression be to accomplish this?

    Thanks in advance for any help!
    Last edited by bricklebrit; 03-16-02 at 01:08.

  2. #2
    Join Date
    Oct 2001
    Location
    Bay Area, CA
    Posts
    117
    If your field is a list box with 2 columns, I assume the data is pulled out of a table.

    In this case, you need to update this table. Either through code, where you dynamically update your table, or with an update query that filters the data based on the selected value (preferably the primary key for your SeriesName). You end with requerying your products field.

    Sorry I can't give you a more specific solution, You have to give us more detail about your form (type of field, underlying data, use of sub form, etc).

  3. #3
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    I assume you have a table, or query, resulting in something like this:

    Code:
    Product:       QuantityOnHand:
    Dohicky One    12
    Dohickey Two   4
    Dohickey Three 78
    I assume that this is the rowsource for some combobox, and when the user selects from that combobox, you want to deduct one from the QuantityOnHand column.

    Assuming that the Product field is a uniquely indexed column, on the AfterUpdate event of the combobox, you can add an update query which will deduct one from the QuantityOnHand column. You can do this with a named querym or an SQL statement and if you've been following some of the other threads in this forum, you know which I prefer.

    You will then need to cause a requery of the combobox's rowsource 'cause you want to update the QuantityOnHand column with the latest information. The requery is simply:

    [NameOfTheComboboxControl].requery

    Good luck.

    Ken

  4. #4
    Join Date
    Feb 2002
    Posts
    108

    Post Are any of the values null ?

    If so, make sure you are using NZ. Example
    =nz([unitsinstock],0)-nz([unitssold],0). That way if one of the items is null or blank, it will assume a zero.

  5. #5
    Join Date
    Mar 2002
    Posts
    20

    Thumbs up

    WORKS GREAT! Thanks so much for your assistance!

Posting Permissions

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