Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2006

    Unanswered: newbie question: please help! thanks!!

    I’m creating a simple inventory database: an "IN" form, "OUT" form, and an on-hand report. The thing is, sometimes I’d get negative on-hand inventory levels on my report because "OUT" quantity is not restricted only to what’s available, meaning you could subtract more than the current available amount. What I’d like to do is prevent the user from subtracting item amounts more than the current level… how do I do that?
    Last edited by mark1983; 12-13-06 at 01:43. Reason: re-wording

  2. #2
    Join Date
    Oct 2003
    Provided Answers: 1
    Would you like to do that on the form or table level?

  3. #3
    Join Date
    Dec 2006
    which is easier?

    I just don't want to be getting negative inventories... that's all.

  4. #4
    Join Date
    Dec 2002
    Préverenges, Switzerland
    i'm a stranger in bound-form world, so i couldn't come up with a way to cancel the save. after a browse in help, this is the best i can suggest:

    in the table design set your inventory field validation rule:
    and (optionally) validation text:
    Not enough inventory

    now you can trap the validation error in code:
    private sub form_err(dataerr as integer, response as integer)
    const cstValidErrWithMsg = 3316 ' ?? # if validation text exists
    const cstValidErrWoMsg = 3317 ' ?? # if no validation text
    if dataerr = cstValidErrWithMsg or dataerr = cstValidErrWoMsg then
    me.somefield.undo ' ?? kill the offending order quantity
    response = acDataErrContinue
    msgbox "some other sort of data error",,"Worry..."

    currently using SS 2008R2

  5. #5
    Join Date
    Nov 2003
    Sussex, England
    I'd suggest putting something in the before update event to either warn the user that the result will be a negative stock result, cancel the save operation or simply set the result to zero stock items.

    Having said that I think you're treating the symptoms not the problem, it shold be impossible to subract more than is in stock and some sort of checking needs to go n the control (text box) that the user can enter the number into. I'd suggest code in the on click event checking the exisitng quantity and refusing any quantity over that.

  6. #6
    Join Date
    Mar 2004
    i use a similar routine for stock control

    if you set a text box to look up the current stock level, i find dlookup function best for this.

    you can then use the afterupdate property of the control where you enter the qty to check if the qty out is greater than the stock qty and display a message box if so.

    i have to say that in real life though you will get negative qty's. you should not be trying to prevent users from "booking out" any qty that might put you in to negative stock.

    people book out one item by mistake and the supply another, or book in one when another is recieved.

    i think the way around this is to create a stock take routine.

    Access 2000/2003 stock control and invoicing systems

Posting Permissions

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