G
Glint
Hi Guys,
I have not found a good way to tackle this problem: How do I get a
calculated field on my form totaled automatically? I found I could not simply
set the record source of the field to: =Sum([MyFiled]). This method usually
returned #Error.
Now I have a form based on my SatsangClass. A Calculated field [Attendance]
has its record source as:
=IIf(IsNull([SatsangID]),Null,DCount("*","SatsangLesson","[SatsangID]=" &
[SatsangID])).
The SatsangLesson table is joined to the SatsangClass through the SatsangID
field (one to many). So far so good, because when I open my form,
[Attendance] is displayed correctly. The problem is that I want a total of
[Attendance] too, and I want this total to be displayed even when I have
filtered my records. Examples of the filtering I have are:
Me.Filter = "[Zone] <> Null"
If Not IsNull(Zone1) Then
Me.Filter = Me.Filter & " And [Zone]=Forms!SatsangCountSchedule!Zone1"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70
MsgBox "Records filtered according to zone.", vbInformation,
"ECKANKAR AREA ADMIN"
End If
If Not IsNull(Area1) Then
Dim A As String
A = "SELECT Zones.ZoneID FROM Zones WHERE
(((Zones.Area)=Forms!SatsangCountSchedule!Area1))"
Me.Filter = Me.Filter & " And [Zone] IN (" & A & ")"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70
MsgBox "Records filtered according to Area.", vbInformation,
"ECKANKAR AREA ADMIN"
End If
If Not IsNull(Discourse1) Then
Me.Filter = Me.Filter & " And
[Discourse]=Forms!SatsangCountSchedule!Discourse1"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70
MsgBox "Records filtered according to Discourse studied.",
vbInformation, "ECKANKAR AREA ADMIN"
End If
If Not IsNull(Book1) Then
If Book1 = 1 Then
Me.Filter = Me.Filter & " And [Discourse] IS Null"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70
MsgBox "Records filtered for book discussions.", vbInformation,
"ECKANKAR AREA ADMIN"
ElseIf Book1 = 2 Then
Me.Filter = Me.Filter & " And [Book] IS Null"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70
MsgBox "Records filtered according to discourse studied.",
vbInformation, "ECKANKAR AREA ADMIN"
End If
End If
The above is executed via a button. It starts by getting all records with
"[Zone]<>Null" because the Zone field cannot be null. Zone1, Discourse1,
Area1 are unbound fields I use to filter the records.
So what is a simple way to get totals for the [Attendance] field even when I
run the various filters?
I have not found a good way to tackle this problem: How do I get a
calculated field on my form totaled automatically? I found I could not simply
set the record source of the field to: =Sum([MyFiled]). This method usually
returned #Error.
Now I have a form based on my SatsangClass. A Calculated field [Attendance]
has its record source as:
=IIf(IsNull([SatsangID]),Null,DCount("*","SatsangLesson","[SatsangID]=" &
[SatsangID])).
The SatsangLesson table is joined to the SatsangClass through the SatsangID
field (one to many). So far so good, because when I open my form,
[Attendance] is displayed correctly. The problem is that I want a total of
[Attendance] too, and I want this total to be displayed even when I have
filtered my records. Examples of the filtering I have are:
Me.Filter = "[Zone] <> Null"
If Not IsNull(Zone1) Then
Me.Filter = Me.Filter & " And [Zone]=Forms!SatsangCountSchedule!Zone1"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70
MsgBox "Records filtered according to zone.", vbInformation,
"ECKANKAR AREA ADMIN"
End If
If Not IsNull(Area1) Then
Dim A As String
A = "SELECT Zones.ZoneID FROM Zones WHERE
(((Zones.Area)=Forms!SatsangCountSchedule!Area1))"
Me.Filter = Me.Filter & " And [Zone] IN (" & A & ")"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70
MsgBox "Records filtered according to Area.", vbInformation,
"ECKANKAR AREA ADMIN"
End If
If Not IsNull(Discourse1) Then
Me.Filter = Me.Filter & " And
[Discourse]=Forms!SatsangCountSchedule!Discourse1"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70
MsgBox "Records filtered according to Discourse studied.",
vbInformation, "ECKANKAR AREA ADMIN"
End If
If Not IsNull(Book1) Then
If Book1 = 1 Then
Me.Filter = Me.Filter & " And [Discourse] IS Null"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70
MsgBox "Records filtered for book discussions.", vbInformation,
"ECKANKAR AREA ADMIN"
ElseIf Book1 = 2 Then
Me.Filter = Me.Filter & " And [Book] IS Null"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70
MsgBox "Records filtered according to discourse studied.",
vbInformation, "ECKANKAR AREA ADMIN"
End If
End If
The above is executed via a button. It starts by getting all records with
"[Zone]<>Null" because the Zone field cannot be null. Zone1, Discourse1,
Area1 are unbound fields I use to filter the records.
So what is a simple way to get totals for the [Attendance] field even when I
run the various filters?