asked on

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

Rey Obrero (Capricorn1)

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

RUSS_EMI

ASKER

Must have done something wrong. Nothing happened. No error or value change. I'll try to debug with msgbox.
Russ

RUSS_EMI

ASKER

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

RUSS_EMI

ASKER

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

RUSS_EMI

ASKER

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

Rey Obrero (Capricorn1)

<<<<<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

RUSS_EMI

ASKER

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

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RUSS_EMI

ASKER

Thanks, I'll rewrite my code more closely to Northwind.
Russ