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
|
Similar Threads
