473,324 Members | 2,193 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,324 software developers and data experts.

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

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 3766
ADezii
8,834 Expert 8TB
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
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 Expert 8TB
@Nick - The logical Event to capture the change in your Units Status would be the AfterUpdate() Event of the Form.
Apr 18 '11 #4
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.Execute strSQL, dbFailOnError

End Sub

This however does not do the trick.
Please help again
Nick
Apr 18 '11 #5
ADezii
8,834 Expert 8TB
  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
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 Expert 8TB
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, 116 views)
Apr 19 '11 #8

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

Similar topics

1
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...
49
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...
4
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
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...
145
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...
16
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
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...
11
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...
1
by: thiago777 | last post by:
How do you add components to a form during compilation? thx
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: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
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: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
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....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

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.