Update Query Issues, Revisited!

[lee.croucher]

I'm trying to update my units in stock within a table. This needs to
be updated whenever a product is sold. The products which are sold are
selected through a combo box on my transaction form.

I have looked into the DSum functions but I can only get the DSum
function to add up numbers from a column, I cannot get the DSum
function to remove items from the stock table based on the value
selected in this combo box, the formula I have at the moment is:

DSum("UnitsInSt ock","Products" )

I am aware that this will look in the Products table and choose the
field UnitsInStock and this should total these values up, but when I
run the query I get a blank field, whereas the field should state to
total number of items in stock.

Also, this is where it gets complicated, hopefully I can explain this
without the use of pictures, I have a form called "Appointmen ts Form"
which has a sub form linked to the form "Products Sold Form" this has
a selection drop down box (from the Products Sold Form) that gets its
information from the Products table and the Product Description field,
this combo box has the name "combo_srv"

I originally thought that a command something like:

[combo_srv.produ cts] or [combo_srv.produ cts."product description"]

This is not working how I would like, so really what I am asking is if
someone selects say "Wella Gel" from the combo box "combo_srv" and
subits the order using the Submit button, I would like the field "Units
In Stock" under "Product" to be updated correctly, it this case to
minus one from the total value in stock.

Please can someone help me with this issue, if you would like any
images or the actual databse please let me know by mailing me.

Regards,

Lee Croucher

[King Ron]

>the formula I have at the moment is:

DSum("UnitsInSt ock","Products" )

Ola L:
For the easy part: the formula you show is looking for a literal string
value:

DSum("UnitsInSt ock","Products" )

In order to force Access to interpret this as a field reference you
need to place the field name in square brackets:

DSum("[UnitsInStock]","Products ")

For the more complex part, I would recommend that instead of directly
manipulating the InStock value that you instead use a more auditable
transaction based model:

1) when the product is inventoried, the field .OnHandAtInvent ory is
updated.
2) Each order is entered into an order transaction table with the
quantity ordered noted.
3) At any time, you can calculate the quantity on hand by
OnHandAtInvento ry - Sum(OrderQty)

In the Interface, you could implement this by creating a single field
subform whose Form.Recordsour ce property is this calculation. By
asserting a Form.Requery to the sfmOnHand subform after each order,
the calculated quantity on hand would be displayed without manipulating
the validated inventory stored value at all.

King Ron of Chi

[lee.croucher]

Ron,

When I use the command shown below in the query:

DSum("[UnitsInStock]","Products ")

The system allows the query to run, but no information is preset, I get
a blank text inpur box, I am not sure if this is my access or I am
using the command incorrectly.

Please can you assist me with this issue, many thanks in advance

Regards,

Lee Croucher

King Ron wrote:

the formula I have at the moment is:
DSum("UnitsInSt ock","Products" )
Ola L:
For the easy part: the formula you show is looking for a literal

string value:

DSum("UnitsInSt ock","Products" )

In order to force Access to interpret this as a field reference you
need to place the field name in square brackets:

DSum("[UnitsInStock]","Products ")

For the more complex part, I would recommend that instead of directly
manipulating the InStock value that you instead use a more auditable
transaction based model:

1) when the product is inventoried, the field .OnHandAtInvent ory is
updated.
2) Each order is entered into an order transaction table with the
quantity ordered noted.
3) At any time, you can calculate the quantity on hand by
OnHandAtInvento ry - Sum(OrderQty)

In the Interface, you could implement this by creating a single field
subform whose Form.Recordsour ce property is this calculation. By
asserting a Form.Requery to the sfmOnHand subform after each order,
the calculated quantity on hand would be displayed without manipulating the validated inventory stored value at all.

King Ron of Chi

[lee.croucher]

Ron,

When I use the command shown below in the query:

DSum("[UnitsInStock]","Products ")

The system allows the query to run, but no information is preset, I get
a blank text inpur box, I am not sure if this is my access or I am
using the command incorrectly.

Also, I understand the theory about the OnHandatInvento ry process, but
how do I get this query to update the database for my individual
product(s) as this is selected on the main form and needs to be
completed when the save button has been clicked

Please can you assist me with this issue, many thanks in advance

Regards,

Lee Croucher