Listbox contents from recordset

  • Thread starter Thread starter Eric D.
  • Start date Start date
E

Eric D.

Hi,

Is there a way you can set the contents of a listbox to
use the results of a recorset?

The record set I'm using was build from a SPROC on the SQL
server.

TIA,
Eric
 
Yes,
set the row source type to function name (in this case: CPPLItem_Populate)
Make a function as follows (Parameter order & type declarations must match
the example)

Function CPPLItem_Populate(Fld As Control, id As Long, row As Long, Col As
Long, code As Integer)
Dim strSQL As String, Rs As Recordset
Static entries As Variant, Cnt As Long
On Error Resume Next
Dim ReturnVal As Variant
Static cpplSQL As String
ReturnVal = Null
Select Case code
Case acLBInitialize 'Initialize.
Cnt = GetActiveWindow()
Application.Echo CBool(GetWindowClass(Cnt) = "OModule")
Cnt = 0
entries = Empty
If Len(cpplSQL) = 0 Then cpplSQL =
Left(DBEngine(0)(0).QueryDefs("qryCPPLITEM").SQL,
InStr(DBEngine(0)(0).QueryDefs("qryCPPLITEM").SQL, ";") - 1)
strSQL = cpplSQL & vbCrLf & IIf(InStr(strSQL, "WHERE") > 0, "AND",
"WHERE") & " CPPLI.COMMISSIONINGPACKAGE = " & Me!COMMISSIONINGPACKAGE &
vbCrLf & _
IIf(Me!Show, "", "AND CPPLI.STATUS <> 1") & vbCrLf & Me!ORDER
Set Rs = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenSnapshot)
If Not Rs.EOF Then
Rs.MoveLast
Cnt = Rs.RecordCount
DoEvents
Rs.MoveFirst
entries = Rs.GetRows(Cnt)
DoEvents
End If
Rs.Close
ReturnVal = Cnt
Application.Echo True
'Me.Recalc
Set Rs = Nothing
Case acLBOpen 'Open.
ReturnVal = Timer 'Unique ID number for control.
Case acLBGetRowCount 'Number of rows.
ReturnVal = Cnt
Case acLBGetColumnCount 'Number of columns.
ReturnVal = Fld.ColumnCount
Case acLBGetColumnWidth 'Column width.
ReturnVal = -1 'Use the default width.
Case acLBGetValue 'Get the data.
ReturnVal = entries(Col, row)
Case acLBEnd 'End
Cnt = 0
entries = Empty
ReturnVal = Nothing
End Select
CPPLItem_Populate = ReturnVal
 
Back
Top