?
Solved

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

Posted on 2004-08-15
9
Medium Priority
?
239 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
[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
  • 6
  • 3
9 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 

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
 

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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

752 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