Total Bound Field in a Form in Access 2010

[ryanmce92]

Hi All,



Thanks for taking a look.

I have a form to add new suppliers, each of these new suppliers will have a rating between 0-3 based on 3 different variables, delivery, quality and experience, there is also an overall rating field.

The overall rating field will be a total of the other 3 ratings, I would like the overall field to be bound to the table and automatically add up the three variables, likewise if one of the values were to change it would automatically recalculate.

It may be something simple I am missing but this has been bugging me for a couple of days, any help would be greatly appreciated, cheers guys!!

[CJ_London]

you don't need it to be bound since it is a calculation so does not need to be stored

[June7]

As Ajax says, summing these 3 fields is a simple calculation that can be done when needed - in query or textbox. Calculations are dynamic and will update when the raw data is changed. Saving the calculated result would require code (VBA or macro) and would be bad design in this case.

[ryanmce92]

I need this to be bound as I want it saved in the table, This will give my end users the chance to see an overall rating.

[CJ_London]

your users should not be looking at tables directly but via a form or report - you put the calculation there in an unbound control or bind the control to a calculation in the form/report recordsource. One of the reasons is because of this

likewise if one of the values were to change it would automatically recalculate.

you'll need lots of additional VBA/Macro code to manage that, whereas calculating when you require the value requires no code at all

Access is a database, not a glorified spreadsheet. The rational is to store values once and once only. There are only very rare occasions where you might store a calculated value - such as a tax amount in an invoice - but the point is, once calculated, it never changes because (in this situation) you need to know what the value was and you don't want to change it if the parameters (such as the tax rate) change.

[ryanmce92]

They will be viewing the value in either excel or datasheet view after the results appear in a query?. I have a search form and the buttons just run a query based on these search results.

[June7]

Does not alter advice already given.

[CJ_London]

what is the sql to your query? that is where you would add the calculation

[ryanmce92]

Hi All,

Thanks for your help, Ajax, you gave me a different idea, for handiness I entered code and attached it to a button, this adds the three totals up and allows the field to still be bound to the table. e.g

Private Sub Command248_Click()
[tboRating] = [cboDelv] + [cboQuality] + [cboExp]
End Sub

Hopefully the button will be easy enough for my end users, thanks for all your help folks!!

[June7]

And if users forget to click the button? Again, the best approach is to calculate this sum when needed. However, if you must save then it is better not to rely on users to do it. Code can go in the AfterUpdate event of each of the input controls or perhaps in the BeforeUpdate event of the form.