Issues with a calculated field using VBA on a subreport (1 Viewer)

HadererDirndl

Registered User.
Local time
Today, 04:12
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].

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
 

HadererDirndl

Registered User.
Local time
Today, 04:12
Joined
Jan 25, 2016
Messages
19
Thanks CJ_London, that is a very useful and elegant function. If I understand it correctly, it doesn't _quite _ do what I need: I need the resulting string to exclude the record I am currently on, which my code actually does. I thought I only have a reference problem in my code or when I call it - because it works on the form, just not on the subreport? Do you disagree? Thanks!
 

sneuberg

AWF VIP
Local time
Today, 04:12
Joined
Oct 17, 2014
Messages
3,506
I converted your subroutine into a function. I added some stuff along with comments. Maybe you could put this in a module and then use in in the record source query of the subreport. You would just add an expression something like:

Code:
Group Members:  GetGroup([StudentID], [GroupID])

Here's the function:
Code:
Public Function GetGroup(StudentID As Variant, GroupID As Variant) As Variant

'this populates the Student File's "with" field for people who signed up with a group.
Dim GroupNames As String
Dim GroupList As Recordset
Dim GroupSize As Integer
' If there is only one student, the box should say ‘alone’
GroupSize = DCount("*", "tblStudents", "[GroupID]= " & GroupID)
If GroupSize = 1 Then
    GetGroup = "alone"
    Exit Function
End If
    
' 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]= " & GroupID & " AND [PK_Student] <> " & StudentID)
If GroupList.EOF And GroupList.BOF Then 'If there are no records movelast will cause an error
    GetGroup = Null
    Exit Function
End If
GroupList.MoveLast  'to populate the RecordCount
GroupList.MoveFirst
If GroupList.RecordCount = 1 Then
    GetGroup = GroupList!StudentFullName
     Exit Function
End If
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
GetGroup = "with " & Left$(GroupNames, Len(GroupNames) - 2)
GroupList.Close

End Function
 

HadererDirndl

Registered User.
Local time
Today, 04:12
Joined
Jan 25, 2016
Messages
19
Thank you so much! I just tested this on a form and it works beautifully. (I just had to change one thing: it should refer to PK_Student, not StudentID I think). Will test on the subreport next.
 
Last edited:

Users who are viewing this thread

Top Bottom