UPDATE query - weird problem (involving a form) (1 Viewer)

[bagurk]

In MS access, i am using an update query, which runs when i enter data into a form.

the update query WORKS (updates the desired table), but only when i close the form, open it again, and enter a NEW value. it then updates to the old value i typed in.
if i re-open it again, and type in a new value again, it will update the table to the second value i entered... and so fourth

(ie. it only updates when i re-open the form and type in a new value, in which case it updates to the the value i entered the time before.)

in the UPDATE query, i am using the DSum function multiple times, to update about 6 different fields.

this is the query:


UPDATE tblPlayer SET tblPlayer.goals = DSum("[goals]","tblPlayerMatchStats","[name]=Forms![editplayermatch]![txtname]"), tblPlayer.shots = DSum("[shots_on_goal]","tblPlayerMatchStats","[name]=Forms![editplayermatch]![txtname]"), tblPlayer.assists = DSum("[assists]","tblPlayerMatchStats","[name]=Forms![editplayermatch]![txtname]"), tblPlayer.penalties = DSum("[penalties]","tblPlayerMatchStats","[name]=Forms![editplayermatch]![txtname]"), tblPlayer.mvp_points = DSum("[mvp_points]","tblPlayerMatchStats","[name]=Forms![editplayermatch]![txtname]")
WHERE (((tblPlayer.name)=[Forms]![editplayermatch]![txtname]));


(the database is for a hockey league for ur information

so, all i want to fix, is the way it updates. i want it to update correctly WHEN i enter the value into the form.

any help is much appreciated!

 

[Rich]

You seem to be storing the results of a summed field instead of calculating when required. Is there some reason for this?

 

[bagurk]

i dont know whats going on ...

im fairly new to access, and all i want it to do is just update the table fields when the query is ran... i dont know why it isnt doing it, do you know why?

 

[bagurk]

please help me!!!!

i am still getting this same problem...!

 

[Jon K]

It's really a strange behaviour.

Where do you run your update query from? Try running it from txtName's AfterUpdate event, or add a command button and run the query from its click event.

 

[RV]

Try using Me.Refresh (assuming you're using an event to trigger your UPDATE query).
And...don't panic, keep to the issue, answers questions asked to help us guide you.

As Rich asked before, is there a reason why you're storing calculated data in a table?
You shouldn't and you needn't....

RV

 

[bagurk]

is there any reason why i shouldnt be using tables, with calculated data?....

i already run the query from the after update event, on some text boxes on the form...

how do i do the Me.Update command RV ?

i will play with it a bit more when i get home this afternoon

thanx for your assistance

 

[Jon K]

"i already run the query from the after update event, on some text boxes on the form..."

Don't run the query from more than one text boxes, or it will be run multiple times.

Run it only in the text box txtName's AfterUpdate event. In this way, the query is run once immediately when a name is typed in and the user presses ENTER or moves the cursor away from the text box.


As Rich and RV have pointed out, try not to store any calculated data in the tables unless you have a reason for doing it. In your case, it seems updating table tblPlayer is unnecessary because you can actually obtain the up-to-date data at any time directly from table tblPlayerMatchStats with a select query:

SELECT Name, sum(Goals) as NumOfGoals, sum(Shots_on_goal) as NumOfShots, sum(Assists) as NumOfAssists, sum(Penalties) as NumOfPenalties, sum(MVP_Points) as NumOfMVP_Points
FROM tblPlayerMatchStats
GROUP BY Name

You can use the result of this query as if it were a table, e.g. to produce your reports.

 

[bagurk]

thanx a heap for the help guys

i took your advice, and made the database only calculate the values when it needs to, instead of storing them in a table.

all sorted! thanx again