Solved

Updating a field in MS Access

Posted on 2012-04-08
6
312 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 250 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 250 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

687 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