473,320 Members | 1,862 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Retrieve records from another recordset

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
May 9 '11 #1
1 1642
Rabbit
12,516 Expert Mod 8TB
Please use code tags when posting code.

You can either use queries instead of coding in the SQL, or you can subquery the original SQL in the FROM clause of your new SQL.
May 9 '11 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

5
by: Rich Protzel | last post by:
Hello, If I run an action SP from MS Access using ADO: ... cmd.execute where the SP is something like Create... Update tbl1 set fld1 = 'something' where... how can I retrive the count of...
2
by: Colm O'Hagan | last post by:
Hi there, I having a problem with a database I'm setting up, I would be delighted if someone out there could help. The database I'm setting up is a task register datebase, it will be used to...
2
by: Sreedhar Vankayala | last post by:
Hi, I have a table IV00108wc which contains several thousand records in whcih ITEMNMBR is the primary key. I have a com+ object returning recordset which has ITEMNMBR and another details...
13
by: kev | last post by:
Hi all, I have created a database for equipments. I have a form to register the equipment meaning filling in all the particulars (ID, serial, type, location etc). I have two buttons at the end...
0
by: padhuwork | last post by:
Hi, I want to create a Windows DLL using VC++ 6.0 which connects to SQL Server. After establishing the connection, I want to retrieve records from table (query) and out put the recordset to a...
4
by: felicia | last post by:
Hi All, Below is my code to delete records: adodcAllEntries.Recordset.MoveFirst Do While (adodcAllEntries.Recordset.EOF = False) If adodcAllEntries.Recordset.Fields(0) = selected_id Then...
3
by: VBANovice | last post by:
Below is my code....I am simply connecting one database to another via VBA. I cannot figure out how to take the records out from the recordset and put them in a table. Me stumped... 'create...
1
by: Tomino | last post by:
Hi all, I have a sticky question, it's killing me for weeks so i hope to find the answer here. I have a query to filter records from a table. From these results I want to edit the records with...
4
by: Evanescent | last post by:
Hi Guys, I am trying to create a form which allows the users to retrieve records based on the values entered or chosen in the various combo boxes and textboxes, such as the customer's name, invoice...
7
by: MrDeej | last post by:
Hello my good people! It have come to my mind that stored procedures can help us speed up the retrival of records from our SQL server. I have now made a stored procedure at our server which is...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.