473,322 Members | 1,347 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

single record transaction to onhand quantity in inventory record

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

9 3528
NeoPa
32,556 Expert Mod 16PB
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
@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
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
32,556 Expert Mod 16PB
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
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
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
32,556 Expert Mod 16PB
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
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
32,556 Expert Mod 16PB
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

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

Similar topics

16
by: noah | last post by:
Does PHP have a feature to associate Cookie sessions with a persistent database connection that will allow a single transaction across multiple HTTP requests? Here is how I imagine my process: I...
5
by: Derek Cooper | last post by:
I hope you can help me. I posted this in the microsoft sql server newsgroup a few days ago and got no response so I thought I'd try here. If I can provide any clarification I'll be glad to do so....
6
by: Tony Stoker | last post by:
I have a .Net web app that adds a record to a SQL database. After the user adds their record I want to have a link that will link them to their new record! The recordID is a AutoNumber in the...
2
by: Patrick Fisher | last post by:
Hi To display a message when a user attempts to edit a record in a multi-user environment where a forms Record Locking is set to Edited Record and another user is editing a record, is difficult...
4
by: Justin | last post by:
Using SQL server and VS.NET with ASP.NET/C# how can I retrieve the Key ID of a record right after creating that record in the same event? Do I need to close and reopen the connection and requery? ...
1
by: Simon | last post by:
Dear reader, With a combobox I can go to a selected record picked in the pull down list. And in the same time the record navigation field shows the selected record number from the...
0
by: KelHemp | last post by:
Greetings, I've been using this site for lots of access help in the past, and it's very helpful! I have a new complexity for you all. Reworking a form to record 70-80 years of oil production on...
1
by: kotresh | last post by:
In my sql how to get the previous record value... in table i m having the field called Date i want find the difference b/w 2nd record date value with first record date... plz any one help me to know...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.