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