ok, its time to beg (calculating and updating from within a form)

[Eric6]

hi everyone

i'm starting to go nuts here
i know this is a long post but please bare with me, i'm desperate
i have a budget program to build for my boss
and this is my first time writting an Access program
(i'm only a student on a summer job)

this is the third week i've been working on this thing and i've come pretty far. I'm trying to make the program as simple as possible to use but its been making the development much harder...

The whole system is based on RO's (there is a specific RO number for each type of expense... ie: 050 is for travel expense, 221 is for consultant pay etc...)
The part of the program that is giving me problems
is when it comes to tracking consultant pay the user first selects RO 221 and is then taken to an entry form.

that form has the table where every RO entry is saved as its control source. (its called ROData) That table is filtered to display only the the entries corresponding to the selected RO (in this case 221)
the problem is, i have to display information from other
tables and queries in that form too...
For example, the user has to select from a drop down list the name of the consultant that is being paid. Once the name is selected, some information about the consultant is then accessed and displayed in the form using the DLookup function (it looks up a table called consultantinfo).
Then, the same form has to calculate how much money the consultant has been paid and display it in the form.
this is done by using another DLookup call, this time to a query that calculates the total amount of money that is paid...
This query checks in ROData and adds up every consultant's pay that has been commited (the commited attribute in ROData equals Yes) and is part of the current fiscal year.

i can display the consultant and pay information
but the real problem surfaces when i have to update or refresh the data entry form because some changes have been made.
For example, the total needs to be update if the user changes the ammount paid changes, if the ammount paid is commited or not, and if the consultant name is changed.

finally, i'm getting to the problem....
the results from the query are not automatically updated when the information in the form is changed. If i change the ammound paid, my total wont be updated until i change record and come back...
i tried to force the query to requery but that resulted in many error messages.
I was able to make it work by placing the line
Requery
when ever i needed to requery the query
but that makes my form return to its first record.
(this is very bad because the people that use this program probably wont notice it and end up changing the first record instead of the new one)
...so i saved the position of the record, requeried and then moved to the saved postion.
It works, but it causes the screen to flicker very badly as access rapidly moves from the first record to the new one

then someone on this board told me to use recordsets to
requery the query
this is the code she gave me (i adapted it to my program)
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Me!ID

Call RQMyQuery("accumulatedcosts&quot
Me.Bookmark = rs.Bookmark
//
Sub RQMyQuery(qryname As String)
On Error GoTo Err_RQMyQuery
Dim mydb As Database
Set mydb = CurrentDb
Dim rst As DAO.Recordset, intI As Integer
Set rst = mydb.OpenRecordset(qryname, dbOpenDynaset)
rst.Requery
rst.Close
Set mydb = Nothing

Exit_RQMyQuery:
Exit Sub
Err_RQMyQuery:
MsgBox Error$
Resume Exit_RQMyQuery
End Sub

now i'm a little lost, i've never used recordsets before
(in VBA at least, i have some experience with ASP)
now my form doesn't update the total (as if the query wasn't requeried) and even worse, if i change the employee name, ammount paid, or commited status (all of which are used in the query) more than 3 times i get an error

**Update or CancelUpdate without AddNew or Edit**

i have no clue how to fix this, but i have isolated the line that causes the error,
Me.Bookmark = rs.Bookmark

if you have any idea what causes this error,
if you have any idea how i could get the total to refresh,
if you have any idea how to refresh a query's results, or
if you have any idea how i could get the same results another way

please help

i'm more than willing to email the program to anyone
its not that big, only about 600K and i know i cant make it smaller by removing some working forms..

thank you in advance,
Eric

 

[zevw]

I Did not really analyze everything properly, but my idea would be use the DSUM function after update in the field where you enter new amounts, and update the field that shows the total. Do a me.refresh or me.requery I think this should work. Look up the help of DSUM in Access Help and they have some real god examples.

Let me know if this works, Good Luck;

 

[Eric6]

i dont believe it,
it works!!!

all i needed to do was use my normal
dlookup from my query
and place a me.refresh in front to it

it doesn't change records, and it updates the values in
the query

it works perfectly!
i can even get rid of all the db and recordset stuff

thanks!
Eric