Problem with an update function

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

Guest

Hello,

I am having a problem with a function that I have created in the past in
Access2000.
Now I am using Access2003 and some somereason this fonction does not update
my table at all, the db does not generate any error at all. I use Step by
Step to analyse and it goes thru the function smoothly, no errors, but my
table is not being updated at all.

Public Sub ActiveDate(num As Integer, dat1 As Variant, dat2 As Variant, dat3
As Variant, dat4 As Variant, dat5 As Variant, dat6 As Variant)
' date1 field is "SpecialNotice"
' date2 field is "RenewalOptions"
' date3 field is "ExerciseDateExpRights"
' date4 field is "ExerciseDateRightsFirst"
' date5 field is "ExerciseDateCancelRights"
' date6 field is "To" from lease

Dim i As Integer
Dim dtemp As Variant, dtemp2 As Variant, actdate As Variant
Dim mdat(5) As Variant
Dim bds As DAO.Database
Set bds = CurrentDb

' specialnotice override all other dates
If Not IsNull(dat1) Then
' mise a jour du ActivationDate
CurrentDb.Execute _
"UPDATE Branch SET ActivationDate = #" & dat1 & "# WHERE idBranch = "
& num & "" & _
dbFailOnError

Set bds = Nothing
Exit Sub
Else
' remplissage du tableau
mdat(0) = dat2
mdat(1) = dat3
mdat(2) = dat4
mdat(3) = dat5
mdat(4) = dat6

'recherche une date de comparaison
For i = 0 To 4
If Not IsNull(mdat(i)) Then
dtemp2 = mdat(i)
Exit For
End If
Next i
'recherche date la plus petite
For i = 0 To 4
dtemp = mdat(i)
If dtemp2 > dtemp Then
dtemp2 = dtemp
End If
Next i

actdate = DateAdd("m", -12, dtemp2)

' mise a jour du ActivationDAte
CurrentDb.Execute _
"UPDATE Branch SET ActivationDate = #" & actdate & "# WHERE
IdBranch = " & num & "" & _
dbFailOnError

Set bds = Nothing
End If
End Sub

Can anyone tell me what I can do to solve this
Thanks
Alain
 
you are missing a comma before dbFailOnError:

"UPDATE Branch SET ActivationDate = #" & dat1 & "# WHERE idBranch = " & num
& "," & dbFailOnError

or (I find this much easier to read, debug & maintain)
strSQL = "UPDATE Branch SET ActivationDate = #" & dat1 & "# WHERE
idBranch = " & num
CurrentDb.Execute strSQL, dbFailOnError

HTH,
 
Back
Top