auto populating fields with conditional calculations

  • Thread starter Thread starter Frankie via AccessMonster.com
  • Start date Start date
F

Frankie via AccessMonster.com

I've put the following code on the AfterUpdate Event of a control textbox
[SRBaseHoraire] in order to populate 2 fields [SalaireHoraire] and
[QuotePartT] based on the value of another field [TreiziemeMois],all
located on the same Form Mission, which doesn't work :

If Me!TreiziemeMois = "Compris" Then
CurrentDb.Execute "UPDATE T_Missions SET " _
& "SalaireHoraire = Me!SRBaseHoraire," _
& "QuotePartT = Sum(Me!SRBaseHoraire-(Me!SRBaseHoraire*12/13))
WHERE IDMission = " _
& Me!IDMission, dbFailOnError
ElseIf Me!TreiziemeMois = "Non Compris" Then
CurrentDb.Execute "UPDATE T_Missions SET " _
& "SalaireHoraire = Sum(Me!SRBaseHoraire*1.12)," _
& "QuotePartT = Sum((Me!SRBaseHoraire*1.12)-Me!SRBaseHoraire) WHERE
IDMission = " _
& Me!IDMission, dbFailOnError
ElseIf Me!TreiziemeMois = "Non Applicable" Then
CurrentDb.Execute "UPDATE T_Missions SET " _
& "SalaireHoraire = Me!SRBaseHoraire," _
& "QuotePartT = Null WHERE IDMission = " _
& Me![IDMission], dbFailOnError
End If

Can someone please help me ??
Thanks in advance
MS Access 2003
 
Frankie,

Your mistake is that you have your form control references enclosed in
the quotes as parts of the text string, so Access can't tell they are
actually references, and ends up treating them as just parts of the
tesxt string. Change your code to:

If Me!TreiziemeMois = "Compris" Then
CurrentDb.Execute "UPDATE T_Missions SET " _
& "SalaireHoraire = " & Me!SRBaseHoraire & "," _
& "QuotePartT = Sum(" & Me!SRBaseHoraire & "-(" _
& Me!SRBaseHoraire & "*12/13))" _
& " WHERE IDMission = " & Me!IDMission, dbFailOnError
ElseIf Me!TreiziemeMois = "Non Compris" Then
CurrentDb.Execute "UPDATE T_Missions SET " _
& "SalaireHoraire = Sum(" & Me!SRBaseHoraire & "*1.12)," _
& "QuotePartT = Sum((" & Me!SRBaseHoraire & "*1.12)-" _
& Me!SRBaseHoraire & ")"
& " WHERE IDMission = " & Me!IDMission, dbFailOnError
ElseIf Me!TreiziemeMois = "Non Applicable" Then
CurrentDb.Execute "UPDATE T_Missions SET " _
& "SalaireHoraire = " & Me!SRBaseHoraire & "," _
& "QuotePartT = Null WHERE IDMission = " _
& Me![IDMission], dbFailOnError
End If

HTH,
Nikos
I've put the following code on the AfterUpdate Event of a control textbox
[SRBaseHoraire] in order to populate 2 fields [SalaireHoraire] and
[QuotePartT] based on the value of another field [TreiziemeMois],all
located on the same Form Mission, which doesn't work :

If Me!TreiziemeMois = "Compris" Then
CurrentDb.Execute "UPDATE T_Missions SET " _
& "SalaireHoraire = Me!SRBaseHoraire," _
& "QuotePartT = Sum(Me!SRBaseHoraire-(Me!SRBaseHoraire*12/13))
WHERE IDMission = " _
& Me!IDMission, dbFailOnError
ElseIf Me!TreiziemeMois = "Non Compris" Then
CurrentDb.Execute "UPDATE T_Missions SET " _
& "SalaireHoraire = Sum(Me!SRBaseHoraire*1.12)," _
& "QuotePartT = Sum((Me!SRBaseHoraire*1.12)-Me!SRBaseHoraire) WHERE
IDMission = " _
& Me!IDMission, dbFailOnError
ElseIf Me!TreiziemeMois = "Non Applicable" Then
CurrentDb.Execute "UPDATE T_Missions SET " _
& "SalaireHoraire = Me!SRBaseHoraire," _
& "QuotePartT = Null WHERE IDMission = " _
& Me![IDMission], dbFailOnError
End If

Can someone please help me ??
Thanks in advance
MS Access 2003
Click to expand...
 
Nikos,

Thank you for your help.
I have pasted your code and I get the error message n°3122 "try to
execute query not including "SalaireHoraire" as part of the agregate function"
Do you have any idea ??
Frankie.

Nikos Yannacopoulos said:
Frankie,

Your mistake is that you have your form control references enclosed in
the quotes as parts of the text string, so Access can't tell they are
actually references, and ends up treating them as just parts of the
tesxt string. Change your code to:

If Me!TreiziemeMois = "Compris" Then
CurrentDb.Execute "UPDATE T_Missions SET " _
& "SalaireHoraire = " & Me!SRBaseHoraire & "," _
& "QuotePartT = Sum(" & Me!SRBaseHoraire & "-(" _
& Me!SRBaseHoraire & "*12/13))" _
& " WHERE IDMission = " & Me!IDMission, dbFailOnError
ElseIf Me!TreiziemeMois = "Non Compris" Then
CurrentDb.Execute "UPDATE T_Missions SET " _
& "SalaireHoraire = Sum(" & Me!SRBaseHoraire & "*1.12)," _
& "QuotePartT = Sum((" & Me!SRBaseHoraire & "*1.12)-" _
& Me!SRBaseHoraire & ")"
& " WHERE IDMission = " & Me!IDMission, dbFailOnError
ElseIf Me!TreiziemeMois = "Non Applicable" Then
CurrentDb.Execute "UPDATE T_Missions SET " _
& "SalaireHoraire = " & Me!SRBaseHoraire & "," _
& "QuotePartT = Null WHERE IDMission = " _
& Me![IDMission], dbFailOnError
End If

HTH,
Nikos
I've put the following code on the AfterUpdate Event of a control textbox
[SRBaseHoraire] in order to populate 2 fields [SalaireHoraire] and
[QuotePartT] based on the value of another field [TreiziemeMois],all
located on the same Form Mission, which doesn't work :

If Me!TreiziemeMois = "Compris" Then
CurrentDb.Execute "UPDATE T_Missions SET " _
& "SalaireHoraire = Me!SRBaseHoraire," _
& "QuotePartT = Sum(Me!SRBaseHoraire-(Me!SRBaseHoraire*12/13))
WHERE IDMission = " _
& Me!IDMission, dbFailOnError
ElseIf Me!TreiziemeMois = "Non Compris" Then
CurrentDb.Execute "UPDATE T_Missions SET " _
& "SalaireHoraire = Sum(Me!SRBaseHoraire*1.12)," _
& "QuotePartT = Sum((Me!SRBaseHoraire*1.12)-Me!SRBaseHoraire) WHERE
IDMission = " _
& Me!IDMission, dbFailOnError
ElseIf Me!TreiziemeMois = "Non Applicable" Then
CurrentDb.Execute "UPDATE T_Missions SET " _
& "SalaireHoraire = Me!SRBaseHoraire," _
& "QuotePartT = Null WHERE IDMission = " _
& Me![IDMission], dbFailOnError
End If

Can someone please help me ??
Thanks in advance
MS Access 2003
Click to expand...
Click to expand...
 
The only thing wrong syntax-wise is there was a missing underscore at
the end of line 11 (sorry!). Code should be:

If Me!TreiziemeMois = "Compris" Then
CurrentDb.Execute "UPDATE T_Missions SET " _
& "SalaireHoraire = " & Me!SRBaseHoraire & "," _
& "QuotePartT = Sum(" & Me!SRBaseHoraire & "-(" _
& Me!SRBaseHoraire & "*12/13))" _
& " WHERE IDMission = " & Me!IDMission, dbFailOnError
ElseIf Me!TreiziemeMois = "Non Compris" Then
CurrentDb.Execute "UPDATE T_Missions SET " _
& "SalaireHoraire = Sum(" & Me!SRBaseHoraire & "*1.12)," _
& "QuotePartT = Sum((" & Me!SRBaseHoraire & "*1.12)-" _
& Me!SRBaseHoraire & ")" _
& " WHERE IDMission = " & Me!IDMission, dbFailOnError
ElseIf Me!TreiziemeMois = "Non Applicable" Then
CurrentDb.Execute "UPDATE T_Missions SET " _
& "SalaireHoraire = " & Me!SRBaseHoraire & "," _
& "QuotePartT = Null WHERE IDMission = " _
& Me![IDMission], dbFailOnError
End If

See if this solves it.

HTH,
Nikos
Nikos,

Thank you for your help.
I have pasted your code and I get the error message n°3122 "try to
execute query not including "SalaireHoraire" as part of the agregate function"
Do you have any idea ??
Frankie.

:

Frankie,

Your mistake is that you have your form control references enclosed in
the quotes as parts of the text string, so Access can't tell they are
actually references, and ends up treating them as just parts of the
tesxt string. Change your code to:

If Me!TreiziemeMois = "Compris" Then
CurrentDb.Execute "UPDATE T_Missions SET " _
& "SalaireHoraire = " & Me!SRBaseHoraire & "," _
& "QuotePartT = Sum(" & Me!SRBaseHoraire & "-(" _
& Me!SRBaseHoraire & "*12/13))" _
& " WHERE IDMission = " & Me!IDMission, dbFailOnError
ElseIf Me!TreiziemeMois = "Non Compris" Then
CurrentDb.Execute "UPDATE T_Missions SET " _
& "SalaireHoraire = Sum(" & Me!SRBaseHoraire & "*1.12)," _
& "QuotePartT = Sum((" & Me!SRBaseHoraire & "*1.12)-" _
& Me!SRBaseHoraire & ")"
& " WHERE IDMission = " & Me!IDMission, dbFailOnError
ElseIf Me!TreiziemeMois = "Non Applicable" Then
CurrentDb.Execute "UPDATE T_Missions SET " _
& "SalaireHoraire = " & Me!SRBaseHoraire & "," _
& "QuotePartT = Null WHERE IDMission = " _
& Me![IDMission], dbFailOnError
End If

HTH,
Nikos
I've put the following code on the AfterUpdate Event of a control textbox
[SRBaseHoraire] in order to populate 2 fields [SalaireHoraire] and
[QuotePartT] based on the value of another field [TreiziemeMois],all
located on the same Form Mission, which doesn't work :

If Me!TreiziemeMois = "Compris" Then
CurrentDb.Execute "UPDATE T_Missions SET " _
& "SalaireHoraire = Me!SRBaseHoraire," _
& "QuotePartT = Sum(Me!SRBaseHoraire-(Me!SRBaseHoraire*12/13))
WHERE IDMission = " _
& Me!IDMission, dbFailOnError
ElseIf Me!TreiziemeMois = "Non Compris" Then
CurrentDb.Execute "UPDATE T_Missions SET " _
& "SalaireHoraire = Sum(Me!SRBaseHoraire*1.12)," _
& "QuotePartT = Sum((Me!SRBaseHoraire*1.12)-Me!SRBaseHoraire) WHERE
IDMission = " _
& Me!IDMission, dbFailOnError
ElseIf Me!TreiziemeMois = "Non Applicable" Then
CurrentDb.Execute "UPDATE T_Missions SET " _
& "SalaireHoraire = Me!SRBaseHoraire," _
& "QuotePartT = Null WHERE IDMission = " _
& Me![IDMission], dbFailOnError
End If

Can someone please help me ??
Thanks in advance
MS Access 2003
Click to expand...
Click to expand...
Click to expand...
 
I had noticed the missing underscore. It just doesn't work!
Could that be because I have upgraded from Access 2002 to 2003 ??
I am experiencing unusua
 
I had noticed the missing underscore. It just doesn't work!
Could that be because I have upgraded from Access 2002 to 2003 ??
I am experiencing unusual problems ever since. Library ones (ADO Vs DAO).
Any other suggestions ?
Thanks in advance.
Frankie
 
No, I don't suppose it has anything to do with the versions. It's
something to do with the query logic.. Perhaps the fact that you are
summing on a form control? Why are you doing that? Anyway, you can do
this to help identify the problem: modify the code as follows (actually,
just comment out the existing code temporarily, and paste this):


'If Me!TreiziemeMois = "Compris" Then
Debug.Print "UPDATE T_Missions SET " _
& "SalaireHoraire = " & Me!SRBaseHoraire & "," _
& "QuotePartT = Sum(" & Me!SRBaseHoraire & "-(" _
& Me!SRBaseHoraire & "*12/13))" _
& " WHERE IDMission = " & Me!IDMission, dbFailOnError
'ElseIf Me!TreiziemeMois = "Non Compris" Then
Debug.Print "UPDATE T_Missions SET " _
& "SalaireHoraire = Sum(" & Me!SRBaseHoraire & "*1.12)," _
& "QuotePartT = Sum((" & Me!SRBaseHoraire & "*1.12)-" _
& Me!SRBaseHoraire & ")" _
& " WHERE IDMission = " & Me!IDMission, dbFailOnError
'ElseIf Me!TreiziemeMois = "Non Applicable" Then
Debug.Print "UPDATE T_Missions SET " _
& "SalaireHoraire = " & Me!SRBaseHoraire & "," _
& "QuotePartT = Null WHERE IDMission = " _
& Me![IDMission], dbFailOnError
'End If

This will result in all three SQL expressions printed in the immediate
window (Ctrl+G to open if closed). Execute the code, copy each SQL
string and paste it in a new query's SQL view, then revert to design
view and see what you get. Is it reasonable? Does it run?

HTH,
Nikos
 
Here is what I pasted in Query SQL :
UPDATE T_Missions SET SalaireHoraire = 10,QuotePartT = Sum(10-(10*12/13))
WHERE IDMission = 1
UPDATE T_Missions SET SalaireHoraire = Sum(10*1.12),QuotePartT = Sum(
(10*1.12)-10) WHERE IDMission = 1
UPDATE T_Missions SET SalaireHoraire = 10,QuotePartT = Null WHERE IDMission
= 1
I get a syntax error (operator missing) on IDMission.
I understand calculating on form is not what one should do so I should
better get the calculation done thru a query but I am not really sure how
to do that.
Frankie
 
Frankie,

To begin with, you have pasted all three separate queries in on query...
sure it wouldn't work! Do that with one at a time (each separate query
starts with an UPDATE).

Furthermore, it is the Sum() calculation that is causing the problem;
the qury should work if you remove the Sum(). So, why did you put it
there in the first place? What are you trying to sum? The values of
BaseHoraire in all records? If you explain the logic in plain english,
it will be easier for me (or anybody) to propose a working solution.

HTH,
Nikos
 
Back
Top