Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ado connection sql script select via listbox
Dear All
I have been struggling with this problem for a while now and need assistance. I have two list boxs. One with a list of customer id's and I want the user to select the custid and the second list box to display the addres information. I have the following code already and it just doesnt work. Please help Dim cnn1 As New ADODB.Connection Dim rst1 As New ADODB.Recordset cnn1.Open "PROVIDER=SQLOLEDB;" & _ "Server=XXXXXXXX;INITIAL CATALOG=XXXX;Integrated Security=sspi" rst1.Open "SELECT CUSTNAME, Address1, address2, City FROM RM00101 where custnmbr in ('" & ListBox1.Value & "') ;", _ cnn1, adOpenStatic rst1.MoveFirst With Me.ListBox2 .Clear Do .AddItem rst1![Custnmbr] .AddItem rst1![CUSTname] .AddItem rst1![Address1] .AddItem rst1![Address2] .AddItem rst1![City] rst1.MoveNext Loop Until rst1.EOF End With |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ado connection sql script select via listbox
I don't know if this is what you are looking for or not, but
If I had 5 items to add to a 5 column list box Private Sub UserForm_Initialize() With Me.ListBox1 .ColumnCount = 5 .AddItem "A1" .List(.ListCount - 1, 1) = "B1" .List(.ListCount - 1, 2) = "C1" .List(.ListCount - 1, 3) = "D1" .List(.ListCount - 1, 4) = "E1" End With End Sub The first column in List is zero. -- Regards, Tom Ogilvy "Newman Emanouel" wrote: Dear All I have been struggling with this problem for a while now and need assistance. I have two list boxs. One with a list of customer id's and I want the user to select the custid and the second list box to display the addres information. I have the following code already and it just doesnt work. Please help Dim cnn1 As New ADODB.Connection Dim rst1 As New ADODB.Recordset cnn1.Open "PROVIDER=SQLOLEDB;" & _ "Server=XXXXXXXX;INITIAL CATALOG=XXXX;Integrated Security=sspi" rst1.Open "SELECT CUSTNAME, Address1, address2, City FROM RM00101 where custnmbr in ('" & ListBox1.Value & "') ;", _ cnn1, adOpenStatic rst1.MoveFirst With Me.ListBox2 .Clear Do .AddItem rst1![Custnmbr] .AddItem rst1![CUSTname] .AddItem rst1![Address1] .AddItem rst1![Address2] .AddItem rst1![City] rst1.MoveNext Loop Until rst1.EOF End With |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ado connection sql script select via listbox
Tom
I am trying to do two this here The first is to use listbox1 to select my criteria for the sql script and Secondly based on what was selected in listbox1 display the resulting (In this case address Info) in listbox2 I hope this makes it clearer, I tried what you have suggested abd it does create the columns required but I am not able to list the information based on the sql selection Regards Newman "Tom Ogilvy" wrote: I don't know if this is what you are looking for or not, but If I had 5 items to add to a 5 column list box Private Sub UserForm_Initialize() With Me.ListBox1 .ColumnCount = 5 .AddItem "A1" .List(.ListCount - 1, 1) = "B1" .List(.ListCount - 1, 2) = "C1" .List(.ListCount - 1, 3) = "D1" .List(.ListCount - 1, 4) = "E1" End With End Sub The first column in List is zero. -- Regards, Tom Ogilvy "Newman Emanouel" wrote: Dear All I have been struggling with this problem for a while now and need assistance. I have two list boxs. One with a list of customer id's and I want the user to select the custid and the second list box to display the addres information. I have the following code already and it just doesnt work. Please help Dim cnn1 As New ADODB.Connection Dim rst1 As New ADODB.Recordset cnn1.Open "PROVIDER=SQLOLEDB;" & _ "Server=XXXXXXXX;INITIAL CATALOG=XXXX;Integrated Security=sspi" rst1.Open "SELECT CUSTNAME, Address1, address2, City FROM RM00101 where custnmbr in ('" & ListBox1.Value & "') ;", _ cnn1, adOpenStatic rst1.MoveFirst With Me.ListBox2 .Clear Do .AddItem rst1![Custnmbr] .AddItem rst1![CUSTname] .AddItem rst1![Address1] .AddItem rst1![Address2] .AddItem rst1![City] rst1.MoveNext Loop Until rst1.EOF End With |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ado connection sql script select via listbox
ADO isn't something I do much with, but
Maybe something like this: Dim cnn1 As New ADODB.Connection Dim rst1 As New ADODB.Recordset cnn1.Open "PROVIDER=SQLOLEDB;" & _ "Server=XXXXXXXX;INITIAL CATALOG=XXXX;Integrated Security=sspi" rst1.Open "SELECT CUSTNAME, Address1, address2, City FROM RM00101 where custnmbr = "'" & ListBox1.Value & "'" ;", _ cnn1, adOpenStatic rst1.MoveFirst rst1.MoveLast rst1.MoveFirst With Me.ListBox2 .Clear Do .AddItem rst1.Fields("Custnmbr") .List(.Listcount -1, 1) = rst1.Fields("CUSTname") .List(.Listcount -1, 2) = rst1.Fields("Address1") .List(.Listcount -1, 3) = rst1.Fields("Address2") .List(.Listcount -1, 4) = rst1.Fields("City") rst1.MoveNext Loop Until rst1.EOF End With -- Regards, Tom Ogilvy "Newman Emanouel" wrote: Tom I am trying to do two this here The first is to use listbox1 to select my criteria for the sql script and Secondly based on what was selected in listbox1 display the resulting (In this case address Info) in listbox2 I hope this makes it clearer, I tried what you have suggested abd it does create the columns required but I am not able to list the information based on the sql selection Regards Newman "Tom Ogilvy" wrote: I don't know if this is what you are looking for or not, but If I had 5 items to add to a 5 column list box Private Sub UserForm_Initialize() With Me.ListBox1 .ColumnCount = 5 .AddItem "A1" .List(.ListCount - 1, 1) = "B1" .List(.ListCount - 1, 2) = "C1" .List(.ListCount - 1, 3) = "D1" .List(.ListCount - 1, 4) = "E1" End With End Sub The first column in List is zero. -- Regards, Tom Ogilvy "Newman Emanouel" wrote: Dear All I have been struggling with this problem for a while now and need assistance. I have two list boxs. One with a list of customer id's and I want the user to select the custid and the second list box to display the addres information. I have the following code already and it just doesnt work. Please help Dim cnn1 As New ADODB.Connection Dim rst1 As New ADODB.Recordset cnn1.Open "PROVIDER=SQLOLEDB;" & _ "Server=XXXXXXXX;INITIAL CATALOG=XXXX;Integrated Security=sspi" rst1.Open "SELECT CUSTNAME, Address1, address2, City FROM RM00101 where custnmbr in ('" & ListBox1.Value & "') ;", _ cnn1, adOpenStatic rst1.MoveFirst With Me.ListBox2 .Clear Do .AddItem rst1![Custnmbr] .AddItem rst1![CUSTname] .AddItem rst1![Address1] .AddItem rst1![Address2] .AddItem rst1![City] rst1.MoveNext Loop Until rst1.EOF End With |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ado connection sql script select via listbox
Tom
Thanks - Your the best chuck out the rest !! Regards Newman "Newman Emanouel" wrote: Dear All I have been struggling with this problem for a while now and need assistance. I have two list boxs. One with a list of customer id's and I want the user to select the custid and the second list box to display the addres information. I have the following code already and it just doesnt work. Please help Dim cnn1 As New ADODB.Connection Dim rst1 As New ADODB.Recordset cnn1.Open "PROVIDER=SQLOLEDB;" & _ "Server=XXXXXXXX;INITIAL CATALOG=XXXX;Integrated Security=sspi" rst1.Open "SELECT CUSTNAME, Address1, address2, City FROM RM00101 where custnmbr in ('" & ListBox1.Value & "') ;", _ cnn1, adOpenStatic rst1.MoveFirst With Me.ListBox2 .Clear Do .AddItem rst1![Custnmbr] .AddItem rst1![CUSTname] .AddItem rst1![Address1] .AddItem rst1![Address2] .AddItem rst1![City] rst1.MoveNext Loop Until rst1.EOF End With |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Double click item in Listbox to select item and close Listbox | Excel Programming | |||
Select a ListBox value with code | Excel Programming | |||
Listbox Multi Select | Excel Programming | |||
I need to select some values in a Listbox???? | Excel Programming | |||
Select from table and listbox | Excel Programming |