What is the difference in defining the following recordsets:

[farah]

Hello Everyone,

What us the difference between the following two methods of declaring the recordset and which one is better? Do you have better than these methods? I do not want to create new record set each time I click on different value in the combo box but it looks like I have to build my SQL statement. Any better way, any hint or help is appretiated. Thanks.
Here are the methods
1.
private sub combo1.click
Dim rs As New ADODB.Recordset
Dim sql As String
dim lIndex as integer

If rs.State = adStateOpen Then
rs.Close
Set rs = Nothing
End If

lIndex = combo1.ListIndex
sql = "SELECT * FROM table1"
sql = sql & " WHERE table1.field1 =
combo1.List(lIndex )"

rs.Open sql, db, adOpenStatic,
adLockOptimistic

end sub

2.
private sub combo1.click
Dim rs As ADODB.Recordset
Dim sql As String
dim lIndex as integer

rs.Close
Set rs = Nothing

lIndex = combo1.ListIndex
sql = "SELECT * FROM table1"
sql = sql & " WHERE table1.field1 =
combo1.List(lIndex )"

set rs = new ADODB.Recordset

rs.Open sql, db, adOpenStatic,
adLockOptimistic

end sub

[ScottF]

I think I know what your asking but I could be wrong. If I'm correct your wanting to hilight a name in a combo box then SQL of that name to recieve all the imfo in tat row or the imfo declaried by the SQL. What I did is place two DOA on a form then a DBCombo and bound the properties of the DBComboas such: DataSource = Data1, Listfield = "The Field you what to fill the DBCombo listdown with", BoundColum = The same as Listfield,
RowRource = Data1
Then Form1 load
ata1.DatabaseName = "Path and Database Name"
Data1.RecordSource = "Your Table Name"
Data1.Refresh
Data2.DatabaseName = "Path and Database Name"
Data2.RecordSource = "Your Table Name"
Data2.Refresh
Then I used a button to excute the SQl
and place this code in it. I' using a table call Member(all member names are located)
My criteria is Lastname(Field in Member table)
im sql As String
Dim last As String
Dim db As Database
Dim dbr As Recordset

last = DBCombo1.Text
sql = "select * from " & "member where lastname = '" & last & "';"
Set db = OpenDatabase("Path and Name of database")
Set dbs = db.OpenRecordset(sql)
Do Until dbs.EOF
'Data2 has Text box boud to it and the 'fields I want to view
Data2.Refresh
Data2.RecordSource = sql
dbs.MoveNext
Loop
It work for me so I hope it will for you
My E-mailAddress is [email protected]