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
:
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" <
[email protected]> a écrit dans le
message de news: (e-mail address removed)...
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
:
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" <
[email protected]> a écrit dans
le
message de (e-mail address removed)...
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
:
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
