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?
ETA: The query does work and update the field when I run it outside of the form.