multiple fields in listbox with user-defined function rowsource

[sub5]

Hi All

I have a user-defined function (fnclistbox) filling a listbox. I want it to populate all the rows and all fields from a recordset. I can get all the rows but how do i get all the fields? Presumably it is something to do with the line "fnclistbox = rst!Engine" in the code below.

I have no acLBGetColumnCount case statement in the code element as I have put 5 as the number of columns in my listbox properties.

Case acLBGetValue
On Error Resume Next
Debug.Print "lngRow:" & lngRow
Debug.Print "lngPreviousRow:" & lngPreviousRow
lngMove = lngRow - lngPreviousRow
Debug.Print "lngMove:" & lngPreviousRow
Debug.Print "varBM:" & varBM
rst.Move lngMove, varBM
varBM = rst.Bookmark
If Err = 0 Then
lngPreviousRow = lngRow
fnclistbox = rst!Engine
Else
fnclistbox = Null
End If
On Error GoTo 0 'start again

 

[Troyston]

Try simplifying it by using some ad hoc SQL to define the list box content:

strSQL = "SELECT Field1 AS F1, Field2 AS F2 FROM Table WHERE Criteria;"
ListBox.RowSource= strSQL

If you need a few things that aren't from a table, the a UNION query can offer a facility for that. Look it up on

<http:

http://www.mvps.org/access>

Troyston :S

Troy Vinson
Trading as IT Supportman

http://www.itsupportman.co.uk

 

[sub5]

A bit confused Troy.
Where are you telling me to put the strSQL?
Is the strSQL a variable
In a function in the form module that is called?

 

[Troyston]

strSQL is a String type variable:

Copy and paste this then take a look.

Private Sub txtNomSiteToFind_Change()
Dim strNomSiteToFind As String
Me.Refresh
txtNomSiteToFind.SetFocus
strNomSiteToFind = txtNomSiteToFind.Text
strSQL = "SELECT Site, VCR, RptTag AS Tag " & _
"FROM LNK_OL_Nominations INNER JOIN PROD_Site ON LNK_OL_Nominations.Site = PROD_Site.SiteCode " & _
"WHERE (Site Like '" & strNomSiteToFind & "*') " & _
"ORDER BY Site;"
lstNominatedSite.RowSource = strSQL
lstNominatedSite.Requery
txtNomSiteToFind.SelStart = txtNomSiteToFind.SelLength
End Sub


Troy Vinson
Trading as IT Supportman

http://www.itsupportman.co.uk

 

[sub5]

Hi Troy, thankyou for your continued support.

I can get function EngList below to work using the principle I think you are telling me.

However I’d still be interested to know how to make my original code (in first post above) work ie how to get the listbox to return all the recordset fields. Ie get line
fnclistbox = rst!Engine
to return all the recordset fields not just the Engine one.


Public Function EngList()

Dim strSQL2 As String, strSQL3 As String
Dim upper1 As Integer
Dim row1 As Integer
upper1 = UBound(arrEng, 2)
For row1 = 0 To upper1
strSQL2 = strSQL2 & " Or ([Tbl1].Fld1)=" & arrEng(0, row1)
Next
strSQL2 = Right(strSQL2, Len(strSQL2) - 4)
strSQL3 = "SELECT [Tbl1].Fld1, [Tbl2].Fld2 FROM ([Tbl2] INNER JOIN [Tbl1] ON [Tbl2].Fld2 = [Tbl1].Fld2) LEFT JOIN [Tbl3] ON [Tbl1].Fld1 = [Tbl3].Fld1 WHERE ((" & strSQL2 & ") AND (([Tbl3].Fld1) Is Null));"
Me!lstFld1.RowSource = strSQL3
Me!lstFld1.Requery

End Function