listbox problem

[Jason]

I got the following code, it's vb script, I want to use
that code implement it in ms access, and use the data I'm
getting and populating an unbound listbox with that data,
how can I do that? Thanks!


===========================================================


Function GetRecs( pId )

Dim oConn
Dim oCmd
Dim oRs

Dim sOut

Dim sCrLf


sCrLf = Chr(13) & Chr(10)

Set oConn = CreateObject("ADODB.Connection")

oConn.IsolationLevel = 1048576 ' adXactIsolated
oConn.CursorLocation = 3 ' adUseClient
oConn.Mode = 1 ' adModeRead
oConn.Provider = "SQLOLEDB.1"
oConn.Properties("Data Source").Value = "sqlserverName"

oConn.Properties("Initial Catalog").Value = "myData"
oConn.Properties("User ID").Value = "myData_usr"
oConn.Properties("Password").Value = "somepwd"

oConn.Open

Set oCmd = CreateObject("ADODB.Command")
oCmd.ActiveConnection = oConn
oCmd.CommandType = 4
oCmd.CommandText = "mach_DTL"
oCmd.Parameters.Append oCmd.CreateParameter("@pId", 3,
1, , ID)
Set oRs = oCmd.Execute

If Not oRs Is Nothing Then
Do Until oRs.EOF = True
sOut = sOut & "item:" & oRs.Fields
("item").Value & sCrLf
sOut = sOut & "po #:" & oRs.Fields
("po").Value & sCrLf
sOut = sOut & "vendor:" & oRs.Fields
("vendor").Value & sCrLf
sOut = sOut & "date:" & oRs.Fields
("create").Value & sCrLf
sOut = sOut & "cost:" & oRs.Fields
("cost").Value & sCrLf
sOut = sOut & "qty:" & oRs.Fields
("quantity").Value & sCrLf
sOut = sOut
& "********************************************************
" & sCrLf

oRs.MoveNext
Loop

oRs.Close
End If

GetRecs = sOut

oConn.Close
Set oRs = Nothing
Set oCmd = Nothing
Set oConn = Nothing

End Function

 

[MSaccess.exe Program Error / Using CreateForm]

The listbox will accept a delimited string for it's data.
In your code, change the sCrLf variable assignment to:
sCrLf = ";"

In your form's listbox property sheet,
change the Row Source Type to "Value List".

In the event procedure your using to populate the listbox
you can assign GetRecs to the rowsource:

Listbox.RowSource = GetRecs(id)

 

[Jason]

thank's a lot man! I got round it before I read your
message, but I used a lot of extra coding that I didn't
need, your way is easier, thanks.

 

[Guest]

You should be aware that the listbox has some size
limitations, so if you return a large recordset you'll get
and error.
It's Something like 2564 characters,
so "LISTBOX;PROBLEM" represents 15 characters.
Not sure if it's the same if bound to table or querey.