[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work 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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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