Update a record with Calculated Field

[Bazza_Perth]

Hi Guys,

I just have a small problem that I just have not been able to solve nor find a succinct answer for on any of the boards I have looked in. What I have done is put together an Access form to calculate a lot of financial details for a project. I have NPV, IRR, Payback, Cashflow calcs and all sorts of things being generated according to details listed in a few subforms. What I need to do now is to pass a calculated number, back into a record on my main table for safekeeping.

Is there any simple way (I'll do it the hard way if that's the only option available though) for me to do this. I just need to grab a calculated number and drop it into a record - is there any way to do this other than asking my users to transcribe the number over!?

Hope someone can help,
Bazza

 

[Andrew Fergus]

Hi Bazza

This can definitely be done.

It is going to take a bit of setting up but I have tested it and it works fine. However I'm going to have to assume that you have the calculated value on the form and that value is calculated on a part of the form where you have access to the unique ID of the record that you need to pass the value through to. On that basis :

Go into your Form Design -> Right click the calculated value box -> Properties -> Name -> Give the box a name (I will use "Your Box Name" for the rest of this post, without the quotes). Check also that the ID field has a name - I have assumed "Your ID Field Name". Save the changes.

New Query -> Add the table that you want to add the calculated value to -> Ok; Add the ID to the query -> untick "Show" -> enter the criteria [Forms]![Your Form Name]![Your ID Field Name] ; Add the field into which you want to copy the calculated value; Save the query.

New Macro : 1st line = GoToControl -> Your Box Name;
2nd line = RunCommand -> Copy;
3rd line = Open Query -> Your Query Name -> Datasheet -> Edit;
4th line = RunCommand -> Paste;
5th line = Close -> Query -> Your Query Name -> No
6th line = StopMacro
Save the macro.

Form Design : beside the calculated value box (in the detail section of the form), create a new command button (using the toolbox), cancel the wizard, go into the properties of the command button -> find "On Click" and select Your Macro Name. Save the form.

This should do the trick - a little clunky but it works.

HTH, Andrew.

 

[Bazza_Perth]

Thanks

Hi Andrew,

Clunky or not, that works a treat and does exactly what I need so is a great solution as far as I'm concerned

I was heading down the path of a macro to copy --> paste but would have ran into trouble at setting up the query side of it. Thanks heaps for the help, greatly appreciated.

Cheers,
Bazza

 

[ATMlady]

Thank you thank you thank you, Andrew Fergus!!! I used your copy - paste trick in an upon change event in another field and it was just the ticket!

 

[Andrew Fergus]

You're welcome. I don't recall answering that thread recently and then I saw the date was 2005! Kudos to you for using the search tools!

Knowing what I know I would probably do it differently nowadays but if it works then great......

Cheers
Andrew