Update Query Button Not Updating Field on Form

[Siena]

I've been using Access for nearly 8 years. I have an extensive database which I created from the ground up, which several of my coworkers use. I avoid doing sql, vb, or other coded bits if I can. Sometimes I need to use sql or vb for particular functions that I need in my database, so I can do it if I have to. My usual method is cut and pasting sql, etc. from help sites, changing the relevant parts for my database. So, while I'm better at understanding the coded functions of a database than I was when I started, I'm still not exactly advanced in those areas. So, if there's a way that I can fix this problem by using the menus, built-in access functions, or by changing properties, that would be excellent. If the solution lies in code, I can do it--but I'm going to need to know where to input that code. I can try to figure it out on my own, but any help that I could get would be much appreciated.

Here's the situation: I was asked to create a check request form which will print out check request report. Each record has 3 amount fields which need to be totaled in the Total Amount field. I know that keeping calculated fields in a table is inadvisable, but I'm not really sure how else to do this. I have an update query which adds the amounts in the 3 fields together, updating the Total Amount field. I put a button on my form which is supposed to run that query, and supposedly it is running, but it is not actually updating the field. Sometimes it does update on a couple of the records, but it is not consistent and I can't figure out why it just won't work.

I vaguely remember running a query at one point that ran calculations on the fly, always displaying an updated number, and I think I even included that result in a report. I can't find it now, and I can't remember how to do it. But perhaps that is a better solution than using an update query to update a field in my table. Ideally, when people enter amounts into the form, the total will automatically update and be included in the report. It would be nice if it would update on screen in the form--either automatically, or after they hit a calculate button. But even if I can't have that, the correct total will need to print on the report.

I just discovered one problem--if any of the 3 amount fields are blank, the query does not return a result in the total amount field. I set a default value of $0, which fixed that problem--but the form is still not updating when I hit the calculate button.

Anyone have any suggestions?

Thanks.

ETA: The query does work and update the field when I run it outside of the form.

[alansidman]

To get around the null value in a field, wherever you are doing your calculation, either in a form or query, wrap your expression in the NZ function and have it set to zero. Here is a short tutorial on the synatax.

MS Access: Nz Function

[meratigoerr]

Here's the situation: I was asked to create a check request form which will print out check request report. Each record has 3 amount fields which need to be totaled in the Total Amount field. I know that keeping calculated fields in a table is inadvisable, but I'm not really sure how else to do this. I have an update query which adds the amounts in the 3 fields together, updating the Total Amount field. I put a button on my form which is supposed to run that query, and supposedly it is running, but it is not actually updating the field. Sometimes it does update on a couple of the records, but it is not consistent and I can't figure out why it just won't work.

First, you are correct, storing the calculated value is not recommended. If all three values are in the record source of your forms and report, you can get a totaled value anytime you need to using =nz([FirstField],0) + nz([SecondField],0) + nz([ThirdField],0) (using the NZ function suggested by Alan)

Second. there's no reason it should not work from the form and then work outside of it, unless there was an error when you called it, or you were running it before the data in the form was saved. Since you don't say that you're getting an error perhaps the second is true. Try saving the record first. Then see if your Query will run properly.

[Siena]

First, you are correct, storing the calculated value is not recommended. If all three values are in the record source of your forms and report, you can get a totaled value anytime you need to using =nz([FirstField],0) + nz([SecondField],0) + nz([ThirdField],0) (using the NZ function suggested by Alan)

Second. there's no reason it should not work from the form and then work outside of it, unless there was an error when you called it, or you were running it before the data in the form was saved. Since you don't say that you're getting an error perhaps the second is true. Try saving the record first. Then see if your Query will run properly.

Thank you! That is exactly it. I had to save it first. I'm so used to everything being saved automatically that I never have to save data manually.

Thank you to both meratigoerr and alansidman.

[meratigoerr]

Thank you! That is exactly it. I had to save it first. I'm so used to everything being saved automatically that I never have to save data manually.

Thank you to both meratigoerr and alansidman.

You should be able to save it in the code that calls your query. use Docmd.runcommand accmdsaverecord before you call the query. then you don't have to save the record manually.

[Siena]

I don't know how to do that.

However--I need to learn. I have another thing that I need to learn how to use, so I'll be starting another thread. If I learn that, I bet I will also learn how to implement your recommendation.

Thanks!

[jadekq5]

just dropping by to say hello