Basing a Recordset on a Recordset

[Sorrells]

In VBA coding, Access97, can I reference a recordset in a 2nd recordset? My current code returns a Run Time Error of 3421: “Data Type Conversion Error” when doing so in a fairly simple set of syntax. Ultimately, if I had my way, a 3rd recordset would be referenced as well.

I have two abbreviated recordsets displayed below to give an idea of what I am talking about but I will mostly appreciate general comments as to if a recordset can be referenced within a 2nd recordset.

Set rst_All_Tasks = dbs.OpenRecordset("SELECT DISTINCTROW " & Table_Name & ".Task, " & _
Table_Name & "." & OG_abbrev & " AS OG_AB, " & _
Table_Name & ".DFT," & _
Table_Name & ".Priority," & _
Table_Name & ".Freq_No," & _
Table_Name & ".Task_ID " & _
"FROM " & Table_Name & _
" ORDER BY " & Table_Name & ".Priority&quot


Set rstAvail_Tasks = dbs.OpenRecordset("SELECT DISTINCTROW " & rst_All_Tasks!Task, _
rst_All_Tasks!og_ab, _
rst_All_Tasks!Priority, _
rst_All_Tasks!Freq_No & _
" FROM rst_All_Tasks&quot

NOTE1: If in the 2nd SQL I use quotes as I normally do, then Access responds that it cannot find the table or query.

NOTE2: The 1st SQL statement uses variables Table_Name & OG_Abbrev
Regards, Sorrells

 

[hermanlaksko]

You can not use a recordset as a base for a new recordset, access will not allow this ..... but you can use your recordset1 to create a table and then use this table as your base for the recordset2.

Do not forget to delete the TempRecordset tbl before creating it :-D

Rgds

Herman

 

[danvlas]

Why would you base a recordset on another recordset? You already have what you need, so why wasting time and resources? You can filter or sort a recordset to get what you need.

The basic idea of any recordset is "just get what you need". If you need something else, open another recordset...

And the creating of a temp table to use it as a recordset is not really a good idea, especially in a multi-user environment...

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[Sorrells]

Responses.........

hermanlaksko, what you are saying makes my problem understandable. It is somewhat a shame. I wanted to stay away from creating tables that would have a significant amount of record addition/deletion to keep dB bloating to a minimum.

danvlas, I wish I did have all that I need. Conceptually the situation is like this. Recordset A is derived from Table A and contains all possible records needed. Recordset B is derived from Table B and contains a varying amount of records that have already been chosen from table A always a complete subset up to possibly the entire set of records in recordset A. In the form, I have a listbox that is to display the records that are in Recordset A but NOT in recordset B.

The recordset rstAvail_Tasks is intended to return this filtered recordset. Since the records are derived from two different tables, I was thinking that I would need to get them on common ground. The use of a variable in the SQL for the 1st recordset also increases the difficulty. The first recordset via this variable actually opens one of about 13 tables depending on the value assigned to this table.

I will spend some time attempting to consolidate the two recordsets into one. I agree that this is the most efficient approach.

I am having a hard time getting past the thought that SQL recordsets should follow the concepts of Set Theory, a discipline learned many years ago. Set A includes the world (recordset A). Set B is a subset, including none to all of Set A. Set B - Set A is what I want to display in the listbox.

Thanks for your suggestions and thoughts.

Regards, Sorrells

 

[JimInKS]

I believe you can do what you want with Queries. You may base a query on the results of another query. This is really like creating the temp table but it lets Access do all the housekeeping.

Just use the Query designer to create Query1 that selects the data for your Recordset1. Then create Query2 based on Query1, and so on.

 

[scking]

I generally agree with most of the posted comments except that:

1) you CAN set a recordset from another and you can also build SQL from the values of another recordset as your code shows you trying to do. Recordsets are set to other recordsets frequently as when searching for a value;
Set MyRcds = Me.RecordsetClone
Returning a recordset from a function
Set MyRcds = GetDaoRecordset(strSQL)
or any number of other reasons.
2) You can also build SQL string that is used to set the recordset.

Dim strSQL As String
strSQL = "SELECT DISTINCTROW rst_All_Tasks." & rst_All_Tasks!Task & ", " _
"rst_All_Tasks." & rst_All_Tasks!og_ab & ", " _
"rst_All_Tasks." & rst_All_Tasks!Priority & ", " _
"rst_All_Tasks." & rst_All_Tasks!Freq_No & _
" FROM rst_All_Tasks"
Debug.Print strSQL
Set rstAvail_Tasks = dbs.OpenRecordset(strSQL)

NEVER FORGET to test the value of the SQL variable before determining the code is correct. A simple Debug.Print to the immediate window can provide you with that information. This allows the developer to EASILY see what is being used as the SQL for the recordset. It's way to easy to make a mistake.


-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------

 

[psemianonymous]

Yes, please do yourself a favor and try and implement this using several queries. There's even a wizard to help you create a "Find Unmatched Records Query", which you could then (hopefully) use to populate your listbox. Though it may be POSSIBLE to create an SQL statement based on the results of rst1, it's certainly not ideal. --
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access