Solved

Updating a field in MS Access

Posted on 2012-04-08
6
307 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
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

911 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now