MikeGinMn
asked on
Updating a field in MS Access
I'm trying to make a form that will look at 3 fields and put the best value in a 4th. I have the following
Private Sub Final_score_BeforeUpdate(C ancel As Integer)
max:iif([fin First Try]>[Fin Second Try],
iif([Fin First Try]>[Fin Third Try],[Fin First Try],
iif([Fin Second Try]>[Fin Third Try],[Fin Second Try],
[Fin Third Try])))
End Sub
Which displays the correct value but how do I store it in the field [Final score]
The [Final score] is used to calculate a place (would also like to automate this but that will be a different question)
Private Sub Final_score_BeforeUpdate(C
max:iif([fin First Try]>[Fin Second Try],
iif([Fin First Try]>[Fin Third Try],[Fin First Try],
iif([Fin Second Try]>[Fin Third Try],[Fin Second Try],
[Fin Third Try])))
End Sub
Which displays the correct value but how do I store it in the field [Final score]
The [Final score] is used to calculate a place (would also like to automate this but that will be a different question)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Also I have tried the function - depending on where I put it under events I get a wide array of errors - on google it says to put under Modules which I don't see in Access 2010 - so where would it go
ASKER
Ok I looked at the original query and copied the sql and pasted to a new one and renamed it to original one. I then used
If [Fin First Try]>[Fin Second Try] and [Fin First Try]>[Fin Third Try] then [Final score] =[Fin First Try]
If [Fin Second Try]>[Fin Third Try] and [Fin Second Try]>[Fin First Try] then [Final score] =[Fin Second Try]
If [Fin Third Try]>[Fin Second Try] and [Fin Third Try]>[Fin First Try] then [Final score] =[Fin Third Try]
under after update for each try and now it works - go figure - so since both suggestions lead me to the right conclusion how do I give each equal points
If [Fin First Try]>[Fin Second Try] and [Fin First Try]>[Fin Third Try] then [Final score] =[Fin First Try]
If [Fin Second Try]>[Fin Third Try] and [Fin Second Try]>[Fin First Try] then [Final score] =[Fin Second Try]
If [Fin Third Try]>[Fin Second Try] and [Fin Third Try]>[Fin First Try] then [Final score] =[Fin Third Try]
under after update for each try and now it works - go figure - so since both suggestions lead me to the right conclusion how do I give each equal points
ASKER
Hope I did that right
ASKER
[Final Score]=iif([fin First Try]>[Fin Second Try],
iif([Fin First Try]>[Fin Third Try],[Fin First Try],
iif([Fin Second Try]>[Fin Third Try],[Fin Second Try],
[Fin Third Try])))
in the after update as a event procedure I get a compile error; Syntax error with it highlighting the [Final Score]=iif([fin First Try]>[Fin Second Try], part of the procedure.
So I tried
If [Fin First Try]>[Fin Second Try] and [Fin First Try]>[Fin Third Try] then [Final score] =[Fin First Try]
If [Fin Second Try]>[Fin Third Try] and [Fin Second Try]>[Fin First Try] then [Final score] =[Fin Second Try]
If [Fin Third Try]>[Fin Second Try] and [Fin Third Try]>[Fin First Try] then [Final score] =[Fin Third Try]
as the after updat event procedure and I get a run time error of 30016 The field 'Final score' is read only