Solved: Populating a listbox with data from sql

[markh1182]

Hi, I have the following code that when run and the parameters passed through will create a table in word that is populated by the results from the sql database.

What I would like to be able to do is populate a listbox on a userform with this information instead, therefore allowing a user to select which item they want so I can populate this information elsewhere.

My code is (I have just taken out the connection string for security reasons):
Sub ApexAddress()
On Error Resume Next

' Initialise database objects
Dim ApexConnection As ADODB.Connection

Dim ApexConnectionString As String

' Ok , lets declare a Table variable to make it easier to manipulate the table

Dim selectedTable As Table
Set selectedTable = Selection.Tables(1)

If selectedTable Is Nothing Then
' problem here , exit
Exit Sub
End If

'get the data
'First establish connection to database , exit and clear up if there is an issue

Set ApexConnection = New ADODB.Connection
On Error GoTo CloseConnection

Dim CmdGetTheData As ADODB.Command
Set CmdGetTheData = New ADODB.Command

Dim rsTheData As New ADODB.Recordset


ApexConnection.ConnectionString = ApexConnectionString
ApexConnection.Open

ApexConnection.CursorLocation = adUseClient

On Error GoTo CloseConnection

'set up stored procedure command

CmdGetTheData.ActiveConnection = ApexConnection
CmdGetTheData.CommandType = adCmdStoredProc
' CommandText = stored procedure name
CmdGetTheData.CommandText = "dbo.ApexGetAddresses"
CmdGetTheData.Parameters.Refresh
' these are the parameters required
CmdGetTheData.Parameters("@ientity").Value = "47010" ' in prep for userform - frmFindContact.txtIentity.Text

'Execute the procedure , fetching the data into the recordset

Set rsTheData = CmdGetTheData.Execute()

'Declare a variable to hold the current row. The column headers should be defined so assume we start at row 2

Dim CurrentTableRow As Row
Set CurrentTableRow = selectedTable.Rows(1)
If CurrentTableRow Is Nothing Then

GoTo CloseConnection

End If

'Loop through the recordset until EOF (end of file )

On Error Resume Next
If rsTheData.EOF = True Then
GoTo CloseConnection
End If

Do While rsTheData.EOF = False

'for each field in the recordset , write the value to the cell
For FieldNumber = 1 To rsTheData.Fields.Count
CurrentTableRow.Cells(FieldNumber).Range.Text = rsTheData.Fields(FieldNumber - 1).Value
Next FieldNumber
rsTheData.MoveNext
If rsdata.EOF = False Then
' Not eof so theres More data to come, so add a row and store it in the currenttablerow object
Set CurrentTableRow = selectedTable.Rows.Add()
End If
Loop
selectedTable.Rows(selectedTable.Rows.Count).Delete

'For each record in the recordset write each column value out to the Word table
' Close the record set

Set selectedTable = Nothing
Set CurrentTableRow = Nothing

rsTheData.Close
Application.StatusBar = False
CloseConnection:

' close and free up the database objects

ApexConnection.Close
Set ApexConnection = Nothing
Set rsTheData = Nothing
Set CmdGetTheData = Nothing

End Sub

I would have thought I'd have to somehow specify a listbox within this section of code Do While rsTheData.EOF = False, but not sure how.

Any help would be appreciated.
Thanks, Mark

[mdmackillop]

Here's a simplified version of the example from this KB Item (http://www.vbaexpress.com/kb/getarticle.php?kb_id=490). Extract both files into the same folder and open the Word document.

[markh1182]

Hi, thanks for the reply.

A followup question from that. How would I input more than one column of data into the box?

My query that I would want to run (through sql, rather than Access database) would have 10 columns, all of which I would want to display. How could I do this?
Thanks, Mark

[mdmackillop]

No time just now, but check out the Listbox.List property. It is used to add data from an array.

[markh1182]

had a search for this listbox.list property and have been able to do what I required.

Thanks for your help.

[mdmackillop]

Can you post the relevant part of your code for the assistance of others?

[markh1182]

This is how I did it.

MyConn on this example is a sql server database, but you could use a MS Access database, you would just need to change this to the path of that database eg. c:\test.mdb

Private Sub UserForm_Initialize()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sSQL As String

sSQL = "select name1, showname, ientity, eclass, dfltaddr, ientitya, restriction from mk_entity where ientity<1000"
MyConn = "Provider=SQLOLEDB.1;Password=[enter password];Persist Security Info=True;User ID=[enter user id];Initial Catalog=[enter database name];Data Source=[enter server name]"
Set cnn = New ADODB.Connection
With cnn
.Open MyConn
End With
Set rst = New ADODB.Recordset
rst.Open sSQL, cnn
Dim i As Integer
rst.MoveFirst
i = 0
With Me.ComboBox1
.Clear
Do
.AddItem
.List(i, 0) = rst![name1]
.List(i, 1) = rst![showname]
.List(i, 2) = rst![ientity]
.List(i, 3) = rst![eclass]
.List(i, 4) = rst![dfltaddr]
.List(i, 5) = rst![ientitya]
.List(i, 6) = rst![restriction]

i = i + 1
rst.MoveNext
Loop Until rst.EOF
End With
rst.Close
cnn.Close
End Sub

[mdmackillop]

Thanks Mark

[untitled]

This is how I did it.



tidy work, I just had to do this for myself for the first time the other day.

I'll post my version when I get into work tomorrow as it follws on from this quite nicely.