RE : Access 2003 : Numbering alarms by PermitNo so that after 365 days of the last number 1, the cou

[Céline Brien]

Hi everybody !
In February I had an answer to this question. See the codes below :
UpdateNoAlarmeIncendie and GetNoAlarmeIncendie.
Maybe I am not in the right news but because it all started in this news I
decided to stay here.
What I need find now, is a way to keep some alarms out of the numbering
system.
I added a yes/no field to identify these alarms.
I tried to create a new variable :

Dim varAlarmeFondee As Variant

And these codes :

varAlarmeFondee = DLookup("AlarmeFondee", "AlarmesIncendie",
strCriteria)

If (varAlarmeFondee) = -1 Then
GetNoAlarmeIncendie = 0

I got zero at all the alarms of the table :-(
Can anybody help me ?
Many thanks,
Céline
-------------------------------------------------------
Public Function UpdateNoAlarmeIncendie()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set dbs = CurrentDb

'Remettre à zéro NoAlarmeIncendie
strSQL = "Update AlarmesIncendie SET NoAlarmeIncendie = 0"
dbs.Execute strSQL

' Mettre à jour NoAlarmeIncendie en appelant la fonction
GetNoAlarmeIncendie pour chacune des lignes
strSQL = _
"SELECT NoPermis, DateAppel, NoAlarmeIncendie " & _
" FROM AlarmesIncendie ORDER BY NoPermis, DateAppel"

Set rst = dbs.OpenRecordset(strSQL)

With rst
Do While Not .EOF
.Edit
.Fields("NoAlarmeIncendie") =
GetNoAlarmeIncendie(.Fields("NoPermis"), .Fields("DateAppel"))
.Update
.MoveNext
Loop
End With

End Function
-------------------------------------------
Public Function GetNoAlarmeIncendie(lngNoPermis As Long, dtmDateAppel As
Date) As Integer

Dim varStartOfYear As Variant
Dim strCriteria As String
Dim intNextNumber As Integer

' if a row exists with an alarm number
' of 1 within last year then next alarm
' number is last number plus 1,
' otherwise new alarm number is 1
strCriteria = _
"NoPermis = " & lngNoPermis & " And DateAppel > #" & _
Format(DateAdd("yyyy", -1, dtmDateAppel), "yyyy-mm-dd hh:mm") & _
"# And NoAlarmeIncendie = 1"

varStartOfYear = DLookup("DateAppel", "AlarmesIncendie", strCriteria)

If IsNull(varStartOfYear) Then
GetNoAlarmeIncendie = 1
Else
' look up last alarm number
strCriteria = _
"NoPermis = " & lngNoPermis & " And DateAppel = #" & _
Format(DMax("DateAppel", "AlarmesIncendie", "NoPermis = " & _
lngNoPermis & " And NoAlarmeIncendie <> 0"), "yyyy-mm-dd hh:mm")
& "#"

intNextNumber = DLookup("NoAlarmeIncendie", "AlarmesIncendie",
strCriteria)
' and add 1
GetNoAlarmeIncendie = intNextNumber + 1
End If

End Function