single record transaction to onhand quantity in inventory record

[Paul Hanrishfeger]

I want to creat a single form record transaction that takes field [issue] and subtracts it from field [qtyonhand] in the inventory table. I have the form based on a query with the issue table linked to the inventory table. Microsoft Access 2003.

[NeoPa]

And what exactly is your difficulty Paul?

You've described the issue well enough, but we have no idea at this stage how far you've got , therefore what you need specific help with.

Perhaps it's help with producing the value in your query?

[Paul Hanrishfeger]

And what exactly is your difficulty Paul?

You've described the issue well enough, but we have no idea at this stage how far you've got , therefore what you need specific help with.

Perhaps it's help with producing the value in your query?

I have created a form based on a query that is indented to perform the issue. A drop down selects the item and populates text boxes showing the inventory information including QtyonHand and an emply issue text box. I want to create a new record in the issue table and at the same time update the quantity on hand in the inventory table without having to change it manually.
I tried adding a Quantity property "on change" expression QtyonHand=Qtyon Hand - Quantity which added the new issue record but did not change the inventory table QtyonHand. Seems like a valid reason to force a unique record inventory field to it's new value.

[Paul Hanrishfeger]

RE perhaps

I'm having trouble getting an update to the linked table Inventory QtyonHand.
The query textbox does not update either. If I change the textbox manually it updates the inventory table. I want it to happen automatically since there is a high likeyhood the user will not update the QtyonHand box.
Thanks,
Paul

[NeoPa]

To create a new record as well as updating an existing record from another table would require an updatable query with the two tables linked. From what you say it seems you have such a query working already. Can you post its SQL for me to look at. This is normally quite hard to produce. It must obviously be an updatable query (Reasons for a Query to be Non-Updatable) of course.

[Paul Hanrishfeger]

Query Question

Queries that form is based on and the update query issued by a Macro from the change property of the issue quantity textbox.

UPDATE Issues INNER JOIN [Crib Inventory] ON Issues.ProductID = [Crib Inventory].[Item Code] SET [Crib Inventory].QuantityOnHand = [Crib Inventory]!QuantityOnHand-Issues!Quantity
WHERE (((Issues.IssueNumber)="Current Record"));

Form Query

SELECT DISTINCTROW Issues.IssueNumber, Issues.[SAP Part Number], Issues.LastName, Issues.FirstName, Issues.OrderDate, Issues.ProductID, Issues.[Returned Y/N], Issues.Quantity, [Crib Inventory].QuantityOnHand
FROM [Crib Inventory] INNER JOIN Issues ON [Crib Inventory].[Item Code]=Issues.ProductID;

[Paul Hanrishfeger]

Solved myself

I played with udate queries but they want to address all records so the deduction would subtract from all former entries. I did not know how to snag the current new record number for criteria automatically.
Solution: Event Procedure
Quantity text box property After Update
Code:

Me.[QuantityOnHand].value=Me.[QuantityOnHand].value-Me.[Quantity].value

Result:
Updated the new issue record and changed the Inventory QuantityOnHand when the new quantity was entered in the form. Corrections could be made by entering a negative quantity to return the Quantity on hand and adding the correct quantity before closing the form.

[NeoPa]

The query your form is built on seems fine.

What are the circumstances that you want to trigger this update on?
If it is related specifically to the data on the form, then an update query is not generally the best approach. I would put some code in a Form_BeforeUpda te event procedure, unless you'd like this to be visible to the operator for review before the record is saved, in which case an AfterUpdate event procedure of the dependent control would be more suitable. Frankly, as this is an adjustment rather than a simple creation, the latter is no longer an option. This must only be done immediately prior to saving the record, otherwise it's perfectly possible to apply the same change multiple times. This would clearly cause problems in this scenario.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.QuantityOnHand = Me.QuantityOnHand - Me.Quantity
End sub

[Paul Hanrishfeger]

Multiple transactions not a problem

The QuantityOnHand textbox on the Form updates immediately when the Issue Quantity is entered. If you enter a wrong number you can back it out by entering it again with a minus in front of it, then reenter the correct number.
the transaction doesn't post to the tables until the record changes or Form is closed. I have tried various scenarios and couldn't cause a problem.
Thanks for your help.
Paul

[NeoPa]

Is this just for your own use Paul? I don't think too many users will be impressed with having to handle such a situation manually, but it's rarely such an issue for the designer themselves.

That said, doing it the other way correctly matches the situation so there is no problem to have to get around in the first place.

You use whatever you're happy with at the end of the day of course.