Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

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

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
RUSS_EMI
Asked:
RUSS_EMI
  • 6
  • 3
1 Solution
 
Rey Obrero (Capricorn1)Commented:
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
 
RUSS_EMIAuthor Commented:
Must have done something wrong.  Nothing happened.  No error or value change.  I'll try to debug with msgbox.
Russ
0
 
RUSS_EMIAuthor Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
RUSS_EMIAuthor Commented:
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
 
RUSS_EMIAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
<<<<<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
 
RUSS_EMIAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
RUSS_EMIAuthor Commented:
Thanks,  I'll rewrite my code more closely to Northwind.
Russ
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now