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
Solved

Trying to write value of a calculated field on a subform to a field in a table

Posted on 2013-06-17
5
264 Views
Last Modified: 2013-06-18
I have a form bound to a table.  On the form is a subform with a calculated field.  For now the field is named Text59.  When the main form closed I have this code in the onclose event but it isn't working.  I get a message that says it can't find the subform.

????


Here's the code:

CurrentDb.Execute "Update  tblProjectBudgetHeader set ConstructionBudget = " & Forms!subfrmProjectBudgetDetails.Text59 & " Where ID=" & Me.Combo113.Column(0), dbFailOnError
0
Comment
Question by:SteveL13
  • 3
  • 2
5 Comments
 
LVL 84
ID: 39254658
You refer to a Subform like this:

Forms!NameOfTheParentForm.NameOfTheSubformCONTROL.Form.YourControl

Note "NameOfTheSubformCONTROL" - this is the name of the CONTROL on the Parent form, and may or may not be named the same as the form you're using as a Subform.
0
 

Author Comment

by:SteveL13
ID: 39254675
I must have this totally messed up.

CurrentDb.Execute "Update  tblProjectBudgetHeader set ConstructionBudget = " & Forms!frmProjectBudget.Text59.subfrmProjectBudgetDetails.Me.Combo113.Column(0) & " Where ID=" & Me.Combo113.Column(0), dbFailOnError
0
 
LVL 84
ID: 39254679
Is "Text59" the name of the control that holds the calculated data?

If so then perhaps this:

CurrentDb.Execute "Update  tblProjectBudgetHeader set ConstructionBudget = " & Forms!frmProjectBudget.subfrmProjectBudgetDetails.Text59 & " Where ID=" & Me.Combo113.Column(0), dbFailOnError

And remember that it's typically NOT a good idea to store calculated data in a field. If you have a formula to calculate the value, then you should use that formula each time you need to work with the data. This recreates the data from the source, so if the source changes, you don't have to worry with updating the ConstructionBudget each time.
0
 

Author Comment

by:SteveL13
ID: 39254697
This doesn't work either:

CurrentDb.Execute "Update  tblProjectBudgetHeader set ConstructionBudget = " & Forms!frmProjectBudget.subfrmProjectBudgetDetails.Text59 & " Where ID=" & Me.Combo113.Column(0), dbFailOnError

Here is the latest (note that I changed ID to BudgetID which is the name of the field).
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39254949
Oops ... forgot the Form call:

Forms!frmProjectBudget.subfrmProjectBudgetDetails.Form.Text59
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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 …

840 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