DATABASE UPDATE NOT TAKING PLACE

G

Guest

HELLO All,

Would appreciate if I could get help on a particular problem i am working on.

I am creating a simple inventory system that maintins records for all items
in and items out and also track items to order.


I have an item master, purchase master, inventory master table.

I have created a form were we enter the po number , date and has a subform
which is linked by purchase id as the child field in the inventory subform.


Based on the purchase id the corresponding record gets displayed on the
subform.

I have calculated Qty on hand using

=Sum(nz([QTY RECEIVED])-nz([QTY SHIPPED]))

This value gets calculated correctly while inputing qty recd and qty shipped
values but does not update the table.

Can you help me what i should do to update the value. I am not too good in
VBA coding too.

thanks a ton in advance
 
J

Jeff Boyce

If this is a value you can calculate (you did provide a formula, right?),
and if the value could change, depending on values of Shipped and Received,
then why bother storing it?

Instead, do your calculation in a query.

In fact, if your underlyling values (Shipped and Received) are "calculated",
consider doing away with them, too! Instead, do a calculation on the fly
that adds up all Qty Shipped amounts and Qty Received amounts and determines
the difference.

Of course, the HOW you would do this depends heavily on HOW your data is
structured...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
G

Guest

Hi Jeff,


Thanks for taking the time to answer my query. I am not very strong in
Access and i do know that you need to run action query to update the value in
the database.

For every input of qty received the qty shipped and total project qty must
change.

eg. Tot project qty = 100

qty received = 50 qty to order = 100- 50 = 50

Qty shipped = 20

qoh = qty received - qty shipped = 50- 20 = 30. This should be seperate
from Tot project qty and next time for the same item say I receive qty 50 than

Tot project qty = 50 + 50 = 100 tot project qty is complete and there the
qty received should be 0 from now on.

The QOH shoud be updated in the database for each input.
I hope I did not confuse you too much. Would appreciate some guidelines as
to how to proceed.

thanks in advance




Jeff Boyce said:
If this is a value you can calculate (you did provide a formula, right?),
and if the value could change, depending on values of Shipped and Received,
then why bother storing it?

Instead, do your calculation in a query.

In fact, if your underlyling values (Shipped and Received) are "calculated",
consider doing away with them, too! Instead, do a calculation on the fly
that adds up all Qty Shipped amounts and Qty Received amounts and determines
the difference.

Of course, the HOW you would do this depends heavily on HOW your data is
structured...

Regards

Jeff Boyce
Microsoft Office/Access MVP

vandy said:
HELLO All,

Would appreciate if I could get help on a particular problem i am working
on.

I am creating a simple inventory system that maintins records for all
items
in and items out and also track items to order.


I have an item master, purchase master, inventory master table.

I have created a form were we enter the po number , date and has a subform
which is linked by purchase id as the child field in the inventory
subform.


Based on the purchase id the corresponding record gets displayed on the
subform.

I have calculated Qty on hand using

=Sum(nz([QTY RECEIVED])-nz([QTY SHIPPED]))

This value gets calculated correctly while inputing qty recd and qty
shipped
values but does not update the table.

Can you help me what i should do to update the value. I am not too good in
VBA coding too.

thanks a ton in advance
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top