access vb return mulññtiple values to a form from function

No sure how to return multiple rows

I have this code so far which needs to return student balances.

the while loop will return something like:

here the student has 2 groups so I need to return both
some students only have one group

I need to display this on a form with the idea being that I can calculate the student credit balance which I havnt worked out yet here but if i know how to get this list to a form I will be fine.

Public Function getStudentalance(StID As Long) As Long

Dim db As DAO.Database
Set db = CurrentDb
Dim RstGroups As DAO.Recordset
Dim StrQuery As String
Dim SID As Long
Dim GrpPriceDifferent As Boolean

GrpPriceDifferent = GroupPriceDifferent(SID)

StrQuery "SELECT TblStudent_Session_Link.Student_FK, TblStudent_Session_Link.Group_FK, Count(TblStudent_Session_Link.Group_FK) AS CountOfGroup_FK" _
& " FROM TblStudent_Session_Link" _
& " GROUP BY TblStudent_Session_Link.Student_FK, TblStudent_Session_Link.Group_FK" _
& " HAVING (((TblStudent_Session_Link.Student_FK)=" & SID & "));"

'Debug.Print StrQuery
Set RstGroups = db.OpenRecordset(StrQuery, dbOpenDynaset)

If RstGroups.BOF And RstGroups.EOF Then


With RstGroups

Do While Not RstGroups.EOF

' i will get row data from here.
I need to work out the balance which is not shown here and that needs to be done in vb hence the need to return multiple rows
as opposed to just using this query as a form record source.


End With

End If
End Function

Open in new window

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
hi Peter,

you can send the function what it needs and make a calculated control with Control Source

Open in new window

WHERE [StudentID] is a control name that has the student ID.

at the end of the procedure, you should close the recordset and release the object variables.
set RstGroups = nothing
set db = nothing

Open in new window

here is a basic video on error handling: 

there are 2 more videos in this series, and they discuss cleaning up object variables in the exit code

have an awesome day,
PeterBaileyUkAuthor Commented:
Where two or more balances exist how do i return that list
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
hi Peter,

> "Where two or more balances exist how do i return that list"

make one generic function that is given an SQL statement, field name, and returns a result.  I will just call it GetTheResult

then, to call that, create the SQL statement in each of the functions that is tied to a control.  For instance:

= GetMyBalance1( [StudentID])

= GetMyBalance2( [StudentID])

where GetMyBalance1 and GetMyBalance2 are functions (with better names!) that construct SQL, call the generic processor, which returns a value based on the SQL statement it is given and a fieldname to look for. And then each respective function can return the calculated value to the control source.

Assuming you want to apply criteria and then aggregate... Where should be used instead of Having.  The SQL statement sent could have everything except the criteria. WHERE comes before GROUP BY so the generic function could look for "GROUP BY" and replace with " WHERE myCriteria GROUP BY ". There would not be a Having clause in the example you showed above.

myCriteria would be something like:
"( (TblStudent_Session_Link.Student_FK) =" & pvSID & ")"

Open in new window

where pvSID is the StudentID passed and is a variant so it can be null

... you could store the basic SQL (no criteria) in the TAG property of the control (me.controlname.Tag) you want a value returned to. Then something like this:

function GetMyBalance1() as Currency
   ' ? initialize a default return value? GetMyBalance1 =?
   dim sSql as string
   sSql = me.controlname.Tag
   if not isnull(pvSID) then
      sSql = replace( sSql, "GROUP BY " _
         , " WHERE ( (TblStudent_Session_Link.Student_FK) =" _
         & pvSID & ") GROUP BY ")
      GetMyBalance1 = GetTheResult( sSql, "FieldnameToRead")
   end if
end function

Open in new window

GetTheResult then opens a recordset for the SQL that is passed. It returns the value in the fieldname that was passed

I left out error handling, but it should be added.

have an awesome day,

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
Or you could use a listbox which you fill with the one or more records to retrieve.
That might be much more straight forward.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.