ado connection sql script select via listbox

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

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

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

 

[Guest]

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

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

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

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

 

[Guest]

Tom

Thanks - Your the best chuck out the rest !!

Regards

Newman