[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
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
?
241 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

649 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