Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

reduce/increase field

Discussion in 'Information Technology' started by LJG, Jul 28, 2006.

  1. LJG

    LJG
    Expand Collapse
    Guest

    Hi Guys,

    I have an order modification form and need a field [txtadjust) to enter a
    number to reduce/increase the value of field [qty]

    I do not need to save the value of this field, I just need it to
    reduce/increase the value in [qty] on save of the form the value of
    [txtadjust] will modify the stock level.

    The problem I have is how can I add this field and either include it in the
    recordset or via an onClick action of an update button.

    I am sure this is dead simple, but I cannot figure this out.

    Any help suggestions would be appreciated.

    TIA
     
  2. Loading...


  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Could we get you to think through how this will work before you try to
    implement it?

    Do you already have the qty field performing the stock reduction? How are
    you doing that? In the AfterUpdate event of the form?

    What happens if the user goes back and changes something when the qty is
    100. Does it subtract the 100 a second time?

    What happens if the user goes back and changes the qty from 100 to 10? Does
    it restore the other 90?

    What happns if the user goes back and changes the product form "widget" to
    "watzit"? Does it restore the 100 widgets that were previously subtracted?

    What happens if the user deletes the record? Do the 100 wigets get restored?

    What happens if the user selects several rows to delete at once? Does it
    handle them all?

    What happens if the user cancels the deletion? Does it wrongly restore the
    stock levels even if the deletion does not proceed?

    Does your stock level gradually become more and more wrong over time, as
    these and other scenarios arise?

    If you just try to force an adjustment abritrarily (e.g. for breakage), how
    will you know later whether the stock level should be the reduced value, or
    if that is just the result of one of these errors.

    An alternative approach would be to get Access to calculate the stock level
    when you need it. Also, allow for periodic stock-taking to reset the
    quantities to known levels. For details of that approach, see:
    Inventory Control - Quantity On Hand
    at:
    http://allenbrowne.com/AppInventory.html

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "LJG" <lj_girvan@no_spam_hotmail.com> wrote in message
    news:OzuCakmoGHA.516@TK2MSFTNGP05.phx.gbl...
    > Hi Guys,
    >
    > I have an order modification form and need a field [txtadjust) to enter a
    > number to reduce/increase the value of field [qty]
    >
    > I do not need to save the value of this field, I just need it to
    > reduce/increase the value in [qty] on save of the form the value of
    > [txtadjust] will modify the stock level.
    >
    > The problem I have is how can I add this field and either include it in
    > the recordset or via an onClick action of an update button.
    >
    > I am sure this is dead simple, but I cannot figure this out.
    >
    > Any help suggestions would be appreciated.
    >
    > TIA
    >
     
  4. Lez

    Lez
    Expand Collapse
    Guest

    Hi Allen,

    The form this is event is to take place is an 'edit' version of the order
    form. When the order is created on closing the form I have a script that
    updates a table 'tblInventory', and the [qty] is reduced from the inventory
    table.

    The edit version allows for the original order [qty] to be either reduced or
    increased. As the [qty] has already been deduced from tblInventory the
    txtadjust will be used to either reduced or increase i.e. -10 or 10 on
    finishing the adjustments required the user will simply click the close
    button to again run the code to update [qty] in tblInventory.

    As the subform has been created as a continuous form, the user cannot select
    all the rows to delete and I have blocked access to certain fields to avoid
    them changing the wrong one.

    My idea is to have field [txtadjust] with a button and onClick action
    something like Me.qty - Me.txtadjust

    So row by row the user can modify qty values. When complete and closing the
    form, the onClick of the close button updates tblInventory.[qty] by
    [txtadjust]

    I have further commented your questions below:


    "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    news:uGNBJ2moGHA.148@TK2MSFTNGP04.phx.gbl...
    > Could we get you to think through how this will work before you try to
    > implement it?
    >
    > Do you already have the qty field performing the stock reduction? How are
    > you doing that? In the AfterUpdate event of the form?


    The user is only allowed to open the form and edit once the record
    has been created.

    > What happens if the user goes back and changes something when the qty is
    > 100. Does it subtract the 100 a second time?


    The [qty] is not used to update the stock levels, the stock
    reduction/increase will be from the unbound field[txtadjust] the update will
    occure on form close, hence my question on creating an unbound field
    txtadjust

    > What happens if the user goes back and changes the qty from 100 to 10?
    > Does it restore the other 90?


    The idea being field qty = 100 txtadjust = -90 the -90 would update
    the qty in tblInventory

    > What happns if the user goes back and changes the product form "widget" to
    > "watzit"? Does it restore the 100 widgets that were previously subtracted?


    The user is blocked from changing the products, can only update qty
    and new requests would be placed on subsequent order

    > What happens if the user deletes the record? Do the 100 wigets get
    > restored?


    No deletion is allowed


    > What happens if the user selects several rows to delete at once? Does it
    > handle them all?


    The form is created as a continuos form and has to edit row by row
    with only txtadjust avialable


    > What happens if the user cancels the deletion? Does it wrongly restore the
    > stock levels even if the deletion does not proceed?


    Cancl does not have any update ation and nothing is changed

    > Does your stock level gradually become more and more wrong over time, as
    > these and other scenarios arise?


    Only just creating it, so cannot tell at this time. I am expeting it
    not to !!
    >
    > If you just try to force an adjustment abritrarily (e.g. for breakage),
    > how will you know later whether the stock level should be the reduced
    > value, or if that is just the result of one of these errors.


    I have a seperate form for handling breakage/stock adjustments by
    moving items into tblfaulty


    > An alternative approach would be to get Access to calculate the stock
    > level when you need it. Also, allow for periodic stock-taking to reset the
    > quantities to known levels. For details of that approach, see:
    > Inventory Control - Quantity On Hand
    > at:
    > http://allenbrowne.com/AppInventory.html


    Will take a look at this approach, I am however creating a stock-
    taking form for these purposes.



    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "LJG" <lj_girvan@no_spam_hotmail.com> wrote in message
    > news:OzuCakmoGHA.516@TK2MSFTNGP05.phx.gbl...
    >> Hi Guys,
    >>
    >> I have an order modification form and need a field [txtadjust) to enter a
    >> number to reduce/increase the value of field [qty]
    >>
    >> I do not need to save the value of this field, I just need it to
    >> reduce/increase the value in [qty] on save of the form the value of
    >> [txtadjust] will modify the stock level.
    >>
    >> The problem I have is how can I add this field and either include it in
    >> the recordset or via an onClick action of an update button.
    >>
    >> I am sure this is dead simple, but I cannot figure this out.
    >>
    >> Any help suggestions would be appreciated.
    >>
    >> TIA
    >>

    >
    >
     
  5. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    This is not going to work, as you cannot have an unbound control (txtAdjust)
    that has different values on each row. You would need to copy the values to
    a temporary table that has a field for the Adjust, and commit the
    adjustments at the end of the process. If this is a mult-user application,
    that's not a simple approach: ensuring that different users are not trying
    to adjust the same records at the same time.

    It is possible to let the user change the qty directly in each row. Then in
    Form_BeforeUpdate, compare qty with qty.OldValue, and save the difference
    into a module level variable (i.e. one declared in the form's General
    Declarations section - top of the module, with the Option statements.) Then
    in Form_AfterUpdate, when you know the change was saved, execute an UPDATE
    query string to update the correct record in your inventory table with this
    adjustment.

    It is possible to select multiple rows in Continuous Form view.

    The problems of the stored quantity apply to the original form (where the
    entry was created) as well (i.e. the original logic you used to reduce the
    quantity when the order is entered.)


    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Lez" <lj_girvan_N-O_5pAm@hotmail.com> wrote in message
    news:O%23w7TTsoGHA.3288@TK2MSFTNGP03.phx.gbl...
    > Hi Allen,
    >
    > The form this is event is to take place is an 'edit' version of the order
    > form. When the order is created on closing the form I have a script that
    > updates a table 'tblInventory', and the [qty] is reduced from the
    > inventory table.
    >
    > The edit version allows for the original order [qty] to be either reduced
    > or increased. As the [qty] has already been deduced from tblInventory the
    > txtadjust will be used to either reduced or increase i.e. -10 or 10 on
    > finishing the adjustments required the user will simply click the close
    > button to again run the code to update [qty] in tblInventory.
    >
    > As the subform has been created as a continuous form, the user cannot
    > select all the rows to delete and I have blocked access to certain fields
    > to avoid them changing the wrong one.
    >
    > My idea is to have field [txtadjust] with a button and onClick action
    > something like Me.qty - Me.txtadjust
    >
    > So row by row the user can modify qty values. When complete and closing
    > the form, the onClick of the close button updates tblInventory.[qty] by
    > [txtadjust]
    >
    > I have further commented your questions below:
    >
    >
    > "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    > news:uGNBJ2moGHA.148@TK2MSFTNGP04.phx.gbl...
    >> Could we get you to think through how this will work before you try to
    >> implement it?
    >>
    >> Do you already have the qty field performing the stock reduction? How are
    >> you doing that? In the AfterUpdate event of the form?

    >
    > The user is only allowed to open the form and edit once the record
    > has been created.
    >
    >> What happens if the user goes back and changes something when the qty is
    >> 100. Does it subtract the 100 a second time?

    >
    > The [qty] is not used to update the stock levels, the stock
    > reduction/increase will be from the unbound field[txtadjust] the update
    > will occure on form close, hence my question on creating an unbound field
    > txtadjust
    >
    >> What happens if the user goes back and changes the qty from 100 to 10?
    >> Does it restore the other 90?

    >
    > The idea being field qty = 100 txtadjust = -90 the -90 would update
    > the qty in tblInventory
    >
    >> What happns if the user goes back and changes the product form "widget"
    >> to "watzit"? Does it restore the 100 widgets that were previously
    >> subtracted?

    >
    > The user is blocked from changing the products, can only update qty
    > and new requests would be placed on subsequent
    > order
    >
    >> What happens if the user deletes the record? Do the 100 wigets get
    >> restored?

    >
    > No deletion is allowed
    >
    >
    >> What happens if the user selects several rows to delete at once? Does it
    >> handle them all?

    >
    > The form is created as a continuos form and has to edit row by row
    > with only txtadjust avialable
    >
    >
    >> What happens if the user cancels the deletion? Does it wrongly restore
    >> the stock levels even if the deletion does not proceed?

    >
    > Cancl does not have any update ation and nothing is changed
    >
    >> Does your stock level gradually become more and more wrong over time, as
    >> these and other scenarios arise?

    >
    > Only just creating it, so cannot tell at this time. I am expeting
    > it not to !!
    >>
    >> If you just try to force an adjustment abritrarily (e.g. for breakage),
    >> how will you know later whether the stock level should be the reduced
    >> value, or if that is just the result of one of these errors.

    >
    > I have a seperate form for handling breakage/stock adjustments by
    > moving items into tblfaulty
    >
    >
    >> An alternative approach would be to get Access to calculate the stock
    >> level when you need it. Also, allow for periodic stock-taking to reset
    >> the quantities to known levels. For details of that approach, see:
    >> Inventory Control - Quantity On Hand
    >> at:
    >> http://allenbrowne.com/AppInventory.html

    >
    > Will take a look at this approach, I am however creating a stock-
    > taking form for these purposes.
    >
    >
    >
    >> --
    >> Allen Browne - Microsoft MVP. Perth, Western Australia.
    >> Tips for Access users - http://allenbrowne.com/tips.html
    >> Reply to group, rather than allenbrowne at mvps dot org.
    >>
    >> "LJG" <lj_girvan@no_spam_hotmail.com> wrote in message
    >> news:OzuCakmoGHA.516@TK2MSFTNGP05.phx.gbl...
    >>> Hi Guys,
    >>>
    >>> I have an order modification form and need a field [txtadjust) to enter
    >>> a number to reduce/increase the value of field [qty]
    >>>
    >>> I do not need to save the value of this field, I just need it to
    >>> reduce/increase the value in [qty] on save of the form the value of
    >>> [txtadjust] will modify the stock level.
    >>>
    >>> The problem I have is how can I add this field and either include it in
    >>> the recordset or via an onClick action of an update button.
    >>>
    >>> I am sure this is dead simple, but I cannot figure this out.
    >>>
    >>> Any help suggestions would be appreciated.
    >>>
    >>> TIA
    >>>

    >>
    >>

    >
    >
     
  6. LJG

    LJG
    Expand Collapse
    Guest

    Hi Allen,

    Thanks for that, the system is not in a multi user site, simply one user who
    process's the orders. Therefore your idea of a temp table seems the best
    option.

    Will give that a go.

    Regards
    Les

    "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    news:eaxNPWvoGHA.3288@TK2MSFTNGP03.phx.gbl...
    > This is not going to work, as you cannot have an unbound control
    > (txtAdjust) that has different values on each row. You would need to copy
    > the values to a temporary table that has a field for the Adjust, and
    > commit the adjustments at the end of the process. If this is a mult-user
    > application, that's not a simple approach: ensuring that different users
    > are not trying to adjust the same records at the same time.
    >
    > It is possible to let the user change the qty directly in each row. Then
    > in Form_BeforeUpdate, compare qty with qty.OldValue, and save the
    > difference into a module level variable (i.e. one declared in the form's
    > General Declarations section - top of the module, with the Option
    > statements.) Then in Form_AfterUpdate, when you know the change was saved,
    > execute an UPDATE query string to update the correct record in your
    > inventory table with this adjustment.
    >
    > It is possible to select multiple rows in Continuous Form view.
    >
    > The problems of the stored quantity apply to the original form (where the
    > entry was created) as well (i.e. the original logic you used to reduce the
    > quantity when the order is entered.)
    >
    >
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Lez" <lj_girvan_N-O_5pAm@hotmail.com> wrote in message
    > news:O%23w7TTsoGHA.3288@TK2MSFTNGP03.phx.gbl...
    >> Hi Allen,
    >>
    >> The form this is event is to take place is an 'edit' version of the order
    >> form. When the order is created on closing the form I have a script that
    >> updates a table 'tblInventory', and the [qty] is reduced from the
    >> inventory table.
    >>
    >> The edit version allows for the original order [qty] to be either reduced
    >> or increased. As the [qty] has already been deduced from tblInventory the
    >> txtadjust will be used to either reduced or increase i.e. -10 or 10 on
    >> finishing the adjustments required the user will simply click the close
    >> button to again run the code to update [qty] in tblInventory.
    >>
    >> As the subform has been created as a continuous form, the user cannot
    >> select all the rows to delete and I have blocked access to certain fields
    >> to avoid them changing the wrong one.
    >>
    >> My idea is to have field [txtadjust] with a button and onClick action
    >> something like Me.qty - Me.txtadjust
    >>
    >> So row by row the user can modify qty values. When complete and closing
    >> the form, the onClick of the close button updates tblInventory.[qty] by
    >> [txtadjust]
    >>
    >> I have further commented your questions below:
    >>
    >>
    >> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    >> news:uGNBJ2moGHA.148@TK2MSFTNGP04.phx.gbl...
    >>> Could we get you to think through how this will work before you try to
    >>> implement it?
    >>>
    >>> Do you already have the qty field performing the stock reduction? How
    >>> are you doing that? In the AfterUpdate event of the form?

    >>
    >> The user is only allowed to open the form and edit once the
    >> record has been created.
    >>
    >>> What happens if the user goes back and changes something when the qty is
    >>> 100. Does it subtract the 100 a second time?

    >>
    >> The [qty] is not used to update the stock levels, the stock
    >> reduction/increase will be from the unbound field[txtadjust] the update
    >> will occure on form close, hence my question on creating an unbound field
    >> txtadjust
    >>
    >>> What happens if the user goes back and changes the qty from 100 to 10?
    >>> Does it restore the other 90?

    >>
    >> The idea being field qty = 100 txtadjust = -90 the -90 would
    >> update the qty in tblInventory
    >>
    >>> What happns if the user goes back and changes the product form "widget"
    >>> to "watzit"? Does it restore the 100 widgets that were previously
    >>> subtracted?

    >>
    >> The user is blocked from changing the products, can only update
    >> qty and new requests would be placed on
    >> subsequent order
    >>
    >>> What happens if the user deletes the record? Do the 100 wigets get
    >>> restored?

    >>
    >> No deletion is allowed
    >>
    >>
    >>> What happens if the user selects several rows to delete at once? Does it
    >>> handle them all?

    >>
    >> The form is created as a continuos form and has to edit row by row
    >> with only txtadjust avialable
    >>
    >>
    >>> What happens if the user cancels the deletion? Does it wrongly restore
    >>> the stock levels even if the deletion does not proceed?

    >>
    >> Cancl does not have any update ation and nothing is changed
    >>
    >>> Does your stock level gradually become more and more wrong over time, as
    >>> these and other scenarios arise?

    >>
    >> Only just creating it, so cannot tell at this time. I am expeting
    >> it not to !!
    >>>
    >>> If you just try to force an adjustment abritrarily (e.g. for breakage),
    >>> how will you know later whether the stock level should be the reduced
    >>> value, or if that is just the result of one of these errors.

    >>
    >> I have a seperate form for handling breakage/stock adjustments by
    >> moving items into tblfaulty
    >>
    >>
    >>> An alternative approach would be to get Access to calculate the stock
    >>> level when you need it. Also, allow for periodic stock-taking to reset
    >>> the quantities to known levels. For details of that approach, see:
    >>> Inventory Control - Quantity On Hand
    >>> at:
    >>> http://allenbrowne.com/AppInventory.html

    >>
    >> Will take a look at this approach, I am however creating a
    >> stock- taking form for these purposes.
    >>
    >>
    >>
    >>> --
    >>> Allen Browne - Microsoft MVP. Perth, Western Australia.
    >>> Tips for Access users - http://allenbrowne.com/tips.html
    >>> Reply to group, rather than allenbrowne at mvps dot org.
    >>>
    >>> "LJG" <lj_girvan@no_spam_hotmail.com> wrote in message
    >>> news:OzuCakmoGHA.516@TK2MSFTNGP05.phx.gbl...
    >>>> Hi Guys,
    >>>>
    >>>> I have an order modification form and need a field [txtadjust) to enter
    >>>> a number to reduce/increase the value of field [qty]
    >>>>
    >>>> I do not need to save the value of this field, I just need it to
    >>>> reduce/increase the value in [qty] on save of the form the value of
    >>>> [txtadjust] will modify the stock level.
    >>>>
    >>>> The problem I have is how can I add this field and either include it in
    >>>> the recordset or via an onClick action of an update button.
    >>>>
    >>>> I am sure this is dead simple, but I cannot figure this out.
    >>>>
    >>>> Any help suggestions would be appreciated.
    >>>>
    >>>> TIA
    >>>>
    >>>
    >>>

    >>
    >>

    >
    >
     

Share This Page