Hi all,
I use a excel userform to pass data to Teradata database and then get the resulst into a listbox.
So far I managed some data getting into the listbox, but when a field is empty, code stops. If I comment those fields out in the code, then it will run as the other fields have date in them.
As it is a listbox, I can get the data only in columns.
This is the code I have so far:
Code
Sub Find_company_address()
Dim uiValue As String
Dim login As String
Dim LastRow As Long
Dim conn As ADODB.Connection
Dim rec1 As ADODB.Recordset
Dim thisSql As String
Dim BinCin As String
Dim i As Integer
login = UserForm3.login.Text
uiValue = UserForm3.uiValue.Text
LastRow = Sheets("Results").Cells(60000, 1).End(xlUp).Row
BinCin = Sheets("Data").Cells(6, 3).Value
Set conn = New ADODB.Connection
conn.CommandTimeout = 900
conn.Open "DSN=DatabaseDSN;Databasename=DatabaseName;Uid=" & login & ";Pwd=" & uiValue & ";"
DoEvents
thisSql = "Select ADDR_LINE_1, " & _
"ADDR_LINE_2, " & _
"ADDR_LINE_3, " & _
"ADDR_LINE_4, " & _
"ADDR_LINE_5, " & _
"ADDR_LINE_6, " & _
"pstl_cd_num " & _
"FROM Database_1.CLIENT_STREET_ADDRESS " & _
"Where ((PRTY_ID)= " & BinCin & ")"
LastRow = Sheets("Results").Cells(60000, 1).End(xlUp).Row + 2
Set rec1 = New ADODB.Recordset
rec1.Open thisSql, conn
rec1.MoveFirst
i = 0
With Me.client_details 'this is the listbox name Me.results
.Clear
Do
.AddItem
.List(i, 0) = rec1![ADDR_LINE_1]
.List(i, 1) = rec1![ADDR_LINE_2]
.List(i, 2) = rec1![ADDR_LINE_3]
.List(i, 3) = rec1![ADDR_LINE_4]
.List(i, 4) = rec1![ADDR_LINE_5]
.List(i, 5) = rec1![ADDR_LINE_6]
.List(i, 6) = rec1![pstl_cd_num]
i = i + 1
rec1.MoveNext
Loop Until rec1.EOF
End With
conn.Close
Set conn = Nothing
End SubWhat can I change to make it put eache address line in one row? and how to catch if one of the address line is empty?
And it would be great to be able to copy it from the listbox.
Greetings.
