Jun 18 2005, 09:41 AM
This is clearly a newbie question, right? I just haven't been able to work this out:
In setting up a form, I have created some calculation controls and concatonation controls, such as total, and a custom date code. And I want to write the calculated values into fields in the record. I'll use "Total" as an example:
I've got a control in a form called "Total" that uses a formula in its "Control Source" property to calculate a total. The form beautifully calculates the total after every change. Now I want to save that value into the record, in a field called "Total".
What's the easiest way to save the value of a calculation control to a field in the current record?
Thanks, I've been on this for hours.
Jun 18 2005, 09:48 AM
Welcome to Utter Access.
Do not save a calculated value to a table. You should calculate the value on the fly as needed.
Jun 18 2005, 09:49 AM
Welcome to Utter Access
just as Truitt said... Do not store calculated values -- that is the beauty of them -- they can be calculated anytime. Storing them risks the integrity of your data.
Just as you can make the total show up beautifully on a form, you can do the same on reports.
Jun 18 2005, 10:11 AM
OK, so saving "totals" may not be a good idea.
But I do want to save a concantonated custom code from the date something like this: = UserID & yymmdd & SubNumber. As before, the control is working great.
This one I'd really like to store in the record. Is there a "preferred method" for storing calculations in a record?
Jun 18 2005, 10:23 AM
instead of making the equation the ControlSource, the ControlSource of the custom code should be your fieldname. To calculate it, create a function behind the form
Private Function MakeCode()
me.CustomCode_controlname = null
if isnull(me.UserID ) then exit sub
if isnull(me.SubNumber) then exit sub
me.CustomCode_controlname = me.UserID & format(date(), "yymmdd") & me.SubNumber
on the AfterUpdate Event of UserID and SubNumber =MakeCode()
Jun 18 2005, 10:48 AM
Thanks. This looks great.
Jun 18 2005, 10:53 AM
just out of curiosity ... what is the purpose of this custom code?
Jun 22 2005, 08:18 AM
The below code worked, after tweaking and customizing. The purpose of this is to generate a PO number, made up of user ititials, date, and a sub-number. Looks like this:
Private Function MakeCode()
Me!PurchaseOrderNumber = Null
If IsNull(Me.EmployeeID) Then
strMsg = "You must pick a value from the Employee ID list."
strTitle = "Employee ID Required"
intStyle = vbOKOnly
MsgBox strMsg, intStyle, strTitle
Cancel = True
If IsNull(Me.ordersubnumber) Then End
Me!PurchaseOrderNumber = Me.EmployeeID & "-" & Format(OrderDate, "yymmdd") & "-" & Me.ordersubnumber
Jun 22 2005, 08:40 AM
As previously advised you should NOT be storing calculated values. There is no need for it since you can use the same expression to display the value whenever needed. You are storing the EmployeeID, the order date and the subnumber. Why waste an extra field on the PO Number? Whereever you need to display the PO Number, be it on forms, reports or in queries just use the expression:
= EmployeeID & "-" & Format(OrderDate, "yymmdd") & "-" & ordersubnumber
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here