• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 41
  • Last Modified:

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:

ex
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

SID = StID
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

Else

With RstGroups

.MoveFirst
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.


.MoveNext
Loop


End With



End If
End Function

Open in new window

0
PeterBaileyUk
Asked:
PeterBaileyUk
  • 2
2 Solutions
 
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
=getStudentalance([StudentID])

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.
RstGroups.close
set RstGroups = nothing
set db = nothing

Open in new window

here is a basic video on error handling:
https://www.experts-exchange.com/videos/1478/Basic-Error-Handling-code-for-VBA-and-Microsoft-Office.html 

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

have an awesome day,
crystal
0
 
PeterBaileyUkAuthor Commented:
Where two or more balances exist how do i return that list
0
 
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:

ControlSource1:
= GetMyBalance1( [StudentID])

ControlSource2:
= 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.

https://www.experts-exchange.com/videos/1478/Basic-Error-Handling-code-for-VBA-and-Microsoft-Office.html

have an awesome day,
crystal
0
 
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now