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(Cancel 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)
MikeGinMnAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

macarrillo1Commented:
Have you tried a direct assignment Or Setting the field to the if statement?

[Final score]=max.value

or you could create a function:

Function FinValue(firstval as  integer, secondval as integer, thirdval as integer)as integer
dim FinVal as integer

if firstval>secondval then
   if firstval > thirdval then
      FinVal=firstval
   else
      FinVal=thirdval
   end if
else
   if Secondval > thirdval then
         FinVal = Secondval
   else
         FinVal=thirdval
   end if
end if
return FinVal
end function

Then you would set the default value of [Final Score]=FinValue([fin First Try], [fin Second Try],[fin ThirdTry])
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dqmqCommented:
To assign the result of that expression to another field, do like this in your procedure.

[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])))


Then call the procedure in the after_update event of each of the three fields.
0
MikeGinMnAuthor Commented:
Ok when I try

[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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

MikeGinMnAuthor Commented:
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
0
MikeGinMnAuthor Commented:
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
0
MikeGinMnAuthor Commented:
Hope I did that right
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.