ado connection sql script select via listbox

N

Newman Emanouel

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
 
T

Tom Ogilvy

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.
 
N

Newman Emanouel

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 said:
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 said:
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
Click to expand...
Click to expand...
 
T

Tom Ogilvy

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Connection Problem (Oracle and Access join) 5
Invalid SQL Statement - View Northwind.mdb 1
Stopping duplications been entered into a listbox 8
Cannot select items in listbox 0
cascading listbox in word vba userform 0
sending search resaults to a listbox 0
ListBox Selected Items into an Array 3
Connect To CSV File via ADO 4

Top