HolidayBuyer's Guide

Office & Productivity Software forum

General discussion

Microsoft Access: How do I create a recordset from another recordset?

by Ewan / April 1, 2004 11:40 AM PST

Here's what I've done:
Dim dbMydatabase As DAO.Database
Dim rsFirst As DAO.Recordset
Dim rsSecond As DAO.Recordset
Dim SQLQuery As String

Set dbMydatabase = OpenDatabase("C:\Pathlist\db1.mdb")
SQLQuery = "SELECT * FROM tblMain"
Set rsFirst = dbMydatabase.OpenRecordset(SQLQuery)

Now everything is peachy so far. But when I add the following to create the second recordset as a subset of the first one, I get an error. Note: Pet is a field in tblMain.

SQLQuery = "SELECT * FROM rsFirst WHERE Pet = 'dog'"
Set rsSecond = rsFirst.OpenRecordset(SQLQuery)

That last line is where I get run-time error 3421: Data type conversion error. Anyone know how to correct the code to prevent the error?

Note: There will be 8 recordsets, each created as a subset of the last. But once I know how to make one recordset from another, I can do the code for the rest.

Any help and/or insight will be greatly appreciated.

Discussion is locked
You are posting a reply to: Microsoft Access: How do I create a recordset from another recordset?
The posting of advertisements, profanity, or personal attacks is prohibited. Please refer to our CNET Forums policies for details. All submitted content is subject to our Terms of Use.
Track this discussion and email me when there are updates

If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.

You are reporting the following post: Microsoft Access: How do I create a recordset from another recordset?
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.
Collapse -
Not this way.

Ewan,

A recordset is a set of records you can read from (and update, and even add to) in Visual Basic (from a source: a table or a query). It's not something you can reference in the from-part of a SQL-statement. That's reserved for tables and queries. No recordsets, no variables, no databases, nothing except tables and queries.

In your example it should be:

SQLQuery = "SELECT * FROM TblMain WHERE Pet = 'dog'"
Set rsSecond = dbMydatabase.OpenRecordset(SQLQuery)

No need at all for rsFirst, unless your program needs to read all the records and the dog-records in parallel.

Hope this helps.


Kees

Collapse -
Re:Not this way.
by Ewan / April 1, 2004 10:45 PM PST
In reply to: Not this way.

Thanks! I didn't know you couldn't reference a recordset with an SQL query.

The reason I was attempting to do that is this: On my form are 8 option groups. The user will make a selection from each group, click the go button, and the program will return the information selected. For instance, the customer who purchased the most expensive dog from the Boston store during March.

The sheer number of pre-designed queries needed to cover every possible combination of selections is staggering. That's why I was creating the query dynamically. In the same vein, generating the SQL string to create the desired recordset in one step would also be unweildy, although doable.

I'm just trying to simplify my code by breaking it down into more manageable chunks.

Is there no other way than appending to the WHERE clause and then generating the recordset in one step? If not, then I suppose that's what I'll have to do.

Collapse -
Re:Re:Not this way.
by Ewan / April 2, 2004 4:58 AM PST
In reply to: Re:Not this way.

WOOHOO! A friend helped me work out the way to do this. Here's the solution:

rsFirst.Filter = "Pet = 'dog'"
Set rsSecond = rsFirst.OpenRecordset()
rsSecond.Filter = "Color = 'brown'"
Set rsThird = rsSecond.OpenRecordset()

rsThird now contains only records about brown dogs.

BTW here's a big thank-you to the person who posted earlier to help. While the answer wasn't exactly what I was looking for, something you said turned on the light for us and enabled us to find the answer in Microsoft Help. Teamwork works! Thanks again.

Collapse -
It's a nice solution, but what's wrong with ...
by Kees Bakker / April 2, 2004 5:18 PM PST
In reply to: Re:Re:Not this way.

SQLQuery = "SELECT * FROM TblMain WHERE Pet = 'dog' and color = 'brown'" [and so on for 8 choices]
Set rsFinal = dbMydatabase.OpenRecordset(SQLQuery)

The 'staged' selection with filter build upon filter only makes sense when you want to build up the result om the screen piece by piece by letting the user choose from the results shown the previous step.
But I still don't read that requirement in your post.

Anyway, thanks for the feedback. Glad I could help by steering a little bit in the right direction. Although you went a quite unintended (but obviously correct) way after that.

Good luck.


Kees

Popular Forums
icon
Computer Newbies 10,686 discussions
icon
Computer Help 54,365 discussions
icon
Laptops 21,181 discussions
icon
Networking & Wireless 16,313 discussions
icon
Phones 17,137 discussions
icon
Security 31,287 discussions
icon
TVs & Home Theaters 22,101 discussions
icon
Windows 7 8,164 discussions
icon
Windows 10 2,657 discussions

HOLIDAY GIFT GUIDE 2017

Cameras that make great holiday gifts

Let them start the new year with a step up in photo and video quality from a phone.