Access 2003 : Numbering alarms by PermitNo so that after 365 days of the last number 1, the count returns to 1

Hi everybody,
FormPermit with SubFormAlarms writing in QryAlarms.
The form describe the owner of a permit for an alarm.
The subform the list of false alarms related to that permit.
How do you obtain an automatic numbering of the field NoAlarm so that, 365
days after the last alarm numbered 1, the count return to 1 ???
PermitNo DateAlarm NoAlarm
1 Jan 4, 2007 1
1 Feb 17, 2007 2
1 April 23, 2007 3
1 Jan 4, 2008 1 ------ back to 1, 365 after the
first alarm
1 Nov 5, 2008 2
1 Jan 4, 2009 1 ------ back to 1, 365 after
the last alarm numbered 1
Thank you very much for your precious help,
Céline

Céline:

Provided there is more than one false alarm per day per permit you could do
it with a subquery, but that would make the query non-updatable, so as the
query is the RecordSource for a subform which you presumably need to edit the
VBA DCount function would be better:

SELECT PermitNo, DateAlarm, DCount("*", "Alarms", "PermitNo = " & [PermitNo]
& " And Year(DateAlarm) = " & Year([DateAlarm]) & " And DateAlarm = #" &
Format([DateAlarm],"mm/dd/yyyy") & "#") AS NoAlarm
FROM Alarms
ORDER BY DateAlarm;

NoAlarm is a computed column in the query here, not a column in the Alarms
table. You don't need a column in the table, and should not have one as it
would introduce redundancy.

You may well find this is rather slow. Do you really need to see the
numbering in the subform? A far more efficient way would be in a report
grouped by PermitNo then Year(DateAlarm). You could then simply put an
unbound text box with a ControlSource property of =1 and a RunningSum
property of Over Group. This would also allow for more then one alarm per
permit per day.

Ken Sheridan
Stafford, England

"Céline Brien" wrote:

Hi everybody,
FormPermit with SubFormAlarms writing in QryAlarms.
The form describe the owner of a permit for an alarm.
The subform the list of false alarms related to that permit.
How do you obtain an automatic numbering of the field NoAlarm so that, 365
days after the last alarm numbered 1, the count return to 1 ???
PermitNo DateAlarm NoAlarm
1 Jan 4, 2007 1
1 Feb 17, 2007 2
1 April 23, 2007 3
1 Jan 4, 2008 1 ------ back to 1, 365 after the
first alarm
1 Nov 5, 2008 2
1 Jan 4, 2009 1 ------ back to 1, 365 after
the last alarm numbered 1
Thank you very much for your precious help,
Céline

Hi everybody,
Hi Ken,
Thank you very much for your answer.
I will try it and come back tomorrow morning.
I need the numbering in QryAlarms because different letters will be send
after the first alarm and the second alarm. Third alarm and over, you pay
for each false alarm. A year after an alarm numbered 1, you go back to 1.
You have two free false alarms and then you have to pay again.
If DCOUNT is to slow, do you think that a fonction created in a module could
do the job ?
Thank you again for your help,
Céline

"Ken Sheridan" a écrit dans le
message de news: ...
Céline:

Provided there is more than one false alarm per day per permit you could
do
it with a subquery, but that would make the query non-updatable, so as the
query is the RecordSource for a subform which you presumably need to edit
the
VBA DCount function would be better:

SELECT PermitNo, DateAlarm, DCount("*", "Alarms", "PermitNo = " &
[PermitNo]
& " And Year(DateAlarm) = " & Year([DateAlarm]) & " And DateAlarm = #" &
Format([DateAlarm],"mm/dd/yyyy") & "#") AS NoAlarm
FROM Alarms
ORDER BY DateAlarm;

NoAlarm is a computed column in the query here, not a column in the Alarms
table. You don't need a column in the table, and should not have one as
it
would introduce redundancy.

You may well find this is rather slow. Do you really need to see the
numbering in the subform? A far more efficient way would be in a report
grouped by PermitNo then Year(DateAlarm). You could then simply put an
unbound text box with a ControlSource property of =1 and a RunningSum
property of Over Group. This would also allow for more then one alarm per
permit per day.

Ken Sheridan
Stafford, England

"Céline Brien" wrote:

Hi everybody,
FormPermit with SubFormAlarms writing in QryAlarms.
The form describe the owner of a permit for an alarm.
The subform the list of false alarms related to that permit.
How do you obtain an automatic numbering of the field NoAlarm so that,
365
days after the last alarm numbered 1, the count return to 1 ???
PermitNo DateAlarm NoAlarm
1 Jan 4, 2007 1
1 Feb 17, 2007 2
1 April 23, 2007 3
1 Jan 4, 2008 1 ------ back to 1, 365 after
the
first alarm
1 Nov 5, 2008 2
1 Jan 4, 2009 1 ------ back to 1, 365 after
the last alarm numbered 1
Thank you very much for your precious help,
Céline

Hi everybody,
Hi Ken,
Finally, I just took the time to try the query with DCount and it seems to
work pretty well with a few data.
I will try it with more date tomorrow morning and come back to you.
Thank again for your answer,
Céline

"Ken Sheridan" a écrit dans le
message de news: ...
Céline:

Provided there is more than one false alarm per day per permit you could
do
it with a subquery, but that would make the query non-updatable, so as the
query is the RecordSource for a subform which you presumably need to edit
the
VBA DCount function would be better:

SELECT PermitNo, DateAlarm, DCount("*", "Alarms", "PermitNo = " &
[PermitNo]
& " And Year(DateAlarm) = " & Year([DateAlarm]) & " And DateAlarm = #" &
Format([DateAlarm],"mm/dd/yyyy") & "#") AS NoAlarm
FROM Alarms
ORDER BY DateAlarm;

NoAlarm is a computed column in the query here, not a column in the Alarms
table. You don't need a column in the table, and should not have one as
it
would introduce redundancy.

You may well find this is rather slow. Do you really need to see the
numbering in the subform? A far more efficient way would be in a report
grouped by PermitNo then Year(DateAlarm). You could then simply put an
unbound text box with a ControlSource property of =1 and a RunningSum
property of Over Group. This would also allow for more then one alarm per
permit per day.

Ken Sheridan
Stafford, England

"Céline Brien" wrote:

Hi everybody,
FormPermit with SubFormAlarms writing in QryAlarms.
The form describe the owner of a permit for an alarm.
The subform the list of false alarms related to that permit.
How do you obtain an automatic numbering of the field NoAlarm so that,
365
days after the last alarm numbered 1, the count return to 1 ???
PermitNo DateAlarm NoAlarm
1 Jan 4, 2007 1
1 Feb 17, 2007 2
1 April 23, 2007 3
1 Jan 4, 2008 1 ------ back to 1, 365 after
the
first alarm
1 Nov 5, 2008 2
1 Jan 4, 2009 1 ------ back to 1, 365 after
the last alarm numbered 1
Thank you very much for your precious help,
Céline

Hi everybody,
Hi Ken,
I just tested again, and I realise I did not made clearly explain the
problem to solve.
The numbering does not resart at 1 at the beginning of a new year, but 365
after the last number 1.
For exemple :
If the first false alarm is April 3, 2007, you come back to 1 April 3, 2008.
Then if your next false alarm is June 23, 2008, you come back to 1 June 23,
2009.
I hope I made the problem more clear.
Thank you for any help,
Céline

"Ken Sheridan" a écrit dans le
message de news: ...
Céline:

Provided there is more than one false alarm per day per permit you could
do
it with a subquery, but that would make the query non-updatable, so as the
query is the RecordSource for a subform which you presumably need to edit
the
VBA DCount function would be better:

SELECT PermitNo, DateAlarm, DCount("*", "Alarms", "PermitNo = " &
[PermitNo]
& " And Year(DateAlarm) = " & Year([DateAlarm]) & " And DateAlarm = #" &
Format([DateAlarm],"mm/dd/yyyy") & "#") AS NoAlarm
FROM Alarms
ORDER BY DateAlarm;

NoAlarm is a computed column in the query here, not a column in the Alarms
table. You don't need a column in the table, and should not have one as
it
would introduce redundancy.

You may well find this is rather slow. Do you really need to see the
numbering in the subform? A far more efficient way would be in a report
grouped by PermitNo then Year(DateAlarm). You could then simply put an
unbound text box with a ControlSource property of =1 and a RunningSum
property of Over Group. This would also allow for more then one alarm per
permit per day.

Ken Sheridan
Stafford, England

"Céline Brien" wrote:

Hi everybody,
FormPermit with SubFormAlarms writing in QryAlarms.
The form describe the owner of a permit for an alarm.
The subform the list of false alarms related to that permit.
How do you obtain an automatic numbering of the field NoAlarm so that,
365
days after the last alarm numbered 1, the count return to 1 ???
PermitNo DateAlarm NoAlarm
1 Jan 4, 2007 1
1 Feb 17, 2007 2
1 April 23, 2007 3
1 Jan 4, 2008 1 ------ back to 1, 365 after
the
first alarm
1 Nov 5, 2008 2
1 Jan 4, 2009 1 ------ back to 1, 365 after
the last alarm numbered 1
Thank you very much for your precious help,
Céline

Céline :

In that case I think you probably will have to have a NoAlarm column in the
Alarms table and update it when a new row is inserted in the subform. You
should be able to do that with a VBA function like this:

Public Function GetNoAlarm(lngPermitNo As Long, dtmDateAlarm 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 = _
"PermitNo = " & lngPermitNo & " And DateAlarm #" & _
Format(DateAdd("yyyy", -1, dtmDateAlarm), "mm/dd/yyyy") & _
"# And NoAlarm = 1"

varStartOfYear = DLookup("DateAlarm", "Alarms", strCriteria)

If IsNull(varStartOfYear) Then
GetNoAlarm = 1
Else
' look up last alarm number
strCriteria = _
"PermitNo = " & lngPermitNo & " And DateAlarm = #" & _
Format(DMax("DateAlarm", "Alarms", "PermitNo = " & _
lngPermitNo & " And NoAlarm 0"), "mm/dd/yyyy") & "#"

intNextNumber = DLookup("NoAlarm", "Alarms", strCriteria)
' and add 1
GetNoAlarm = intNextNumber + 1
End If

End Function

In the BeforeUpdate event procedure of the subform update the number with:

If Me.NewRecord Then
Me.NoAlarm = GetNoAlarm(Me.PermitNo, Me.DateAlarm)
End If

The drawback of this of course is that it relies on the rows for each permit
being inserted in the correct data order. You could not for instance insert
a row for May 1, then one for July 14 and then one for July 7. It should be
possible to write a global update routine like this which would renumber them
correctly, however:

Public Function UpdateNoAlarms()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set dbs = CurrentDb

'set all alarm numbers to zero
strSQL = "Update Alarms SET NoAlarm = 0"
dbs.Execute strSQL

' update alarm numbers calling GetNoAlarm function for each row
strSQL = _
"SELECT PermitNo, DateAlarm, NoAlarm " & _
" FROM Alarms ORDER BY PermitNo, DateAlarm"

Set rst = dbs.OpenRecordset(strSQL)

With rst
Do While Not .EOF
.Edit
.Fields("NoAlarm") = GetNoAlarm(.Fields("PermitNo"),
..Fields("DateAlarm"))
.Update
.MoveNext
Loop
End With

End Function

Ken Sheridan
Stafford, England

"Céline Brien" wrote:

Hi everybody,
Hi Ken,
I just tested again, and I realise I did not made clearly explain the
problem to solve.
The numbering does not resart at 1 at the beginning of a new year, but 365
after the last number 1.
For exemple :
If the first false alarm is April 3, 2007, you come back to 1 April 3, 2008.
Then if your next false alarm is June 23, 2008, you come back to 1 June 23,
2009.
I hope I made the problem more clear.
Thank you for any help,
Céline

"Ken Sheridan" a écrit dans le
message de news: ...
Céline:

Provided there is more than one false alarm per day per permit you could
do
it with a subquery, but that would make the query non-updatable, so as the
query is the RecordSource for a subform which you presumably need to edit
the
VBA DCount function would be better:

SELECT PermitNo, DateAlarm, DCount("*", "Alarms", "PermitNo = " &
[PermitNo]
& " And Year(DateAlarm) = " & Year([DateAlarm]) & " And DateAlarm = #" &
Format([DateAlarm],"mm/dd/yyyy") & "#") AS NoAlarm
FROM Alarms
ORDER BY DateAlarm;

NoAlarm is a computed column in the query here, not a column in the Alarms
table. You don't need a column in the table, and should not have one as
it
would introduce redundancy.

You may well find this is rather slow. Do you really need to see the
numbering in the subform? A far more efficient way would be in a report
grouped by PermitNo then Year(DateAlarm). You could then simply put an
unbound text box with a ControlSource property of =1 and a RunningSum
property of Over Group. This would also allow for more then one alarm per
permit per day.

Ken Sheridan
Stafford, England

"Céline Brien" wrote:

Hi everybody,
FormPermit with SubFormAlarms writing in QryAlarms.
The form describe the owner of a permit for an alarm.
The subform the list of false alarms related to that permit.
How do you obtain an automatic numbering of the field NoAlarm so that,
365
days after the last alarm numbered 1, the count return to 1 ???
PermitNo DateAlarm NoAlarm
1 Jan 4, 2007 1
1 Feb 17, 2007 2
1 April 23, 2007 3
1 Jan 4, 2008 1 ------ back to 1, 365 after
the
first alarm
1 Nov 5, 2008 2
1 Jan 4, 2009 1 ------ back to 1, 365 after
the last alarm numbered 1
Thank you very much for your precious help,
Céline

Hi everybody,
Hi Ken,
Tahnk you so much for your answer !
I will try your solution tomorrow morning (it is 18:34 and I did not had
supper yet).
Do not worry about the order of the alarm. They should always be in the
correct data order.
I will come back if I have any questions.
Thank again !
Céline

"Ken Sheridan" a écrit dans le
message de news: ...
Céline :

In that case I think you probably will have to have a NoAlarm column in
the
Alarms table and update it when a new row is inserted in the subform. You
should be able to do that with a VBA function like this:

Public Function GetNoAlarm(lngPermitNo As Long, dtmDateAlarm 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 = _
"PermitNo = " & lngPermitNo & " And DateAlarm #" & _
Format(DateAdd("yyyy", -1, dtmDateAlarm), "mm/dd/yyyy") & _
"# And NoAlarm = 1"

varStartOfYear = DLookup("DateAlarm", "Alarms", strCriteria)

If IsNull(varStartOfYear) Then
GetNoAlarm = 1
Else
' look up last alarm number
strCriteria = _
"PermitNo = " & lngPermitNo & " And DateAlarm = #" & _
Format(DMax("DateAlarm", "Alarms", "PermitNo = " & _
lngPermitNo & " And NoAlarm 0"), "mm/dd/yyyy") & "#"

intNextNumber = DLookup("NoAlarm", "Alarms", strCriteria)
' and add 1
GetNoAlarm = intNextNumber + 1
End If

End Function

In the BeforeUpdate event procedure of the subform update the number with:

If Me.NewRecord Then
Me.NoAlarm = GetNoAlarm(Me.PermitNo, Me.DateAlarm)
End If

The drawback of this of course is that it relies on the rows for each
permit
being inserted in the correct data order. You could not for instance
insert
a row for May 1, then one for July 14 and then one for July 7. It should
be
possible to write a global update routine like this which would renumber
them
correctly, however:

Public Function UpdateNoAlarms()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set dbs = CurrentDb

'set all alarm numbers to zero
strSQL = "Update Alarms SET NoAlarm = 0"
dbs.Execute strSQL

' update alarm numbers calling GetNoAlarm function for each row
strSQL = _
"SELECT PermitNo, DateAlarm, NoAlarm " & _
" FROM Alarms ORDER BY PermitNo, DateAlarm"

Set rst = dbs.OpenRecordset(strSQL)

With rst
Do While Not .EOF
.Edit
.Fields("NoAlarm") = GetNoAlarm(.Fields("PermitNo"),
.Fields("DateAlarm"))
.Update
.MoveNext
Loop
End With

End Function

Ken Sheridan
Stafford, England

"Céline Brien" wrote:

Hi everybody,
Hi Ken,
I just tested again, and I realise I did not made clearly explain the
problem to solve.
The numbering does not resart at 1 at the beginning of a new year, but
365
after the last number 1.
For exemple :
If the first false alarm is April 3, 2007, you come back to 1 April 3,
2008.
Then if your next false alarm is June 23, 2008, you come back to 1 June
23,
2009.
I hope I made the problem more clear.
Thank you for any help,
Céline

"Ken Sheridan" a écrit dans le
message de news: ...
Céline:

Provided there is more than one false alarm per day per permit you
could
do
it with a subquery, but that would make the query non-updatable, so as
the
query is the RecordSource for a subform which you presumably need to
edit
the
VBA DCount function would be better:

SELECT PermitNo, DateAlarm, DCount("*", "Alarms", "PermitNo = " &
[PermitNo]
& " And Year(DateAlarm) = " & Year([DateAlarm]) & " And DateAlarm = #"
&
Format([DateAlarm],"mm/dd/yyyy") & "#") AS NoAlarm
FROM Alarms
ORDER BY DateAlarm;

NoAlarm is a computed column in the query here, not a column in the
Alarms
table. You don't need a column in the table, and should not have one
as
it
would introduce redundancy.

You may well find this is rather slow. Do you really need to see the
numbering in the subform? A far more efficient way would be in a
report
grouped by PermitNo then Year(DateAlarm). You could then simply put an
unbound text box with a ControlSource property of =1 and a RunningSum
property of Over Group. This would also allow for more then one alarm
per
permit per day.

Ken Sheridan
Stafford, England

"Céline Brien" wrote:

Hi everybody,
FormPermit with SubFormAlarms writing in QryAlarms.
The form describe the owner of a permit for an alarm.
The subform the list of false alarms related to that permit.
How do you obtain an automatic numbering of the field NoAlarm so that,
365
days after the last alarm numbered 1, the count return to 1 ???
PermitNo DateAlarm NoAlarm
1 Jan 4, 2007 1
1 Feb 17, 2007 2
1 April 23, 2007 3
1 Jan 4, 2008 1 ------ back to 1, 365
after
the
first alarm
1 Nov 5, 2008 2
1 Jan 4, 2009 1 ------ back to 1, 365
after
the last alarm numbered 1
Thank you very much for your precious help,
Céline

Hi everybody,
Hi Ken,
It is working perfectly !
Numbering and update of the numbers in case a date has to be retyped.
The function UpdateNoAlarms is in the AfterUpdate event procedure of the
subform.
It is pretty fast to. Our total number of alarms by year should be around 1
200.
I don't know how to thank you !
The only little thing is when there is more than two alarms the same day for
the same permit. The last entered is given the right numer and all the
others are given the number 1. Is there anything that can be done ? It might
not happen often, but once is enough...
Each alarm as a unique number : CallNo. It is a text field compose of the
date and a number : 02262007-001
Can we use this field in the criteria ?
Thanks again,
Céline



"Ken Sheridan" a écrit dans le
message de news: ...
Céline :

In that case I think you probably will have to have a NoAlarm column in
the
Alarms table and update it when a new row is inserted in the subform. You
should be able to do that with a VBA function like this:

Public Function GetNoAlarm(lngPermitNo As Long, dtmDateAlarm 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 = _
"PermitNo = " & lngPermitNo & " And DateAlarm #" & _
Format(DateAdd("yyyy", -1, dtmDateAlarm), "mm/dd/yyyy") & _
"# And NoAlarm = 1"

varStartOfYear = DLookup("DateAlarm", "Alarms", strCriteria)

If IsNull(varStartOfYear) Then
GetNoAlarm = 1
Else
' look up last alarm number
strCriteria = _
"PermitNo = " & lngPermitNo & " And DateAlarm = #" & _
Format(DMax("DateAlarm", "Alarms", "PermitNo = " & _
lngPermitNo & " And NoAlarm 0"), "mm/dd/yyyy") & "#"

intNextNumber = DLookup("NoAlarm", "Alarms", strCriteria)
' and add 1
GetNoAlarm = intNextNumber + 1
End If

End Function

In the BeforeUpdate event procedure of the subform update the number with:

If Me.NewRecord Then
Me.NoAlarm = GetNoAlarm(Me.PermitNo, Me.DateAlarm)
End If

The drawback of this of course is that it relies on the rows for each
permit
being inserted in the correct data order. You could not for instance
insert
a row for May 1, then one for July 14 and then one for July 7. It should
be
possible to write a global update routine like this which would renumber
them
correctly, however:

Public Function UpdateNoAlarms()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set dbs = CurrentDb

'set all alarm numbers to zero
strSQL = "Update Alarms SET NoAlarm = 0"
dbs.Execute strSQL

' update alarm numbers calling GetNoAlarm function for each row
strSQL = _
"SELECT PermitNo, DateAlarm, NoAlarm " & _
" FROM Alarms ORDER BY PermitNo, DateAlarm"

Set rst = dbs.OpenRecordset(strSQL)

With rst
Do While Not .EOF
.Edit
.Fields("NoAlarm") = GetNoAlarm(.Fields("PermitNo"),
.Fields("DateAlarm"))
.Update
.MoveNext
Loop
End With

End Function

Ken Sheridan
Stafford, England

"Céline Brien" wrote:

Hi everybody,
Hi Ken,
I just tested again, and I realise I did not made clearly explain the
problem to solve.
The numbering does not resart at 1 at the beginning of a new year, but
365
after the last number 1.
For exemple :
If the first false alarm is April 3, 2007, you come back to 1 April 3,
2008.
Then if your next false alarm is June 23, 2008, you come back to 1 June
23,
2009.
I hope I made the problem more clear.
Thank you for any help,
Céline

"Ken Sheridan" a écrit dans le
message de news: ...
Céline:

Provided there is more than one false alarm per day per permit you
could
do
it with a subquery, but that would make the query non-updatable, so as
the
query is the RecordSource for a subform which you presumably need to
edit
the
VBA DCount function would be better:

SELECT PermitNo, DateAlarm, DCount("*", "Alarms", "PermitNo = " &
[PermitNo]
& " And Year(DateAlarm) = " & Year([DateAlarm]) & " And DateAlarm = #"
&
Format([DateAlarm],"mm/dd/yyyy") & "#") AS NoAlarm
FROM Alarms
ORDER BY DateAlarm;

NoAlarm is a computed column in the query here, not a column in the
Alarms
table. You don't need a column in the table, and should not have one
as
it
would introduce redundancy.

You may well find this is rather slow. Do you really need to see the
numbering in the subform? A far more efficient way would be in a
report
grouped by PermitNo then Year(DateAlarm). You could then simply put an
unbound text box with a ControlSource property of =1 and a RunningSum
property of Over Group. This would also allow for more then one alarm
per
permit per day.

Ken Sheridan
Stafford, England

"Céline Brien" wrote:

Hi everybody,
FormPermit with SubFormAlarms writing in QryAlarms.
The form describe the owner of a permit for an alarm.
The subform the list of false alarms related to that permit.
How do you obtain an automatic numbering of the field NoAlarm so that,
365
days after the last alarm numbered 1, the count return to 1 ???
PermitNo DateAlarm NoAlarm
1 Jan 4, 2007 1
1 Feb 17, 2007 2
1 April 23, 2007 3
1 Jan 4, 2008 1 ------ back to 1, 365
after
the
first alarm
1 Nov 5, 2008 2
1 Jan 4, 2009 1 ------ back to 1, 365
after
the last alarm numbered 1
Thank you very much for your precious help,
Céline

Céline:

Adding some code to the function to test for an existing alarm for the
permit on the same day should do it. This seems to work with my test data:

Public Function GetNoAlarm(lngPermitNo As Long, dtmDateAlarm As Date) As
Integer

Dim varStartOfYear As Variant
Dim varLastNumber 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 = _
"PermitNo = " & lngPermitNo & " And DateAlarm #" & _
Format(DateAdd("yyyy", -1, dtmDateAlarm), "mm/dd/yyyy") & _
"# And NoAlarm = 1"

varStartOfYear = DLookup("DateAlarm", "Alarms", strCriteria)

If IsNull(varStartOfYear) Then
GetNoAlarm = 1
Else
' is there aleady row for this permit on same day?
strCriteria = _
"PermitNo = " & lngPermitNo & " And DateAlarm = #" & _
Format(dtmDateAlarm, "mm/dd/yyyy") & "# And NoAlarm 0"
varLastNumber = DMax("NoAlarm", "Alarms", strCriteria)
If Not IsNull(varLastNumber) Then
' add 1 to last number on same day
GetNoAlarm = varLastNumber + 1
Else
' look up last alarm number
strCriteria = _
"PermitNo = " & lngPermitNo & " And DateAlarm = #" & _
Format(DMax("DateAlarm", "Alarms", "PermitNo = " & _
lngPermitNo & " And NoAlarm 0"), "mm/dd/yyyy") & "#"

intNextNumber = DMax("NoAlarm", "Alarms", strCriteria)
' and add 1
GetNoAlarm = intNextNumber + 1
End If
End If

End Function

Ken Sheridan
Stafford, England

"Céline Brien" wrote:

Hi everybody,
Hi Ken,
It is working perfectly !
Numbering and update of the numbers in case a date has to be retyped.
The function UpdateNoAlarms is in the AfterUpdate event procedure of the
subform.
It is pretty fast to. Our total number of alarms by year should be around 1
200.
I don't know how to thank you !
The only little thing is when there is more than two alarms the same day for
the same permit. The last entered is given the right numer and all the
others are given the number 1. Is there anything that can be done ? It might
not happen often, but once is enough...
Each alarm as a unique number : CallNo. It is a text field compose of the
date and a number : 02262007-001
Can we use this field in the criteria ?
Thanks again,
Céline



"Ken Sheridan" a écrit dans le
message de news: ...
Céline :

In that case I think you probably will have to have a NoAlarm column in
the
Alarms table and update it when a new row is inserted in the subform. You
should be able to do that with a VBA function like this:

Public Function GetNoAlarm(lngPermitNo As Long, dtmDateAlarm 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 = _
"PermitNo = " & lngPermitNo & " And DateAlarm #" & _
Format(DateAdd("yyyy", -1, dtmDateAlarm), "mm/dd/yyyy") & _
"# And NoAlarm = 1"

varStartOfYear = DLookup("DateAlarm", "Alarms", strCriteria)

If IsNull(varStartOfYear) Then
GetNoAlarm = 1
Else
' look up last alarm number
strCriteria = _
"PermitNo = " & lngPermitNo & " And DateAlarm = #" & _
Format(DMax("DateAlarm", "Alarms", "PermitNo = " & _
lngPermitNo & " And NoAlarm 0"), "mm/dd/yyyy") & "#"

intNextNumber = DLookup("NoAlarm", "Alarms", strCriteria)
' and add 1
GetNoAlarm = intNextNumber + 1
End If

End Function

In the BeforeUpdate event procedure of the subform update the number with:

If Me.NewRecord Then
Me.NoAlarm = GetNoAlarm(Me.PermitNo, Me.DateAlarm)
End If

The drawback of this of course is that it relies on the rows for each
permit
being inserted in the correct data order. You could not for instance
insert
a row for May 1, then one for July 14 and then one for July 7. It should
be
possible to write a global update routine like this which would renumber
them
correctly, however:

Public Function UpdateNoAlarms()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set dbs = CurrentDb

'set all alarm numbers to zero
strSQL = "Update Alarms SET NoAlarm = 0"
dbs.Execute strSQL

' update alarm numbers calling GetNoAlarm function for each row
strSQL = _
"SELECT PermitNo, DateAlarm, NoAlarm " & _
" FROM Alarms ORDER BY PermitNo, DateAlarm"

Set rst = dbs.OpenRecordset(strSQL)

With rst
Do While Not .EOF
.Edit
.Fields("NoAlarm") = GetNoAlarm(.Fields("PermitNo"),
.Fields("DateAlarm"))
.Update
.MoveNext
Loop
End With

End Function

Ken Sheridan
Stafford, England

"Céline Brien" wrote:

Hi everybody,
Hi Ken,
I just tested again, and I realise I did not made clearly explain the
problem to solve.
The numbering does not resart at 1 at the beginning of a new year, but
365
after the last number 1.
For exemple :
If the first false alarm is April 3, 2007, you come back to 1 April 3,
2008.
Then if your next false alarm is June 23, 2008, you come back to 1 June
23,
2009.
I hope I made the problem more clear.
Thank you for any help,
Céline

"Ken Sheridan" a écrit dans le
message de news: ...
Céline:

Provided there is more than one false alarm per day per permit you
could
do
it with a subquery, but that would make the query non-updatable, so as
the
query is the RecordSource for a subform which you presumably need to
edit
the
VBA DCount function would be better:

SELECT PermitNo, DateAlarm, DCount("*", "Alarms", "PermitNo = " &
[PermitNo]
& " And Year(DateAlarm) = " & Year([DateAlarm]) & " And DateAlarm = #"
&
Format([DateAlarm],"mm/dd/yyyy") & "#") AS NoAlarm
FROM Alarms
ORDER BY DateAlarm;

NoAlarm is a computed column in the query here, not a column in the
Alarms
table. You don't need a column in the table, and should not have one
as
it
would introduce redundancy.

You may well find this is rather slow. Do you really need to see the
numbering in the subform? A far more efficient way would be in a
report
grouped by PermitNo then Year(DateAlarm). You could then simply put an
unbound text box with a ControlSource property of =1 and a RunningSum
property of Over Group. This would also allow for more then one alarm
per
permit per day.

Ken Sheridan
Stafford, England

"Céline Brien" wrote:

Hi everybody,
FormPermit with SubFormAlarms writing in QryAlarms.
The form describe the owner of a permit for an alarm.
The subform the list of false alarms related to that permit.
How do you obtain an automatic numbering of the field NoAlarm so that,
365
days after the last alarm numbered 1, the count return to 1 ???
PermitNo DateAlarm NoAlarm
1 Jan 4, 2007 1
1 Feb 17, 2007 2
1 April 23, 2007 3
1 Jan 4, 2008 1 ------ back to 1, 365
after
the
first alarm
1 Nov 5, 2008 2
1 Jan 4, 2009 1 ------ back to 1, 365
after
the last alarm numbered 1
Thank you very much for your precious help,
Céline

Hi everybody,
Hi Ken,
Again, thank you so much for your answer. I highly appreciate your help !
I just came back from walking with my dogs, and during my walk, I had the
idea of adding the time of the alarm in the same field has the date (it was
in a different field).
It works perfectly well, maybe even to much !!!!
If the first alarm id Feb, 10 2007 at 22:45 and than you have another alarme
Feb,10 2008 at 16:45, it will give the alarm the number 2 because we are
missing 6 hours before the 365 days are over !!!!
I will study your last proposition and come back tomorrow. Now I am going to
visit my old mother.
Many many thanks again and again !
Céline

"Ken Sheridan" a écrit dans le
message de news: ...
Céline:

Adding some code to the function to test for an existing alarm for the
permit on the same day should do it. This seems to work with my test
data:

Public Function GetNoAlarm(lngPermitNo As Long, dtmDateAlarm As Date) As
Integer

Dim varStartOfYear As Variant
Dim varLastNumber 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 = _
"PermitNo = " & lngPermitNo & " And DateAlarm #" & _
Format(DateAdd("yyyy", -1, dtmDateAlarm), "mm/dd/yyyy") & _
"# And NoAlarm = 1"

varStartOfYear = DLookup("DateAlarm", "Alarms", strCriteria)

If IsNull(varStartOfYear) Then
GetNoAlarm = 1
Else
' is there aleady row for this permit on same day?
strCriteria = _
"PermitNo = " & lngPermitNo & " And DateAlarm = #" & _
Format(dtmDateAlarm, "mm/dd/yyyy") & "# And NoAlarm 0"
varLastNumber = DMax("NoAlarm", "Alarms", strCriteria)
If Not IsNull(varLastNumber) Then
' add 1 to last number on same day
GetNoAlarm = varLastNumber + 1
Else
' look up last alarm number
strCriteria = _
"PermitNo = " & lngPermitNo & " And DateAlarm = #" & _
Format(DMax("DateAlarm", "Alarms", "PermitNo = " & _
lngPermitNo & " And NoAlarm 0"), "mm/dd/yyyy") & "#"

intNextNumber = DMax("NoAlarm", "Alarms", strCriteria)
' and add 1
GetNoAlarm = intNextNumber + 1
End If
End If

End Function

Ken Sheridan
Stafford, England

"Céline Brien" wrote:

Hi everybody,
Hi Ken,
It is working perfectly !
Numbering and update of the numbers in case a date has to be retyped.
The function UpdateNoAlarms is in the AfterUpdate event procedure of the
subform.
It is pretty fast to. Our total number of alarms by year should be around
1
200.
I don't know how to thank you !
The only little thing is when there is more than two alarms the same day
for
the same permit. The last entered is given the right numer and all the
others are given the number 1. Is there anything that can be done ? It
might
not happen often, but once is enough...
Each alarm as a unique number : CallNo. It is a text field compose of the
date and a number : 02262007-001
Can we use this field in the criteria ?
Thanks again,
Céline



"Ken Sheridan" a écrit dans le
message de news: ...
Céline :

In that case I think you probably will have to have a NoAlarm column in
the
Alarms table and update it when a new row is inserted in the subform.
You
should be able to do that with a VBA function like this:

Public Function GetNoAlarm(lngPermitNo As Long, dtmDateAlarm 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 = _
"PermitNo = " & lngPermitNo & " And DateAlarm #" & _
Format(DateAdd("yyyy", -1, dtmDateAlarm), "mm/dd/yyyy") & _
"# And NoAlarm = 1"

varStartOfYear = DLookup("DateAlarm", "Alarms", strCriteria)

If IsNull(varStartOfYear) Then
GetNoAlarm = 1
Else
' look up last alarm number
strCriteria = _
"PermitNo = " & lngPermitNo & " And DateAlarm = #" & _
Format(DMax("DateAlarm", "Alarms", "PermitNo = " & _
lngPermitNo & " And NoAlarm 0"), "mm/dd/yyyy") & "#"

intNextNumber = DLookup("NoAlarm", "Alarms", strCriteria)
' and add 1
GetNoAlarm = intNextNumber + 1
End If

End Function

In the BeforeUpdate event procedure of the subform update the number
with:

If Me.NewRecord Then
Me.NoAlarm = GetNoAlarm(Me.PermitNo, Me.DateAlarm)
End If

The drawback of this of course is that it relies on the rows for each
permit
being inserted in the correct data order. You could not for instance
insert
a row for May 1, then one for July 14 and then one for July 7. It
should
be
possible to write a global update routine like this which would
renumber
them
correctly, however:

Public Function UpdateNoAlarms()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set dbs = CurrentDb

'set all alarm numbers to zero
strSQL = "Update Alarms SET NoAlarm = 0"
dbs.Execute strSQL

' update alarm numbers calling GetNoAlarm function for each row
strSQL = _
"SELECT PermitNo, DateAlarm, NoAlarm " & _
" FROM Alarms ORDER BY PermitNo, DateAlarm"

Set rst = dbs.OpenRecordset(strSQL)

With rst
Do While Not .EOF
.Edit
.Fields("NoAlarm") = GetNoAlarm(.Fields("PermitNo"),
.Fields("DateAlarm"))
.Update
.MoveNext
Loop
End With

End Function

Ken Sheridan
Stafford, England

"Céline Brien" wrote:

Hi everybody,
Hi Ken,
I just tested again, and I realise I did not made clearly explain the
problem to solve.
The numbering does not resart at 1 at the beginning of a new year, but
365
after the last number 1.
For exemple :
If the first false alarm is April 3, 2007, you come back to 1 April 3,
2008.
Then if your next false alarm is June 23, 2008, you come back to 1
June
23,
2009.
I hope I made the problem more clear.
Thank you for any help,
Céline

"Ken Sheridan" a écrit dans le
message de news: ...
Céline:

Provided there is more than one false alarm per day per permit you
could
do
it with a subquery, but that would make the query non-updatable, so
as
the
query is the RecordSource for a subform which you presumably need to
edit
the
VBA DCount function would be better:

SELECT PermitNo, DateAlarm, DCount("*", "Alarms", "PermitNo = " &
[PermitNo]
& " And Year(DateAlarm) = " & Year([DateAlarm]) & " And DateAlarm =
#"
&
Format([DateAlarm],"mm/dd/yyyy") & "#") AS NoAlarm
FROM Alarms
ORDER BY DateAlarm;

NoAlarm is a computed column in the query here, not a column in the
Alarms
table. You don't need a column in the table, and should not have
one
as
it
would introduce redundancy.

You may well find this is rather slow. Do you really need to see
the
numbering in the subform? A far more efficient way would be in a
report
grouped by PermitNo then Year(DateAlarm). You could then simply put
an
unbound text box with a ControlSource property of =1 and a
RunningSum
property of Over Group. This would also allow for more then one
alarm
per
permit per day.

Ken Sheridan
Stafford, England

"Céline Brien" wrote:

Hi everybody,
FormPermit with SubFormAlarms writing in QryAlarms.
The form describe the owner of a permit for an alarm.
The subform the list of false alarms related to that permit.
How do you obtain an automatic numbering of the field NoAlarm so
that,
365
days after the last alarm numbered 1, the count return to 1 ???
PermitNo DateAlarm NoAlarm
1 Jan 4, 2007 1
1 Feb 17, 2007 2
1 April 23, 2007 3
1 Jan 4, 2008 1 ------ back to 1, 365
after
the
first alarm
1 Nov 5, 2008 2
1 Jan 4, 2009 1 ------ back to 1, 365
after
the last alarm numbered 1
Thank you very much for your precious help,
Céline