populating field with conditional calculation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 = " _
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
 
1) I assume the code you copied got a little garbled when you posted.
"Compris" & "NonCompris" conditions appear twice and you appear to start 2
"If.." statements, but there is only one "End If". Therefore, I ignored the
code preceding the 2nd "If..."

2) You can't put variables within quotations.

"WHERE IDMission = " & Me!IDMission
is correct (if IDMission is numeric)

"UPDATE T_Missions SET SalaireHoraire = Me!SRBaseHoraire, QuotePartT =
Sum(Me!SRBaseHoraire-(Me!SRBaseHoraire*12/13))"
is not. It should be something like:
"UPDATE T_Missions SET SalaireHoraire = " & Me!SRBaseHoraire & ", QuotePartT
= " & Sum(Me!SRBaseHoraire-(Me!SRBaseHoraire*12/13)) (etc.)

I would strongly advise that you assign your SQL to a string variable and
then execute the string variable.
strSQL = "UPDATE yadaWHERE yada"
CurrentDb.Execute strSQL, dbFailOnError

The advantage to this is that you can set a breakpoint on the Execute line,
and easily test the value of the variable in the Immediate window before
Execute is executed:
?strSQL
This makes debugging a lot easier. You can see the value of the string you
are passing to Jet and it's usually fairly easy to spot problems & typos.

Another advantage is that your structure could be changed to the following,
which I would find easier to maintain & debug (only one Execute):

If x then
strSQL = this
ElseIf y then
strSQL = that
ElseIf z then
strSQL = the other thing
Else
'(What happens if none of the above are true?)
End if
Currentdb.Execute strSQL, dbFailOnError

HTH,
--
George Nicholson

Remove 'Junk' from return address.


Frankie said:
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 = " _
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...
 
Georges,

Thank yo for your help. Sorry for the messy pasting.
I have tried your solution but I can't get it to work!
I am not an Access expert so maybe I am missing something.
How should the code look in the AfterUpdate event section within private
sub ? It keeps returning an error message like "trying to call a procedure
or sub that doesn't exist".
Also, I have recently installed Access 2003 onto Access 2002 and a lot of
messy things are now happening due to library problems (Ado Vs DAO ???).
It seems I 'm gonna have to reinstall Access 2003 after a registry clean up.
What do you think?
Frankie.
 
Back
Top