Pass a Recordset from a Module to a Listbox.

[Turbo]

I have a recordsets that I will need to use frequently so I have created a module with code for creating these recordsets. I want to pass the recordsets as needed to a list box in a form. I have confirmed the recordsets in the immediate window but I can not get the records to populate the listbox.

Any ideas?

Turbo

"There is a fine line between confidence and conceit" - Ken Mai

 

[Rolliee]

In order to populate a listbox with a recordset, you have to set the property "recordsource type" as "query/table" in the listbox properties. Otherwise it should work okay. If you want a sample, email and ask for it.

rollie@bwsys.net

 

[cmmrfrds]

A recordset is neither a query or a table. Rolliee, I am curious on how you did this. Can you briefly outline the steps and settings needed assuming the recordset is already created. The way I have done this is using a callback function by making the recordset an array. If there is an easier way, I would like to find out also. Access 2000 does not have an AddItem method like Visual Basic, so that cannot be used either.

 

[Rolliee]

A recordset is the results of a query and thus is synonomous with being called a query.

If you have a listbox named list0

you can, for instance, using a command button, fill the text0 with a recordset - using a query.

sub command0_click()
dim SQL as string


SQL = "SELECT * FROM MYTable;"

me.list0.recordsourcetpye = "Query/Table"
me.list0.recordsource = SQL

exit sub

and voile' the listbox is populated with a recordset from a query.

rollie@bwsys.net

 

[Rolliee]

Excuse, those "recordsosurce's" are "rowsources"

I just checked.

Rollie

 

[cmmrfrds]

Yes, I have used an SQL statement as the row source for a combobox, but this question was about a recordset. A recordset being an object of the Form (Me.Recordset) or an object you can create in DAO or ADODB. The Combobox does not have a recordset object. Specifically, where I have run into a problem is returning a query (stored procedure) resultset to an ADODB.Recordset and then using the recordset to populate the listbox. This was a case of using a stored procedure with parameters which cannot be used as the rowsource for a combobox. That is why I was specifically referring to a recordset object not the results of a query. That is the way I read Turbo's question also. I am interested in a better solution than I have applied for this case, which was using the callback function.

 

[Turbo]

Rolliee and cmmrfrds,

I have just read your comments and thank you for your interest.

Rolliee:
I have tried the "Query" concept with no success. I placed the code behind a command button to populate a listbox with the recordset I created in the module. This did not work, nor did pulling the recordset code into the command button ignoring the module.

cmmrfrds:
Can you explain the "callback" concept and how you made that work?

Thanks,


Turbo

"There is a fine line between confidence and conceit" - Ken Mai

 

[cmmrfrds]

The name of the callback function, UserGroupList goes in the row source type under the data tab in the combobox. This example is reading the Users and Groups collections to show valid users/groups in the app.

In the example under Case acLBInitialize put your recordset and load the array. If you start out by only loading 2 columns the rest of the logic should work and you can expand from that point. The initialize is run once to build the array and from that point the data is extracted from the array to load the listbox.


Example of a callback function.

Function UserGroupList(fld As Control, ID As Variant, _
rowX As Variant, col As Variant, _
code As Variant) As Variant

Dim ur As ADOX.User, gp As ADOX.Group
Static myarray() As Variant
Static row As Integer, rowcount As Integer
Dim cg As New ADOX.Catalog

Dim ReturnVal As Variant
ReturnVal = Null

Select Case code
Case acLBInitialize ' Initialize.
Set cg.ActiveConnection = CurrentProject.Connection
rowcount = cg.Users.Count
row = 0
ReDim Preserve myarray(rowcount, 2)
For Each ur In cg.Users
myarray(row, 0) = ur.Name
For Each gp In ur.Groups
myarray(row, 1) = gp.Name
Next
row = row + 1
If row = rowcount Then Exit For
Next
ReturnVal = rowcount

Case acLBOpen ' Open.
' Generate unique ID for control.
ReturnVal = Timer
Case acLBGetRowCount ' Get number of rows.
ReturnVal = rowcount
Case acLBGetColumnCount ' Get number of columns.
ReturnVal = 2
Case acLBGetColumnWidth ' Column width.
' -1 forces use of default width.
ReturnVal = -1

Case acLBGetValue ' Get data.
'-- zero based array
'Select Case col
' Case 0
' ReturnVal = myarray(rowX, 0)
' Case 1
' ReturnVal = myarray(rowX, 1)
'End Select
ReturnVal = myarray(rowX, col)
Debug.Print "column = "; col

Case acLBEnd ' End.
Erase myarray
End Select
'''''Debug.Print "return value = "; ReturnVal
UserGroupList = ReturnVal
End Function

 

[Rolliee]

Turbo,

If you want a sample that does just this, email me and describe what you wish.

rollie@bwsys.net

 

[Rolliee]

Turbo,

Believe me. YOu can populate a listbox with a query.
This code puts the same recordset that is in a form into listbox

Private Sub cmdTable_Click()
Me.List0.RowSourceType = "Table/Query"

Me.List0.RowSource = Me.RecordSource
End Sub


It works!!!!!!!

rollie@bwsys.net

 

[Turbo]

Rolliee and cmmrfrds,

Rolliee:
The "Table/Query" is not doing the job.

cmmrfrds:
I have to agree with you that the call back is much to complex.



I think before populating the listbox, I need to confirm that the recordset is being passed to the form. I thought that I could just show the recordset data in the listbox to test this but the listbox is proving to be a puzzle in itself.

I also had an idea over the weekend. What about this...

No recordset. In the code I would use SQL to create a select query to populate the listbox and another select query from the items selected in the listbox. Then insert(or append) those items to the appropriate table.

Thanks,


Turbo

"There is a fine line between confidence and conceit" - Ken Mai

 

[Rolliee]

Turbo,

Again, I say, send me your email and I will send you a sample that does just this.

cmmfrds,
A form can certainly have a recordset. That recordset is the result of a query. The recordset can be duplicated by using the same SQL to create whatever you wish.


rollie@bwsys.net

 

[cmmrfrds]

Turbo, it sounds from your last explanation that you can just use a query or sql statement to populate the listbox, and you are in the process of doing it. If you can avoid the recordset object and stick to a query or table that will keep it simple.

 

[Turbo]

cmmrfrds,

Agreed. I think the SQL/Query option is the right direction for this situation. However, I still am interested in learning how to pass a recordset from a module to a listbox.

Rolliee's code is not going to work for the recordset situation. It is refering to an existing table or query which is helpful but still doesn't answer my original delima.

Thanks,



Turbo

"There is a fine line between confidence and conceit" - Ken Mai