Add a function in reports to clear a field for each record

[BDibz]

This may be a bit unusual, but here's what I'm trying to do.

I have a transaction-based inventory tracker to keep track of how many electrodes are on hand at any point. Some of these Electrodes are part of a Purchase Order (PO) so we added the option to tag a transaction with a PO number. This report aggregates the number of electrodes checked in by PO number, so they can see if they have received all of the electrodes to fulfill the PO. Since this list would grow indefinitely, I added a "clear all POs" button as a temporary solution that Updates all records in the transactions table, setting PO = ''.

I want to be able to do this on a per-electrode basis - so you'd have a button next to each of the entries on the report that would allow you to clear the PO field for all of the transaction entries that have Electrode ID equal to the one correlating to the button you push. Any suggestions for a good way to do this?

[ranman256]

you dont put buttons on reports, you put them on the form, run macros from there THEN run reports.

[BDibz]

Ok, well if I were doing the exact same thing in a form, is it doable?

[orange]

BDibz,

Have you reviewed Allen Browne's info re Quantity on Hand?

I don't know the details of your set up, but I see 2 concepts/processes involved and offer these comments for consideration.

1) You do a stock taking of physical electrodes-- that gives you the physical counts. You record the quantity of electrodes entering your system as positive transactions and you record quantity of electrodes used/given to employees as negative transactions.
So at any time t, the quantity of Hand = Last stockTaking Count + QuantityIncoming - Quantity Outgoing/used

2) You have Purchase Orders containing requested quantity of electrodes and you have Goods (electrodes) received. You also have some internal process to record goods received --this is a check/audit/ accounting of complete or partial shipments of electrodes. I'm sure there are situations where the supplier(s) may not have X electrodes so send less than X and owe you the remainder. So a PO quantity may not equal the shipped/received quantity.
So it seems with respect to PurchaseOrders and GoodsReceived you would have
CurrentElectrodeBalance = ElectrodesOrdered - ElectrodesReceived

and this would applied for each Electrode type and each Supplier (which probably equates to your PO??)


Another point to consider is that Reports are meant for printing. And Forms are meant for user interaction.

Good luck with your project.

OOOps: I see ranman has responded while I was typing.