Very simple expression question

[Stephen Davies]

Hi,

I'm making a quick database and need to use an expression. I have no idea what I'm doing.

I would like the user to be able to input a number in one field, have Access perform a simple calculation on it (*.34) and spit the result back out into another text box and save it in the database.

I'm trying to use the "AfterUpdate" thing but I have no idea on syntax or anything.

Thanks in advance.

[NeoPa]

Stephen, I'm glad you asked.

It's not simple and it's not recommended. Let me start from the beginning...

Calculating the value is very simple. Expressions can be added into a query or a form easily enough. What is strongly recommended against (See Normalisation and Table structures) is saving the value of a calculation that is repeatable. If B is always equal to A * 0.34, then B should never be stored. It should always be worked out on the fly.

The only time a calculation should be stored is when you want to make a note of the historical condition ==> B = A * 0.34; A changes; B should not change to reflect the new value of A.

The expression needed in the query is simply :

B: [A] * .34

This will make both [A] and [b] available to your form.

Welcome to Bytes!

[Stephen Davies]

NeoPa, thanks for the help and the quick reply! I am completely new to Access and as per usual I'm having to teach myself rather than being sent on a course :(

So I apologise for this but, where do I put the equation? The calculation could be done on the fly I suppose. It's for calculating how much a print costs. So I have this:

BW_cost: [BW_Clicks] * 0.34

Typed into the "After Update" property of the BW_cost input box but it doesn't work.

[NeoPa]

Is [BW_Cost] an existing field in your table?

This is an important question. You could say the crux of the matter.

[Stephen Davies]

In a word, yes.

[NeoPa]

Clear answers are always good :)

Did you understand the comments I made in my post #2? About not storing data that can reliably be worked out?

The technique I recommend relies on the control being unbound you see. A calculated value cannot be applied to a bound control (which you would have if you were saving the value in a field).

[Stephen Davies]

I think I understood, yes. I didn't realise you couldn't apply a calculated value to a bound control. So the thing to do is to "un-bind" the control and try again. I will have a go at that tomorrow as I am now going home for the day. Thanks very much for your help.

[NeoPa]

No worries.

Be aware. This means the value will no longer be stored in your table (which is as it should be). It can always be available when needed though (Hence it's the ideal and recommended solution).

[Stephen Davies]

Hi again,

Right, I've been struggling with this on and off all day and think I'm very nearly there!

I now have two calculations working (hurrah!) and then another one that adds the two together (double hurrah!). The user types in a number and Access performs the necessary calculations. I have done this by typing:

=[B+W Clicks]*0.034

Into the control source property of an unbound text box and then:

=[b]+[C]

Into another unbound text box. So far so good.

The problem is that in order to see the calculations I have to exit the form and then go back in. How can I make it update straight away?

Thanks in advance (again). :)

[NeoPa]

In my test version it updates immediately. I can't imagine what you may be doing differently to cause this not to occur.

Perhaps a detailed description of what you have on your form might help. Otherwise we could look at getting a copy of your database posted. See below for instructions I prepared earlier :

When attaching your work please follow the following steps first :

1. Remove anything not relevant to the problem. This is not necessary in all circumstances but some databases can be very bulky and some things do not effect the actual problem at all.
2. Likewise, not entirely necessary in all cases, but consider saving your database in a version not later than 2003 as many of our experts don't use Access 2007. Largely they don't want to, but some also don't have access to it. Personally I will wait until I'm forced to before using it.
3. If the process depends on any linked tables then make local copies in your database to replace the linked tables.
4. If you've done anything in steps 1 to 3 then make sure that the problem you're experiencing is still evident in the updated version.
5. Compile the database (From the Visual Basic Editor select Debug / Compile {Project Name}).
6. Compact the database (Tools / Database Utilities / Compact and Repair Database...).
7. Compress the database into a ZIP file.
8. When posting, scroll down the page and select Manage Attachments (Pressing on that leads you to a page where you can add or remove your attachments. It also lists the maximum file sizes for each of the allowed file types.) and add this new ZIP file.

It's also a good idea to include some instructions that enable us to find the issue you'd like help with. Maybe some instructions of what to select, click on, enter etc that ensures we'll see what you see and have the same problems.

[Stephen Davies]

I am running Access 2007 if that makes a difference. If I try to save it into an earlier format and the form doesn't go with it, just the table.

[NeoPa]

I'm not sure what you're saying Stephen. Let me see if I can clarify.

2007 databases are not something I can even open, so I won't be able to help unless it's regressed at least to 2003.

I don't understand why a form would not be available after regressing. I've not heard that stated before.

Have you followed all the instructions? I must admit asking about posting as 2007 leads me to believe perhaps you haven't. Please read through them all. The issue may be a compilation one, in which case we should deal with that directly rather than trying to work out what other issues may result from a database that is already known to fail to compile.

[Stephen Davies]

I found the form. It was hiding, sorry about that. I will have another go at this tomorrow. Again, I appreciate all your help and patience.

[NeoPa]

No worries. Tomorrow is fine :)

[Stephen Davies]

Morning. I have been through your instructions and attached the file. I've looked all over Google for a solution too but have been unable to find one. Very grateful for any help you can give. Thanks.

[NeoPa]

There is no file attached Stephen :(

[Stephen Davies]

That's weird, I definitely uploaded it. Clicked on "Manage Attachments" etc. I'll try again. It's uploading now.

[NeoPa]

It's certainly there now Stephen. I cannot download when in the office but I'll look from home later :)

[Stephen Davies]

You're a diamond. Thank you.

[Stephen Davies]

>>>> Just thought I'd *bump* this up in the hope someone might have an answer. :)

Hi again,

Right, I've been struggling with this on and off all day and think I'm very nearly there!

I now have two calculations working (hurrah!) and then another one that adds the two together (double hurrah!). The user types in a number and Access performs the necessary calculations. I have done this by typing:

=[B+W Clicks]*0.034

Into the control source property of an unbound text box and then:

=[b]+[C]

Into another unbound text box. So far so good.

The problem is that in order to see the calculations I have to exit the form and then go back in. How can I make it update straight away?

Thanks in advance (again). :)

[NeoPa]

Sorry Stephen. I'm glad you bumped.

I thought I had 3 threads last night that I needed to look up as they had outstanding work to do but were no longer flagged as I'd read the latest post already. I had a hunt when I was home but only found the other two I'm afraid.

I'll look at this over the weekend for you. Again, sorry.

[NeoPa]

I'm sorry Stephen. I've downloaded the database now, but your instructions are missing.

Something to do with [b] & [C] but I couldn't find any references to them in the database.

If you notice the text at the bottom of post #10 you'll see I suggest posting something so I can find your problem. I think you still need to do that if I'm to be of any more help.

[Stephen Davies]

Hi, thanks for your continued patience. Thanks to your help I've very nearly managed to get this working.

Basically, in the form "2010-2011 Data" there are three tabs. In the third tab, "Admin" there are two boxes, labelled "B+W Clicks" and "Colour Clicks".

Entering a number in either of those two boxes calculates a cost which appears in "B+W Click Cost" and "Colour Click cost" respectively.

These two are then added together and the result appears in "Total Click Charge".

My problem is that in order for these calculations to actually take place, you have to come out of the form and go back in. I would like it to update straight away but I have no idea how to achieve this.

Thanks.

[NeoPa]

I don't have the db with me here atm so can I ask you now, what are the actual names of the TextBox controls (or if they are the names you've given, then what labels are near that I will recognise them by)?

[Stephen Davies]

In the form "2010-2011 Data" there are three tabs. In the third tab, "Admin" there are two boxes, labelled "B+W Clicks" and "Colour Clicks". These are the two text boxes that the user inputs a number into.

Entering a number in either of those two boxes calculates a cost which appears in "B+W Click Cost" and "Colour Click cost" respectively.

[NeoPa]

I'm sorry Stephen. I got tied up till nearly 02:00 this morning on another, quite complicated, issue for a friend here.

I won't be around much tonight either, but tomorrow I have booked as holiday so I'll give it some serious attention then.

[Stephen Davies]

That would be great. But there's no hurry, please don't put yourself out too much.

[NeoPa]

This isn't going to be good news for you Stephen, but it works perfectly for me!

As soon as I leave either of the two controls (after changing the value of course) the related calculated control updates perfectly. I made no changes first, it just ran and worked.

I suspect there may be something about your Access setup that is causing the difference, but I can't think of anything.

BTW your label shows .34p for the clicks but the calculation uses 0.034 (IE. 3.4p).

[Stephen Davies]

Bugger. I wonder what the problem could be then. I am using Access 2007 but I can't see why that would be the problem. I only just installed it using the "standard install" option so can't have changed anything since then. guess I'll contact Microsoft tech support. Thanks for all your help.

Thanks for the tip re: the calculation too :).

[Stephen Davies]

Just one last idea. When you say you "ran" it. Do you mean you opened the database, opened the form in form view and it worked or did you export it in some way?

[NeoPa]

I'm sorry to say it was definitely the former. I just opened the form and tested. I was expecting to see what you described, but it updated immediately. It was only afterwards I even looked to see whether you had implemented it as a formula within the unbound controls (as indeed you had) or had effected it with code in the AfterUpdate() event procedure of the bound controls.