Solved

How do I update a field from within a form based on the value of another field?

Posted on 2004-08-15
9
232 Views
Last Modified: 2012-06-22
I have these tables; Employee, Hand Receipt, HR Detail, and Inventory. I have created a HR Subform, HR form.
On the HR Subform I have HRDetail.ID, Inventory.ID, Inventory.Description, HRDetail.Quantity and Inventory.QuantityInStock.
I would like to autmatically update the Inventory.QuantityInStock after the HRDetail.Quantity is entered, both on the form and in the Inventory table.
I have tried using the Expression Builder w/ AfterUpdate (of Quantity), but I see no change.
Thanks,
Russ
   
0
Comment
Question by:RUSS_EMI
  • 6
  • 3
9 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 11803906
try this

Private Sub Quantity_AfterUpdate()    'this is the control on the form for HRDetail.Quantity
       me.QuantityInStock=Quantity.Value          'will place the value to the quantityin stock

       currentDb.Execute ("Update Inventory Set QuantityInStock=" & Me.QuantityInStock &" Where Inventory.ID= " & Me.InventoryID & ""),dbfailonerror

End Sub

or you could use the BeforeUpdate event of the textbox Quantity



0
 

Author Comment

by:RUSS_EMI
ID: 11807100
Must have done something wrong.  Nothing happened.  No error or value change.  I'll try to debug with msgbox.
Russ
0
 

Author Comment

by:RUSS_EMI
ID: 11807194
I have a simple question related to Access, not this topic.  
How do I display the Description, not the ID of a field in Combo Box on a form?
Table - Department
field1 = ID; autonumber
field2 = Description; text

I have the following set;
Column Source - Department (the field itself)
Row Source - Table/Query
Bound Column - tried both 1 & 2
Number of Columns - 2
Have set Column width / number of Rows

Thanks,
Russ
0
 

Author Comment

by:RUSS_EMI
ID: 11807355
Back to the original question / problem.  Typo in field name, corrected.  Now another problem - Upon trying to exit the record, an error occurs saying "another user has changed the record" giving me an option of not saving or putting the data in a clipboard.  If I use the clipboard, all is OK when I reopen the form / record, otherwise no data is saved on the Hand Receipt table.
Russ
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:RUSS_EMI
ID: 11807521
Here is exactly what I'd like to do.  Using Northwind.mdb as a template,  put the Products!UnitsInStock value in Quantity on the Orders Subform.  Then after update of record, modify Products!UnitsInStock to reflect the change (UnitsInStock - Quanity).
Thanks,
Russ
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 11809227
<<<<<How do I display the Description, not the ID of a field in Combo Box on a form?
Table - Department
field1 = ID; autonumber    <<<< you have two fields here, this the first
field2 = Description; text   <<<<< this the second

I have the following set;
Column Source - Department (the field itself)   <<<<< ' Clear this
Row Source - Table/Query           <<<<< this should be Row Source Type -Table/Query

Row Source - Select ID,Description From Department;
Number of Columns - 2
Bound Column - tried both 1 & 2      <<<< Set to 1 if you want bind the ID , 2 for desription
Column Widths 0;2                  <<<< this setting will not show the ID, only the Description

be back with the other problem
0
 

Author Comment

by:RUSS_EMI
ID: 11809762
capricorn1,
   Thanks for the minor problem fix.  Worked well.  Please use the Northwind.mdb as a basis for your other work, that way I can have something to use as a template.
I really appreciate your efforts,
Russ
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 11810052
using the Northwind.mdb

Private Sub Quantity_AfterUpdate()
'enter the quantity to test

'CurrentDb.Execute ("Update Products Set UnitsInStock=UnitsInStock -" & Me.Quantity & " where ProductID=" & Me.ProductID & ""), dbFailOnError
End Sub
0
 

Author Comment

by:RUSS_EMI
ID: 11810917
Thanks,  I'll rewrite my code more closely to Northwind.
Russ
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

706 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now