?
Solved

Updating a field in MS Access

Posted on 2012-04-08
6
Medium Priority
?
313 Views
Last Modified: 2012-04-12
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)
0
Comment
Question by:MikeGinMn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
6 Comments
 
LVL 9

Accepted Solution

by:
macarrillo1 earned 750 total points
ID: 37821375
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
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 750 total points
ID: 37821397
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
 

Author Comment

by:MikeGinMn
ID: 37837032
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:MikeGinMn
ID: 37837288
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
 

Author Comment

by:MikeGinMn
ID: 37837471
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
 

Author Comment

by:MikeGinMn
ID: 37837484
Hope I did that right
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question