473,836 Members | 1,241 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How do you update units available on Table "Stock on Hand" from value entered on form

5 New Member
How do you update value "Units available" on Table STOCK ON HAND from a value entered on a form for attribute "Units In" on table UNITS IN. I need to update the "Units available" as soon as control is passed back after entering the "Units In" form. With "Units In" I need to Increase the value of "Units available" on table STOCK ON HAND. I also need to decrease the value in "Units Available" on table STOCK ON HAND when a value is entered on a form in attribute "Units Out" on table UNITS OUT.
Regards,
Nick
Apr 17 '11 #1
7 3800
ADezii
8,834 Recognized Expert Expert
In its simplest form, the Logic would be:
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3. If IsNull(Me![Units In]) Or Not IsNumeric(Me![Units In]) Or Me![Units In] <= 0 Then Exit Sub
  4.  
  5. strSQL = "UPDATE [STOCK ON HAND] SET [Units Available] = ([Units Available] + " & _
  6.                  Me![Units In] & ") WHERE [Unit_ID] = " & Me![Unit_ID]
  7.  
  8. CurrentDb.Execute strSQL, dbFailOnError
Apr 17 '11 #2
Nick Ferreira
5 New Member
Hi ADezii,
Thank you for the prompt reply. However I am not a programmer type, and created a Query to link the two tables (STOCK ON HAND and UNITS IN) and used that for the form where the "Units in" are captured.
The same for the Units out form. My question now is where do I copy and paste your solution to have the available units updated?
Regards,
Nick
Apr 18 '11 #3
ADezii
8,834 Recognized Expert Expert
@Nick - The logical Event to capture the change in your Units Status would be the AfterUpdate() Event of the Form.
Apr 18 '11 #4
Nick Ferreira
5 New Member
Hi again, sorry I am lost again!!!
I went into design mode, got the properties on the "Units In" attribute on the form and on After Update, created an event procedure that looks like this:

rivate Sub Number_of_Units _in_AfterUpdate ()
1. Dim strSQL As String
2.
3. If IsNull(Me![Units In]) Or Not IsNumeric(Me![Units In]) Or Me![Units In] <= 0 Then Exit Sub
4.
5. strSQL = "UPDATE [STOCK ON HAND] SET [Units Available] = ([Units Available] + " & _
6. Me![Units In] & ") WHERE [Unit_ID] = " & Me![Unit_ID]
7.
8. CurrentDb.Execu te strSQL, dbFailOnError

End Sub

This however does not do the trick.
Please help again
Nick
Apr 18 '11 #5
ADezii
8,834 Recognized Expert Expert
  1. Is the Form Field actually called [Units In]?
  2. Is your Primary Key Field actually named [Unit_ID], and is it Numeric? Does it exist on the Form?
  3. Are you receiving an Error Message, or is the Value simply not Updating?
  4. If you are receiving an Error Message, what exactly is it?
  5. Nick, you must be more specific in your explanation.
P.S. - When I get a chance, I'll create a Demo that will illustrate the basic Logic.
Apr 19 '11 #6
Nick Ferreira
5 New Member
The Primary key on all three tables are "Stock Code"(Text Field) , the STOCK IN and STOCK OUT tables additionaly have a sequence number added to Stock Code as primary key.
The actual field on the form for the STOCK IN is "Number of Units In" and for the STOCK OUT is "Number of Stock Out". The field I am trying to update on STOCK ON HAND table is "Available Number of Unts"

With the code I sent you I dont receive an error, just nothing happens
Apr 19 '11 #7
ADezii
8,834 Recognized Expert Expert
I created a Demo (Attachment) for you that should definately point you in the right direction.
Attached Files
File Type: zip Units.zip (19.8 KB, 117 views)
Apr 19 '11 #8

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

Similar topics

1
3146
by: Danny Anderson | last post by:
Hola, PHP folk! I have a php page that contains a self-processing form. The form holds search results. The search terms originally came from the previous page, but the user can repeatedly refine the results on the page in question until the target item can be found. This search refinement is where the self-processing form comes to play. The search results are listed in a table with a radio button at the end of each row. What I...
49
2885
by: Ville Vainio | last post by:
I don't know if you have seen this before, but here goes: http://text.userlinux.com/white_paper.html There is a jab at Python, though, mentioning that Ruby is more "refined". -- Ville Vainio http://www.students.tut.fi/~vainio24
4
7684
by: Flapper | last post by:
Help please, Have a situation when converting from Oracle SP's to SQL SP's. The old oracle cursor was roughly as follows CURSOR cur_rsStock IS select * from (select StockRowId, CategoryId
235
11833
by: napi | last post by:
I think you would agree with me that a C compiler that directly produces Java Byte Code to be run on any JVM is something that is missing to software programmers so far. With such a tool one could stay with C and still be able to produce Java byte code for platform independent apps. Also, old programs (with some tweaking) could be re-compiled and ported to the JVM. We have been developing such a tool over the last 2 years and currently...
145
6360
by: Sidney Cadot | last post by:
Hi all, In a discussion with Tak-Shing Chan the question came up whether the as-if rule can cover I/O functions. Basically, he maintains it can, and I think it doesn't. Consider two programs: /*** a.c ***/
16
10835
by: Chris | last post by:
Looking at some code I see a declaration inside a function like static const string s("some string"); Does the static serve any purpose here?
1
1744
by: noor khanan | last post by:
Hi every boy, I am a beginner. I have a project in access 2003.I have problem to update nonbase table. i have a main form and one subform in it.i want to update my non base table condition in subform textbox. My problem summary is that. when i purchase some item, against its my stock table is updated.For this solution i use VBA code and Query but still i not update the non base table. >>>Help me in code or query or give the best suggestion.
11
7709
by: hamiltongreg | last post by:
I am new to Java and am having problems getting my program to compile correctly. My assignment is as follows; Choose a product that lends itself to an inventory (for example, products at your workplace, office supplies, music CDs, DVD movies, or software). • Create a product class that holds the item number, the name of the product, the number of units in stock, and the price of each unit. • Create a Java application that displays the...
1
983
by: thiago777 | last post by:
How do you add components to a form during compilation? thx
0
9813
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9660
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10535
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
7778
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6976
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5645
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5815
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4446
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3106
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.