Form Formulas and Storage of Data to Table

[bkf7911]

I have a text box on a form that calculates the value of other text boxes on the form, and displays the value.

Currently, the control source of this text box = (NZ([Text162])+NZ([Text164])+NZ([Text165])+NZ([Text172])+NZ([Text173])+NZ([Text177])+NZ([Text182])+NZ([Text184])+NZ([Text186])+NZ([Text188])+NZ([Text190]))

Normally, to store a value in a field, the control source references that field. How can i save the results of this equation to a field in the table that is the recordsource of the form??

 

[lameid]

Generally this is a bad idea. If you have separate fields it is because they are seperate attributes of the thing your record represents. That means they should not be combined in the table. You can make a similar expression in a query if you want to display the information.

If the controls are unbound (not saved) then you may be ont he right track.

The amperstand (&) is more typically used to concatenate text than the plus (+). While technically it does not matter if they are all text, if you had numbers it could add them.

When using controls, it is typical to name them rather than accept the Access default names.

Beyond that you need Code that saves puts the value in.

Something as simple as the following will do it.

Me!FieldName = (NZ(Me![Text162]) & NZ(Me![Text164]) & NZ(Me![Text165]) & _ 
    NZ(Me![Text172]) & _ 
    NZ(Me![Text173]) & NZ(Me![Text177]) & NZ(Me![Text182]) & NZ(Me![Text184]) & _ 
    NZ([Me!Text186]) & NZ(Me![Text188]) & NZ(Me![Text190]))

You might do this on the click event of a button or on some other event depending on when you want to do it.

 

[bkf7911]

I'm not concatenating. I'm calculating.

The data in Text162 is a score for a line item. The same with Text164. I'm calculating this score in a third text-box. I need to then store the calculated score in the table, in the field i choose.

 

[lameid]

Use plusses then but I think you want the second parameter of NZ to be 0. As it is it returns a zero length string instead.

 

[bkf7911]

its calculating fine. i just need to be able to store that calculated value in field.

it looks like the code you've written would do that, but what event-property should it be set to? the value in the calculated field should changed based upon a change in the text-boxes that make up that calculated field. the

 

[lameid]

It sounds like you want the Forms's Afterupdate event.

 

[bkf7911]

Thanks. I'll try this once i can breathe. Should be able to provide an update by Tuesday

 

[TheAceMan1]

How are ya bkf7911 . . .

Be aware: calculated controls are dependent on [blue]Tab Order[/blue] ( View - Tab Order!)

If your assigning a result before [blue]Tab Order[/blue] is complete, erroneous results will always be obtained!

Check the [blue]Tab Order[/blue] of all calculations before the assignment is made! . . .

[blue]Your Thoughts! . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[JoeAtWork]

Aceman - I created a test form and did not find that tab order affects it in any way.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.Calc = Nz(Num1, 0) + Nz(Num2, 0) + Nz(Num3, 0)
End Sub

I first tried with the Calc field set to tab index = 0, I then tried setting it to tab index = 2. No matter which control I was in when I saved the record, it always calculated the current values of Num1, Num2, and Num3.

BTW, putting this code in the AfterUpdate event resulted in not being able to move off the record, as setting the Calc field re-dirtied the record.

 

[ProgramError]

Do you want to store the result when a change is made, when you move onto the next record or at will by pressing a command button?


Ian Mayor (UK)
Program Error
Always make your words sweet and nice. Because you never know when you may have to eat them.

 

[TheAceMan1]

[blue]Aceman - I created a test form and did not find that tab order affects it in any way.[/blue]

If I remember (searching my library for the text), the tab order comes into play when you have [blue]controlsource equations dependent on other textbox controlsource equations![/blue].

Not sure if this was the case in this thread, but just in case . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886