Build Value List from SQL Rowsource 1

[MelissaKT]

I have a listbox on a popup form. The listbox has 10 columns. The rowsource is assigned when the popup form opens using an openarg parameter. After it opens, I need to change that SQL rowsource into a value list. Is there an easy way to do this? I'm not sure if this helps or not but here is the current rowsource:

SELECT tblBIDS.ProductID, tblVendor.VendorName, tblBIDS.CategoryID, tblBIDS.Item_No, tblBIDS.Description, tblBIDS.UnitType AS [Unit Type], tblBIDS.UnitCost AS [Price], tblBIDS.SoleSource,tblBIDS.ColorOrSize AS [COLOR=Or Size], tblBIDS.PageNo AS [Page No]
FROM tblVendor
INNER JOIN tblBIDS
ON tblVendor.VendorID = tblBIDS.VendorID

 

[Remou]

I do not see why you need to do this, but you can loop through the recordset and build a value list. For example:

Dim rs As DAO.Recordset
strSQL= Me.[i]lstListBox[i].RowSource
Set rs=CurrentDB.OpenRecordset(strSQL)
Do While Not rs.EOF
   For i = 0 to 9
   strList=strList & rs.Fields(i) & ";"
   Next
Loop
strList=Left(strList,Len(strList)-1)

The above is typed, not tested, so I hope I have it right.

 

[MelissaKT]

Thanks for your help!