Hello All,
I need to build another query using the same data. However, I have no idea how to
reference the records from the previous recordset.
I have put the code below, if you can help me in this, it would be great!
Dim rs_tw As ADODB.Recordset
Set rs_tw = New ADODB.Recordset
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
rs_tw.ActiveConnection = cnn
rs_tw.LockType = adLockReadOnly
rs_tw.CursorType = adOpenDynamic
Dim strSQL As String
'Define Query and Run it
'CASE 1: For TireWidth
If (Len(Nz(txttirewidth, "")) > 0 And Len(Nz(txttirewidthop, "")) > 0 And Len(Nz(txttirewidthvalue, "")) > 0) Then
strSQL = "SELECT DISTINCT[list].[Field3],[list].[Field7] FROM List WHERE[list].[Field3] Like '%" & Me!txttirewidthvalue & "%';"
'strSQL = "SELECT TOP 10 [Field3], [Field7] FROM List WHERE [Field3] Like '%" & Replace(Me!txttirewidthvalue, "'", "''") & "%'"
rs_tw.Open strSQL
lstResult.RowSource = vbNullString
rs_tw.MoveFirst
'If (txtaspectratiovalue = Null) Then
Do While Not rs_tw.EOF
lstResult.additem rs_tw![Field3]
rs_tw.MoveNext
Loop
rs_tw.Close
'End If
End If
'CASE 2: For ASPECT RATIO
Dim rs_ar As ADODB.Recordset
Set rs_ar = New ADODB.Recordset
Dim cnn_ar As ADODB.Connection
Set cnn_ar = CurrentProject.Connection
rs_ar.ActiveConnection = cnn_ar
rs_ar.LockType = adLockReadOnly
rs_ar.CursorType = adOpenDynamic
If (Len(Nz(txtaspectratio, "")) > 0 And Len(Nz(txtaspectratioop, "")) > 0 And (Len(Nz(txtaspectratiovalue, "") > 0))) Then
' Now I want to select the same records: rs_tw![Field3] and use the foll. WHERE statement to filter it and retrive the records. I have used " ? " for the missing blocks.
strSQL = " SELECT DISTINCT " ? " FROM ? WHERE ? Like '%" & "/" & Me.txtaspectratiovalue & "R" & "%')"
rs_ar.Open strSQL
rs_ar.MoveFirst
Do While Not rs_ar.EOF
lstResult.additem rs_ar! ?
rs_ar.MoveNext
Loop
rs_ar.Close
End If
'End If
Nikhil