?
Solved

Looking for code to UPDATE tables from ONE form - Access 2010

Posted on 2014-04-07
4
Medium Priority
?
2,521 Views
Last Modified: 2014-04-08
I have a continuous form that that displays multiple records and each record is its own line.   This form uses Table1 as its datasource. Each line/record has a field called QTY (from Table2)  and NEWQTY.  There is one calculation done in an AFTER UPDATE that takes QTY from Table 2 and subtracts SHOTS USED  from it-this new number is called NEWQTY.  I want to replace QTY in Table2 with the NEWQTY for each record (see attached JPG).  The form can have up to 20 records showing.

In the attached picture:
Name, Code,QTY - come from Table2
QTY - user inputs data
NEWQTY - calculated when CALCULATE button pushed.

Three things need to happen when the user CLICKS the CALCULATE button:
1:Calculate NEWQTY for each record - (I have this)
2:After calculate, replace QTY from Table 2 with NEWQTY for EACH/ALL records. - (need this_
3:ADD ALL current records to a different table - (I have this done)

When the user opens the form again to input data the new values for QTY should show i.e instead of 4 Buck the second user should see 3, the second user should see 15 00 instead of 25.

I need VBA code or code/query to make this happen and be transparent to the user.

Thanks for the help
Form-Data.jpg
0
Comment
Question by:juricta
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 12

Expert Comment

by:jkaios
ID: 39984914
In your CalculateButton_Click() event try this:

dim rs as recordset
set rs = CurrentDb.OpenRecordset("Select QTY from Table2")
rs.Edit
rs("QTY") = myForm.QTY
rs.Update
0
 
LVL 12

Accepted Solution

by:
jkaios earned 1400 total points
ID: 39984923
Private Sub CalculateButton_Click()

   Dim rs As DAO.Recordset
   Set rs = CurrentDb.OpenRecordset("Select QTY from Table2 where [Name] = 'Buck'")
   rs.Edit
   rs("QTY") = Me.QTY
   rs.Update

End Sub
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 39986647
First the lecture - storing calculated values is dangerous to your health and frequently leads to data errors because the underlying data was changed but the value was not updated.

OK - now that you know you are not supposed to do this, I will tell you how to do it.
Create a query that joins the two tables (you may need a left join) and use that query as the RecordSource for the form.  Pull the NewQty field from table 2.  If you have this column in table 1, you should probably delete it.

In the BeforeUpdate event of the Form, calculate the new value:

Me.NewQty = Me.Qty - Me.ShotsUsed.


If you want the user to see the change as soon as he tabs out of Me.ShotsUsed, then you need to put code into TWO places because you need to make sure the calculation happens if EITHER field changes.

Me.NewQty = Nz(Me.Qty,0) - Nz(Me.ShotsUsed,0)

The Nz() function MUST be used because when you perform this calculation in the AfterUpdate events of the individual controls, either control may be blank and you don't want to generate an error.

Currently you are trying to run this as a batch process and that is the wrong approach to take especially since you are depending on the user to press the Calculate button to make this happen.  What if he doesn't press the calculate button?  Then with your method, whatever rows had a value change now have invalid totals which is EXACTLY why I started with a lecture.  Storing calculated values requires extreme care to ensure that the values are ALWAYS, ALWAYS, ALWAYS updated to reflect the state of their constituent parts.
0
 

Author Closing Comment

by:juricta
ID: 39987437
I have to accept this as the answer as it was the first comment that worked.  Thank you for the help and excellent support
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses
Course of the Month11 days, 5 hours left to enroll

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question