how to update field in table when the field is calculated from a form?

[uuthanh]

Hi Experts!

I have a one table and one form:

Table A
fields: X,Y,Z

Form B
fields: X,Y,Z (same fields as in table A)

In the form B: X value is manually entered, Y and Z are calculated by a function in module based on X value.

When I enter X value into the form, it of course automatically update its field in form B and also Table A. Field Y,Z got themselves calculated, and appear on form B. The problem is the value of Y,Z won't be updated in table A. I want field Y and Z value appear on table A automatically. How do I do this?

I know this sounds simple, but I got hard time figuring it out. Please help. Thanks so much!

 

[Darrylles]

Hi,

Why can't you bind the fields in the table for Y and Z to textboxes on the form?

You have got fields defined in the table for Y and Z - yes?

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[annsolomon]

Is there a reason you need to store the values of Y and Z in the table? I prefer to do my calculations in a query and base the form on that query.

For example, your query would have 3 values (X, Y:, and Z where:

X (manually entered by the user)
Y: (X+17)
Z: (X/2)

This way, Y and Z values are calculated every time you have the X record open in form view without having to actually store those two values in the table.

Ann

 

[Hasse]

uuthanh,

I sincerely agree with Ann.
'Never' store calculated values! Why should you want to store the calculated values Y & Z when you can have them all available through queries-forms-reports?
(a) export?
=> No good reason. You can use a query with calculated fields (with the same expressions you wrote in the form) for export
(b) used as a linked table in other databases
=> might be an acceptable reason to me, but an odd situation. Ann?
(c) very complex calculations of which results are used elsewhere in the application
=> Ann: is this is a valuable reason?
=> you need extra security to avoid X edits outside this form, as this should cause inconsistencies (Y no longer equalling Calculation Y(X))!
(d) ...?

BUT in case there is some obscure reason why you need to store the value, I should achieve this by... assuming you named the form text control for X 'txtX' AND fields Y & Z are part of the form recordsource...
1. Add the txtX 'AfterUpdate' property = Event Procedure
=> doubleclick to open the code window
2. enter following lines
me!Y = X*X 'fill in here calculation Y (X)
me!Z = X*X*X 'fill in here calculation Z (X)
Further, I should disable the Y & Z text controls to avoid confusion & unwanted changes to the calculated values.

Hans

 

[gusbrunston]

[tt]
Storing calculated values violates the Rules of Normalization and if any of the data is critical, if the database is going to used in a business after the present programmer has moved on, it should not be done.

I am not above "de-normalizing" when it suits me, but whenever I store calculated values, they are later "cleared" or recalculated and replaced. A user who cannot figure out how to store calculated values usually does not have the judgement to discern those particular circumstances where it is proper to store them.

Those who provide the procedure for storing such data may contribute to serious future problems for the institution or enterprise where the database will be used. Such establishments can become victims of well-meaning, but misguided Access users.

An alternative to storing calculated values is to keep time and date specific backups of particular tables. e.g., a price list table which changes frequently would alter the display of archived invoices if applied after price changes. Therefore, the price list table could be archived to be used with the appropriate batch of invoices.

I suspect that some questions about storing calculated values are handed out by instructors, and eventually end up on Tek-Tips.

On review, this post sounds rather pompous. I guess it is. [tt] Gus Brunston - Access2000[/tt] Intermediate skills.

 

[uuthanh]

Thanks so much for all your replies. I appreciate it.

X,Y,Z are defined in table A.

Actually, this is our company trouble ticket system. For each ticket, the tech support personnel enters the time X he/she spend on a call. The X value is only entered once. The complex calculation of Y and Z is stored in a function of module, and has couple hundred lines of code.

right now:
Y controlsource = FuncCalculateY () (function from module)
Z controlsource = FuncCalculateZ () (function from module)

See, when come to coding couple hundred line of code, I have no problem in VBA, but I do when getting into MS Access architechture and stuff.

Thanks guys. I guess I just keep trying and read more Access books.

 

[Hasse]

uuthanh,
I think that the conclusion is that you should build a query with (among eventual others) those two calculated fields:
Y = funcionY(X)
Z = functionZ(X)
This should be quite simple when you store them as public procedures in a module in the same database. In a further step, you can use this query to calculate totals per employee, project,... use it in reports,...
If you need more help on this, just drop another post. Otherwise: good luck,
Hans

 

[uuthanh]

Hi Hans,

Can you please explain it a bit more detail how to build a query?

right now, my functionY is calculated from FormB.X.value, similarly for functionZ. When I view FormB, there are values for field Y,Z. But when I view TableA, fields y,Z are empty.

I tried to create a new query, but look like MS-Access doesn't allow me to pull out Y and Z values from FormB.

Thanks so much again.

uuthanh

 

[Hasse]

uuthanh,

you should indeed leave the form and use the query as 'platform' for your calculations. To realise this,
(1) build the query with calculated fields using your VBA functions
(2) change the recordsource of the form in Queryname instead of TableName
(3) change the recordsource of the text controls on the form into the query field names

About how to build a query if you are not familiar at all with Access:
- go to query tab
- click 'new'
- select the tables you need in the dialog box; enter 'close'
- drag the fields you need in the form into the grid below
- enter two calculated fields using the VBA functions for Y & Z
- OK!

If the calculations are also used in other forms, reports, exports, further calculations, summations, analysis,... it might be wise to (1) build a query with ID's (for later linking) & (just) the calculations and (2) use this query (+ specific fields from other tables,...) in a second one used the recordsource for your form.

In the following lines, I present an example I quickly worked out based on your information. By the way:I went here one step further & based the calculation of Y & Z on two factors from different tables instead of one 'X'. Hope this didn't confuse you any further.
I don't think it will be necessary, but if you want & drop your e-mail in a post, I can mail it too.

Good luck & drop a line on the forum if it worked all right.

Happy newyear!

Hasse

ps Other readers: Even I've quite some experience, I'm no pro so if I make some crucial mistakes, please don't remain silent

= = = = = = = = = = = = = = = = = = = = = = = = =

TABLES

tblCALLS
ca_IDcall ca_Subject ca_Time ca_IDemployee ca_IDProject
1 Bug 1 30 1 1
2 Bug 1 45 2 1
3 Bug 2 10 1 1
4 Bug 3 15 1 2

tblPROJECTS
pr_IDproject pr_Name
1 Project One
2 Project Two

tblEMPLOYEES
em_IDemployee em_Name em_Wage em_Function
1 John Williams 100 Helpdesk
2 Bill Wates 250 Programmer

Remarks
ID = Autonumber
ca_Time in minutes

MODULE basCALCULATIONS

Option Compare Database
Option Explicit

'Calculation of Y based on X (= calltime & wage)
Public Function fY(calltime as Single, wage as Single)
fY = calltime * wage 'fill in here your own function
End Function

'Calculation of Z based on X (= calltime & wage)
Public Function fZ(calltime as Single, wage as Single)
fZ = calltime * wage * 2 'fill in here your own function
End Function

Query qryNAME
(SQL-view)
SQL = SELECT tblCalls.ca_IDProject, tblCalls.ca_IDcall, tblCalls.ca_IDemployee, tblCalls.ca_Time AS X, fY([ca_Time],[em_Wage]) AS Y, fZ([ca_Time],[em_Wage]) AS Z
FROM tblCalls INNER JOIN tblEmployees ON tblCalls.ca_IDemployee = tblEmployees.em_IDemployee;
(TABLE-view)
ca_IDProject ca_IDcall ca_IDemployee X Y Z
1 1 1 30 3000 6000
1 2 2 45 11250 22500
2 3 1 10 1000 2000

FORM
recordsource = query
= = = = = = = = = = = = = = = = = = = = = = = = =

ps I can't guarantee I'll be here for any further help. I've engaged myself recently in a rescue plan for one of the last unique Lambic beer breweries in Beersel (Belgium) which unfortunately recently closed. It's a very very (!) urgent case as the pub which makes a 2-unity with the brewery is at this very moment for sale. (We're going to look for Beer lovers, companies,... prepared to invest money in the project without the opportunity of expecting a direct financial return... no evident matter these days) Anyway... I hope the information above will help you out!

 

[uuthanh]

Hi Hans,

Thanks so much... I'm carefully reading your reply in a hope that can solve my problem.

Happy New Year!

Thanks again. Good luck on your mission!

uuthanh

 

[Hasse]

Rereading my previous post, I've got to apologise for the hurry in which I wrote it which made me forget words,... I hope it's clear enough anyway. - Hasse

 

[uuthanh]

Hi Hasse,

Your tip really helped me. My problem was resolved. Thanks so much.

Happy new year!

UUThanh

 

[Hasse]

Thanks UUTanh, it was a pleasure. See you around [santa2] (i.e. not wishing you anymore troubles ;-) ) - Hasse