By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 1,583 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

single record transaction to onhand quantity in inventory record

P: 7
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.
Mar 24 '10 #1

✓ answered by 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_BeforeUpdate 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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.     Me.QuantityOnHand = Me.QuantityOnHand - Me.Quantity
  3. End sub

Share this Question
Share on Google+
9 Replies


NeoPa
Expert Mod 15k+
P: 31,186
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?
Mar 24 '10 #2

P: 7
@NeoPa
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=QtyonHand - 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.
Mar 24 '10 #3

P: 7
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
Mar 24 '10 #4

NeoPa
Expert Mod 15k+
P: 31,186
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.
Mar 24 '10 #5

P: 7
Queries that form is based on and the update query issued by a Macro from the change property of the issue quantity textbox.
Expand|Select|Wrap|Line Numbers
  1. UPDATE Issues INNER JOIN [Crib Inventory] ON Issues.ProductID = [Crib Inventory].[Item Code] SET [Crib Inventory].QuantityOnHand = [Crib Inventory]!QuantityOnHand-Issues!Quantity
  2. WHERE (((Issues.IssueNumber)="Current Record"));
Form Query
Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM [Crib Inventory] INNER JOIN Issues ON [Crib Inventory].[Item Code]=Issues.ProductID;
Mar 24 '10 #6

P: 7
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:
Expand|Select|Wrap|Line Numbers
  1. 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.
Mar 25 '10 #7

NeoPa
Expert Mod 15k+
P: 31,186
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_BeforeUpdate 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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.     Me.QuantityOnHand = Me.QuantityOnHand - Me.Quantity
  3. End sub
Mar 25 '10 #8

P: 7
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
Mar 25 '10 #9

NeoPa
Expert Mod 15k+
P: 31,186
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.
Mar 25 '10 #10

Post your reply

Sign in to post your reply or Sign up for a free account.