Hi everyone,
I have a fairly typical situation with an orders table and a order details table. I am using a BE/FE design and I have placed a data macro on the after update event of the quantity shipped field of the order details table in the back end. The products table has a field name qtyOnHand and that is supposed to update every time someone enters a new line item in order details. The problem is this: I start with a product with say 1000 in inventory. I create numerous line items in different orders through a form and check the inventory. Occasionally the after update event does not fire and i'm left with a quantity on hand that has not been updated!
If you delete the quantity shipped then it will add it back to the quantity on hand. If I create a few line items, enter quantity shipped, then go back and delete each of those, theoretically I should be back at 1000 in inventory. This is not the case. Skipping entries appears random but perhaps its something else that is going on with my macro. Has anyone experienced this kind of behavior with data macros? I find it hard to believe that this could be a bug as it would render data macros completely useless.
Full Version: Data Macro Not Updating Field?
So, rather than keeping a 'log' of transactions affecting the quantity on hand, you're actually going in and modifying the quantity?
What about the idea of having each order (detail) row represent a decrement, each "restocking" event represent an increment, and start with a single, unchanging quantity (i.e., quantity-at-start)? That way, you'd be able to use a query to calculate, on the fly, what the current balance was of any item.
...oh yes,
What about the idea of having each order (detail) row represent a decrement, each "restocking" event represent an increment, and start with a single, unchanging quantity (i.e., quantity-at-start)? That way, you'd be able to use a query to calculate, on the fly, what the current balance was of any item.
...oh yes,
Thank you for the welcome 
I originally created the database from a microsoft article detailing the method of updating a quantity on hand and I used ADO to do this. After finally handling the many errors that this can cause I finally got it to work well. I thought that the data macros would be an enormous improvement over the method I am using and fairly easy to implement when I update to 2010. I have toyed with the idea of calculating the inventory on the fly but I have never successfully got it to work and there are so many places that this application calculates its inventory from that it would be a huge undertaking to change it all. I have production, repackaging, orders, returns, broken or discarded etc. I have been unable to find an example of the on-the-fly approach that I can use to adapt to my situation.
To answer your first question, I am storing a quantity on hand each time a transaction is entered from any one of many locations. There is also a log of each transaction in the order details table but each product in the product table has an inventory total in a field. I have tested a little more and I struggling with the idea that it works sometimes and not others. It must be something I have done in the macro but for the life of me and can't see it. The after update event fires as soon as the record is saved, right? What could possibly cause the macro to change the field one time and not the next? I get no error, it just doesnt change the total on hand.
If you or anyone has a link to an example inventory database that calculates on the fly, I would like to look at that again, but I will not be able to change the whole project over for quite some time. I need to get my macros working or stick with ADO, which I do not want to do.
Cheers
I originally created the database from a microsoft article detailing the method of updating a quantity on hand and I used ADO to do this. After finally handling the many errors that this can cause I finally got it to work well. I thought that the data macros would be an enormous improvement over the method I am using and fairly easy to implement when I update to 2010. I have toyed with the idea of calculating the inventory on the fly but I have never successfully got it to work and there are so many places that this application calculates its inventory from that it would be a huge undertaking to change it all. I have production, repackaging, orders, returns, broken or discarded etc. I have been unable to find an example of the on-the-fly approach that I can use to adapt to my situation.
To answer your first question, I am storing a quantity on hand each time a transaction is entered from any one of many locations. There is also a log of each transaction in the order details table but each product in the product table has an inventory total in a field. I have tested a little more and I struggling with the idea that it works sometimes and not others. It must be something I have done in the macro but for the life of me and can't see it. The after update event fires as soon as the record is saved, right? What could possibly cause the macro to change the field one time and not the next? I get no error, it just doesnt change the total on hand.
If you or anyone has a link to an example inventory database that calculates on the fly, I would like to look at that again, but I will not be able to change the whole project over for quite some time. I need to get my macros working or stick with ADO, which I do not want to do.
Cheers
Nobody has encountered this situation before? Does anyone have any ideas about how to troubleshoot something like this?
Nobody has encountered this situation before? Does anyone have any ideas about how to troubleshoot something like this?
The problem is that the after update event only fires on a record update, not on an insert.
I would move your sample code to a named macro, and then put a call in BOTH the after update event and after insert event.
So for after insert and after update, just have one line of code to call the named macro
RunDataMacro (name of your data macro).
You should not have to change your code since when calling a named macro ALL of the current values remain in context.
However, if you call a named macro from a form, then there IS no context.
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
kallal@msn.com
I would move your sample code to a named macro, and then put a call in BOTH the after update event and after insert event.
So for after insert and after update, just have one line of code to call the named macro
RunDataMacro (name of your data macro).
You should not have to change your code since when calling a named macro ALL of the current values remain in context.
However, if you call a named macro from a form, then there IS no context.
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
kallal@msn.com
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.