By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,696 Members | 1,522 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,696 IT Pros & Developers. It's quick & easy.

Populating a listbox from a function

JustJim
Expert 100+
P: 407
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!

Expand|Select|Wrap|Line Numbers
  1. Function FillS2A(fld As Control, ID As Variant, _
  2. row As Variant, col As Variant, code As Variant) _
  3. As Variant
  4. Static survDB As Database
  5. Static rsData As Recordset
  6. Static rsCourses As Recordset
  7. Static strSearch As String
  8. Static strField As String, strStaff As String
  9. Static cntCourse As Integer, cntData As Integer, Counter As Integer
  10. Static S2A_Totals() As Integer
  11. Static DisplayData() As Variant
  12. Dim ReturnVal As Variant
  13.  
  14. Set survDB = CurrentDb()
  15.  
  16. Set rsData = survDB.OpenRecordset("tblReturnData", dbOpenDynaset)
  17. rsData.MoveLast
  18. rsData.MoveFirst
  19.  
  20. Set rsCourses = survDB.OpenRecordset("tblCourses", dbOpenDynaset)
  21. rsCourses.MoveLast
  22. rsCourses.MoveFirst
  23.  
  24. strField = "S2A_PD_Desc"
  25. strStaff = "S2A_PD_Staff"
  26. ReDim S2A_Totals(rsCourses.RecordCount)
  27.  
  28. Select Case code
  29. Case acLBInitialize
  30. ReDim DisplayData(rsCourses.RecordCount, 1)
  31. For cntCourse = 0 To rsCourses.RecordCount - 1
  32. rsCourses.AbsolutePosition = cntCourse
  33. For cntData = 0 To rsData.RecordCount - 1
  34. rsData.AbsolutePosition = cntData
  35. For Counter = 1 To 4
  36. If rsData.Fields(strField & Format(Counter, "@")) = rsCourses!course Then
  37. S2A_Totals(cntCourse) = S2A_Totals(cntCourse) + rsData.Fields(strStaff & Format(Counter, "@"))
  38. DisplayData(cntCourse, 0) = rsData.Fields(strField & Format(Counter, "@"))
  39. DisplayData(cntCourse, 1) = S2A_Totals(cntCourse)
  40. End If
  41. Next Counter
  42. Next cntData
  43. Next cntCourse
  44.  
  45. ReturnVal = True
  46.  
  47. Case acLBOpen
  48. ReturnVal = Timer
  49.  
  50. Case acLBGetRowCount
  51. ReturnVal = rsCourses.RecordCount
  52.  
  53. Case acLBGetColumnCount
  54. ReturnVal = 2
  55.  
  56. Case acLBGetColumnWidth
  57. ' -1 uses default, other values in twips (567 per cm)
  58. ReturnVal = -1
  59.  
  60. Case acLBGetValue
  61. returnvalue = DisplayData(row, col)
  62.  
  63. End Select
  64.  
  65. FillS2A = ReturnVal
  66.  
  67. 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
Jun 1 '07 #1
Share this Question
Share on Google+
5 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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
Jun 1 '07 #2

ADezii
Expert 5K+
P: 8,692
  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:
    Expand|Select|Wrap|Line Numbers
    1. 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.
    Expand|Select|Wrap|Line Numbers
    1. FillS2A = returnVal
  5. Now that I have thoroughly confused you, change Line #61 to:
    Expand|Select|Wrap|Line Numbers
    1. ReturnVal = DisplayData(row, col)
  6. Let me know how you make out.
Jun 1 '07 #3

ADezii
Expert 5K+
P: 8,692
  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:
    Expand|Select|Wrap|Line Numbers
    1. 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.
    Expand|Select|Wrap|Line Numbers
    1. FillS2A = returnVal
  5. Now that I have thoroughly confused you, change Line #61 to:
    Expand|Select|Wrap|Line Numbers
    1. ReturnVal = DisplayData(row, col)
  6. Let me know how you make out.
Jun 1 '07 #4

JustJim
Expert 100+
P: 407
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.
Jun 3 '07 #5

ADezii
Expert 5K+
P: 8,692
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.
Jun 4 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.