reduce/increase field

[LJG]

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

 

[Allen Browne]

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

 

[Lez]

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:

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[Allen Browne]

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.

Reply to group, rather than allenbrowne at mvps dot org.

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:

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[LJG]

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

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.

Reply to group, rather than allenbrowne at mvps dot org.

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:

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.

Reply to group, rather than allenbrowne at mvps dot org.

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