HadererDirndl
Registered User.
- Local time
- Today, 03:16
- Joined
- Jan 25, 2016
- Messages
- 19
I am trying to add a calculated field called ‘txtWith’ to a subreport. This field needs to be present for all records, so it can’t be in the header or footer. The code that “fills” the field generally works fine elsewhere – I already used it successfully to calculate the same field content on a form in single view. When I use the same code in the report, the field stays blank. The code sample is posted blow.
My suspicion is that I am doing one of the following things wrong, or a combination of those:
* I am not referencing the fields correctly. I am for example using Me.GroupID instead of using the full path of the subform?
* After much Google I am currently calling the event procedure from the Detail_Paint() event of the subform. This may be the wrong event?
* I may need to call it from the main form, not the subform?
* This has to be done completely differently because it’s a continuous subreport, not a single view?
I have not been able to figure this out on my own. When I change things, errors start happening. I hope someone has done this before and will spot immediately where I'm going wrong. Any help will be very much appreciated.
The use case: The report is a list of students who signed up for a particular class. Each student can be by themselves, or part of a group. For those who are part of the group, the txtWith field will say for every student: “with” and then enumerate the other members of the group. If they are alone, txtWith = “alone”. A group is identified through all group members having the same value in [GroupID].
My suspicion is that I am doing one of the following things wrong, or a combination of those:
* I am not referencing the fields correctly. I am for example using Me.GroupID instead of using the full path of the subform?
* After much Google I am currently calling the event procedure from the Detail_Paint() event of the subform. This may be the wrong event?
* I may need to call it from the main form, not the subform?
* This has to be done completely differently because it’s a continuous subreport, not a single view?
I have not been able to figure this out on my own. When I change things, errors start happening. I hope someone has done this before and will spot immediately where I'm going wrong. Any help will be very much appreciated.
The use case: The report is a list of students who signed up for a particular class. Each student can be by themselves, or part of a group. For those who are part of the group, the txtWith field will say for every student: “with” and then enumerate the other members of the group. If they are alone, txtWith = “alone”. A group is identified through all group members having the same value in [GroupID].
Code:
Private Sub Detail_Paint()
'this populates the Student File's "with" field for people who signed up with a group.
Dim StudentID As String
Dim GroupID As String
Dim GroupNames As String
Dim GroupList As Recordset
Dim GroupSize As Integer
StudentID = Me.PK_Student
GroupID = Me.GroupID
' If there is only one student, the box should say ‘alone’
GroupSize = DCount("*", "tblStudents", "[GroupID]= " & Me.GroupID)
If GroupSize = 1 Then
Me.txtWith = "alone"
Else
' Create a list of all names in the group, except the one in the current reocrd ('me').
Set GroupList = CurrentDb.OpenRecordset("SELECT [PK_Student], [StudentFirstName] & ' ' & [StudentLastName] AS StudentFullName FROM tblStudents WHERE [GroupID]= " & Me.GroupID & " AND [PK_Student] <> " & StudentID)
If GroupList.RecordCount = 1 Then
Me.txtWith = GroupList!StudentFullName
Else
GroupList.MoveFirst
Do Until GroupList.EOF
GroupNames = GroupNames & GroupList!StudentFullName & ", "
GroupList.MoveNext
Loop
' remove the last two chars from string to get rid of extra comma and space
Me.txtWith = "with " & Left$(GroupNames, Len(GroupNames) - 2)
End If
GroupList.Close
End If
End Sub