#1
  1. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jan 2005
    Posts
    70
    Rep Power
    10

    Question Update Query Button Not Updating Field on Form


    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.
  2. #2
  3. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Feb 2008
    Location
    Lake County, IL
    Posts
    865
    Rep Power
    152
    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

    Comments on this post

    • Siena agrees : Thanks!
    Alan Sidman
    Office 2010, 2007, 2002

    If I helped you, then click "give rep" button in the lower left corner.
  4. #3
  5. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    58
    Originally Posted by Siena

    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.

    Comments on this post

    • Siena agrees : Thanks!
  6. #4
  7. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jan 2005
    Posts
    70
    Rep Power
    10
    Originally Posted by meratigoerr
    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.
  8. #5
  9. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    58
    Originally Posted by Siena
    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.
  10. #6
  11. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jan 2005
    Posts
    70
    Rep Power
    10
    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!
  12. #7
  13. No Profile Picture
    Registered User

    Join Date
    Mar 2010
    Location
    United States
    Posts
    11
    Rep Power
    0

    hey everyone


    just dropping by to say hello

Similar Threads

  1. Update query join tables and update field
    By pschneider in forum SQL Development
    Replies: 1
    Last Post: February 22nd, 2008, 12:09 PM
  2. Importing and updating a field with an update query
    By marlie in forum Microsoft Access Help
    Replies: 6
    Last Post: August 25th, 2006, 10:09 AM
  3. updating unbound field on form
    By evansc in forum Microsoft Access Help
    Replies: 3
    Last Post: November 7th, 2005, 08:31 AM
  4. Replies: 5
    Last Post: March 23rd, 2005, 10:22 PM
  5. Updating a form field based on previous field
    By dss08 in forum Microsoft Access Help
    Replies: 0
    Last Post: February 12th, 2004, 02:23 PM

IMN logo majestic logo threadwatch logo seochat tools logo