Populating a listbox from a function

[JustJim]

Well this has been fun, but I now elect to "phone a friend"

I have an Access application in which the operator can enter/choose the name of a training course and enter the number of teachers from a particular school who have attended that course.

They can do this in four sets of combo box (for course name) and text box (number attended) and then move to the next record (another school's data) and do it all again.

By looping through the schools data table and the course title table, I can add up how many teachers in the whole system have attended each course. Easy!

Now I want to display that data and I thought that a listbox on a form would suit. I wrote the following function to be used as the listbox's Row Source Type and when the form is opened, the function is called several times and returns data for acLBInitialize, acLBOpen etc. The problem is that Access never asks the function for acLBGetValue so my poor lonely listbox stays empty!

Function FillS2A(fld As Control, ID As Variant, _
row As Variant, col As Variant, code As Variant) _
As Variant
Static survDB As Database
Static rsData As Recordset
Static rsCourses As Recordset
Static strSearch As String
Static strField As String, strStaff As String
Static cntCourse As Integer, cntData As Integer, Counter As Integer
Static S2A_Totals() As Integer
Static DisplayData() As Variant
Dim ReturnVal As Variant
 
Set survDB = CurrentDb()
 
Set rsData = survDB.OpenRecordset("tblReturnData", dbOpenDynaset)
rsData.MoveLast
rsData.MoveFirst
 
Set rsCourses = survDB.OpenRecordset("tblCourses", dbOpenDynaset)
rsCourses.MoveLast
rsCourses.MoveFirst
 
strField = "S2A_PD_Desc"
strStaff = "S2A_PD_Staff"
ReDim S2A_Totals(rsCourses.RecordCount)
 
Select Case code
Case acLBInitialize
ReDim DisplayData(rsCourses.RecordCount, 1)
For cntCourse = 0 To rsCourses.RecordCount - 1
rsCourses.AbsolutePosition = cntCourse
For cntData = 0 To rsData.RecordCount - 1
rsData.AbsolutePosition = cntData
For Counter = 1 To 4
If rsData.Fields(strField & Format(Counter, "@")) = rsCourses!course Then
S2A_Totals(cntCourse) = S2A_Totals(cntCourse) + rsData.Fields(strStaff & Format(Counter, "@"))
DisplayData(cntCourse, 0) = rsData.Fields(strField & Format(Counter, "@"))
DisplayData(cntCourse, 1) = S2A_Totals(cntCourse)
End If
Next Counter
Next cntData
Next cntCourse
 
ReturnVal = True
 
Case acLBOpen
ReturnVal = Timer
 
Case acLBGetRowCount
ReturnVal = rsCourses.RecordCount
 
Case acLBGetColumnCount
ReturnVal = 2
 
Case acLBGetColumnWidth
' -1 uses default, other values in twips (567 per cm)
ReturnVal = -1
 
Case acLBGetValue
returnvalue = DisplayData(row, col)
 
End Select
 
FillS2A = ReturnVal
 
End Function

Edit: Hmm don't think I did the code tag thing properly
Any hints, pointers or straight-out help will be appreciated.

Jim

[MMcCarthy]

Firstly you don't need to include the =vb in the closing tag

Can you post the code you are using to pass this return to the Row Source of the list box

[ADezii]

1. For future reference, please indent your code. In its present state, it is very difficult to read and even harder to decipher.
2. You've selected the most efficient mechanism for populating a List Box, (a list filling Callback Function), but unfortunately the most com[plicated. Access allows you to supply a Function that tells it about the List or Combo Box you want displayed and has virtually no limitations. In order for Access to communicate with your Function, it does however, require a very specific interface.
3. Let's see if we can eliminate the most obvious problem first.
   

   The problem is that Access never asks the function for acLBGetValue so my poor lonely listbox stays empty!

4. Access does request, and receives the acLBGetValue but the problem seems to be that you are assigning this value to the wrong Variable. In Line #12 your Declaration was:
   

   Dim ReturnVal As Variant

   but in Line #61, you are attempting to assign a value to the Variable returnvalue which was never even Declared and in Line #65, you are setting your Function equal to ReturnVal NOT returnvalue.
   

   FillS2A = returnVal

5. Now that I have thoroughly confused you, change Line #61 to:
   

   ReturnVal = DisplayData(row, col)

6. Let me know how you make out.

[ADezii]

1. For future reference, please indent your code. In its present state, it is very difficult to read and even harder to decipher.
2. You've selected the most efficient mechanism for populating a List Box, (a list filling Callback Function), but unfortunately the most complicated. Access allows you to supply a Function that tells it about the List or Combo Box you want displayed and has virtually no limitations. In order for Access to communicate with your Function, it does however, require a very specific interface.
3. Let's see if we can eliminate the most obvious problem first.
   
4. Access does request, and receives the acLBGetValue but the problem seems to be that you are assigning this value to the wrong Variable. In Line #12 your Declaration was:
   

   Dim ReturnVal As Variant

   but in Line #61, you are attempting to assign a value to the Variable returnvalue which was never even Declared and in Line #65, you are setting your Function equal to ReturnVal NOT returnvalue.
   

   FillS2A = returnVal

5. Now that I have thoroughly confused you, change Line #61 to:
   

   ReturnVal = DisplayData(row, col)

6. Let me know how you make out.

[JustJim]

Boy do I feel silly!! returnvalue instead of ReturnVal???? I should have noticed that the parser didn't capitalise the R and the V for me. Thanks for your help, sorry for the late reply but I was away for the weekend.

I do indent my code by the way, otherwise I couldn't read it either! That was my first go with the new {Code=vb} thingy and I guess I didn't get it right. So you don't need it on the closing {/Code} marker, is that right?


Thanks again for the prompt and helpful advice.

[ADezii]

Boy do I feel silly!! returnvalue instead of ReturnVal???? I should have noticed that the parser didn't capitalise the R and the V for me. Thanks for your help, sorry for the late reply but I was away for the weekend.

I do indent my code by the way, otherwise I couldn't read it either! That was my first go with the new {Code=vb} thingy and I guess I didn't get it right. So you don't need it on the closing {/Code} marker, is that right?


Thanks again for the prompt and helpful advice.

1. Use [ and ] instead of braces { } when using your Code Tags, the rest is correct.
2. Glad to help. Just a little advice if you are interested - this situation could have been completely eliminated before and also in the future by using Option Explicit which forces you to explicitly declare all Variables. You can easily set this Option via: in the VBA Environment ==> Tools ==> Options ==> Editor Tab ==> Require Variable Declaration. If you had this Option set previously, it would have recognized returnval as an Undeclared Variable. Not using Option Explicit, allows you to 'implicitly' declare a Variable (Variant) as soon as you assign a value or refer to it.