Recordset as a data source in listbox or combobox

[cmmrfrds]

How do I use a recordset as the data for a listbox or combobox. I can use either box types for displaying the data from a recordset. I came across an example a while back but cannot find it again. I have seem examples using AddItem in VB but there is no AddItem object in Access 2000 for the listbox. I cannot use a string since it maxs out at about 2000 characters.

Thank you.

 

[cuok]

Hi cmmrfrds !

I'm so little you - but those times I’m working a lot to fill/pull ListBoxes.

I have some functions dealing with.

Pls. Give me an example or more explanation for your needs

I’ll be more than glade if I can help u in any way...
CUOK

 

[susanhawk]

For a combobox you can have an unbound combo and in the properties under Row Source write the select statement such as: SELECT Employees.EmployeeLName FROM Employees ORDER BY Employees.EmployeeLName;

Similarly, for a list box that is unbound:
SELECT ReportNames.tblReports FROM tblReports ORDERBY ReportNames.tblReports
This query returns the name of each report by it's name as stored in a table called tblReports.

 

[cmmrfrds]

susanhawk, what you suggest works fine if you want to pull from table(s). I want to pull my data from an already existing recordset.

For example. I am building a recordset with file names from a directory and want to display in one of the boxes.

cuox, below is my code up to loading the listbox.

''-- Create FSO Object
Dim objFSO As Object, objFile As Object, objFileItem As Object
Dim objFolder As Object, objFolderContents As Object
Set objFSO = CreateObject("Scripting.FileSystemObject&quot
Set objFile = objFSO.GetFile("C:\atestdir\myTest.txt&quot
Set objFolder = objFile.ParentFolder
Set objFolderContents = objFolder.Files

''-- Create Disconnected Recordset Object
Dim rs As New ADODB.Recordset
Dim varrfields As Variant
With rs.Fields
.Append "reportName", adVarChar, 125, adFldIsNullable
End With
varrfields = Array("reportName&quot
rs.Open

''- Add directory file names to the recordset
For Each objFileItem In objFolderContents
rs.AddNew varrfields, Array(objFileItem.Name)
Next '- loop through files

rs.Sort = "reportName"

' Populate the ListBox
rs.MoveFirst
Do While Not rs.EOF
'-- WHAT is the code for loading the listbox??????????
'-- Below code is NOT correct. There is no list0.AddItem
'-- in Access 2000
List0.Value = rs.Fields("reportName&quot.Value
rs.MoveNext
Loop
List0.Requery

 

[cuok]

Good morning dear cmmrfrds!

i took your code with me.

i hope i'll come up with somthing tonight!

good luck

CUOK

 

[BillPower]

Hi cmmrfrds, you seem to have posted a similar question twice, here's the same answer as I posted in your other thread.

This is one way to add a Recordset to a Combo or List Box, by creating a temporary table, in the following example all files in c:\my documents and sub folders will be added to a combo/list box called MyList.

Paste the following into your form's declaration section:

Dim strMyCombo As String

Paste this into the form's module:

Sub CreateMyRowsource()
Dim dbs As Database, tdf As TableDef, Fld As Field
Set dbs = CurrentDb
Set tdf = dbs.CreateTableDef(strMyCombo)
With tdf
.Fields.Append .CreateField("Field1", dbText)
End With
dbs.TableDefs.Append tdf
dbs.Close
End Sub

This into the form's unload event:

On Error Resume Next
DoCmd.DeleteObject acTable, strMyCombo 'delete temp table

This into a button's on click event on your form to test it:

Dim dbs As Database, rst As Recordset
Dim i As Long, strFoundRef As String
On Error Resume Next
strMyCombo = Me.Name & Me!MyList.Name 'unique name for temp table
Me!MyList.RowSource = "" 'set your list / combo rowsourcw to nothing
DoCmd.DeleteObject acTable, strMyCombo 'delete temp table
CreateMyRowsource 'create temp table
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset(strMyCombo, dbOpenDynaset) 'set recordset = temp table
With Application.FileSearch
.LookIn = "c:\My Documents"
.Filename = "*.*"
.SearchSubFolders = True
If .Execute() > 0 Then
For i = 1 To .foundfiles.Count
rst.AddNew
rst!Field1 = .foundfiles(i)
rst.Update
Next i
End If
End With
Me!MyList.RowSourceType = "Table/Query"
'set your list / combo to temp table sorted by Field1
Me!MyList.RowSource = "SELECT Field1 FROM " & strMyCombo & " ORDER BY Field1;"
rst.Close

Obviously, rename MyList with your Combo/List name. In Access this has been the only way I've found to get around the 2048 character limit using a Value List with a recordset.

 

[cmmrfrds]

Thanks for the input Bill. Since I was using the listbox just to show the information, I changed to a subform instead.