Open a rs within a rs (1 Viewer)

[painterz]

Hi All,

I thought opening a recordset (subset) within a recordset would be straightforward--evidently not.

Here's what I have:

Dim db As Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim sqlstr As String
Dim sqlstr1 As String
Dim sqlstrall As String

.....
sqlstr = "SELECT tblPeople.FamilyID, tblPeople.LastName, tblPeople.FirstName, " & _
        "tblPeople.EmailAddress, tblPhone.PhoneNumber " & _
        "FROM (tblFamily INNER JOIN tblPeople ON tblFamily.FamilyID = tblPeople.FamilyID) LEFT JOIN " & _
        "tblPhone ON tblPeople.PeopleID = tblPhone.PeopleID "

        Set db = CurrentDb
        Set rs = db.OpenRecordset(sqlstr) 'this should limit all records to the search criteria

....

        Select Case controlname.Name
            Case subformname!FirstName.ControlSource
                sqlstr1 = sqlstr & "WHERE (tblPeople.FirstName)= '" & inputstring & "' "
            Case subformname!LastName.ControlSource
                sqlstr1 = sqlstr & "WHERE (tblPeople.LastName) like '*" & inputstring & "' "
            Case subformname!EmailAddress.ControlSource
                sqlstr1 = sqlstr & "WHERE (tblPeople.EmailAddress)='" & inputstring & "' "
            Case subformname![Phone].Form!PhoneNumber.ControlSource  'this is a sub-subform
                sqlstr1 = sqlstr & "WHERE (tblPhone.PhoneNumber)='" & inputstring & "' "
        End Select

 
    'ck value of txtbookmark
        If intfambookmark = 0 Then
            sqlstrall = sqlstr1
        Else
            sqlstrall = sqlstr1 & " AND (tblPeople.FamilyID) > " & intfambookmark & ";"
        End If

    'set the next recordset within the first recordset
        If Not rs.EOF Then

           Set rs1 = rs.OpenRecordset(sqlstrall)
....

I keep getting a data type conversion error. I'm opening a new recordset within an existing recordset based on a query. What's causing the data type conversion error?

Thanks

 

[Ranman256]

Why would you?
Except to read Attachment field.

 

[MarkK]

If you post code that generates an error, what is the first thing a potential troubleshooter needs to know?

 

[painterz]

The last line of the code is where the error is being generated.

Set rs1 = rs.OpenRecordset(sqlstrall)

This didn't happen when I used "Set rs1 = db.openrecordset(sqlstrall)" but I didn't get the results I was looking for either.

 

[painterz]

Actually

Set rs1 = db.OpenRecordset(sqlstrall)

might work if I could get my bookmarks working

subformname.bookmark = rs1.bookmark

 

[MarkK]

The DAO.Recordset.OpenRecordset method does not take a SQL statement as a parameter.

Why do you need two recordsets in this situation? Why not construct the SQL you need, then open the final recordset using that SQL?

 

[painterz]

In this instance I'm looking for people with the same name (LastName = 'Jones') in my db through my subform. The first recordset never moves me to the next person, it always starts over and shows me the first person. My thinking is have a recordset within a recordset and the starting value of the second recordset is stored on the main form from the last search. I can see it moving through the people like it supposed to do but I can't get my bookmarks to work so I can show the correct record on my form.

 

[painterz]

Sorry,

Set rs1 = db.OpenRecordset(sqlstrall)

works

 

[MarkK]

So you're good? You got it figured out?

One cool trick in a case like this is you can assign your newly opened recordset directly to the Recordset property of a listbox or subform. That's a fast and simple way to show the results of a search. So say you come up with SQL you need based on user input, then you can do . . .

   Set Me.lstSearchResult.Recordset = CurrentDb.OpenRecordset(SQL)

... and *poof* the list shows the result set.

Cheers,

 

[painterz]

That is very cool! Hopefully, I can use it in another project. Do you have any tricks or caveats regarding bookmarks?

 

[painterz]

I'm still stuck on bookmarks. Not sure why they stopped working.

 

[painterz]

This may be a clue as to why I'm stuck.

When I look up someone, I write their familyID to an unbound textbox. I need the bookmark to align the familyID in the textbox to the actual form.

This writes the value to the unbound textbox:

famsubformname!txtbookmark.Value = rs1(1).Value

(if I look at the name associated with rs1(1), I am on the right person)

And this is where my code fails:

formname.bookmark = rs1.bookmark

I don't think the two are even communicating to each other. Do you know how to move to the next record that is shown in my unbound textbox?

 

[MarkK]

How are you using bookmarks?

The only time I use bookmarks is for navigation in a recordset that is supported by a different object, say a form, and I want to find a record in that form. In that case I would open a clone of the recordset, search the clone, if the record is found in the clone, then set the recordset's bookmark equal to the clone's bookmark.

Public Sub GoToID(SomeID as long)
   With Me.Recordsetclone
      .FindFirst "SomeID = " & SomeID
      If Not .NoMatch Then Me.Bookmark = .Bookmark
   End With
End Sub

But if I'm opening a recordset directly in code, I would never use bookmarks. I would open exactly the recordset I need, rather open one overly broad, and then search within it, in which case you wouldn't use bookmarks.

But again, this depends on what you are doing,

 

[painterz]

I'm now testing with clones. My problem is if there are multiple names to be searched, the user has to say 'next', and the code starts again. That's why I'm writing to an unbound text box as a place holder to start the next search. My code was similar to what you posted and I couldn't go to the next record because the code starts over again and always landed on the same record.

 

[MarkK]

But this is where I don't see why, if you are returning search results, that you aren't using a listbox or subform or something capable of showing the list of records that match the search parameters.

 

[painterz]

Picture this...one form with two subforms and one of those subforms has two subsubforms. On this "form" I can see all of the info I need. However, when I look up a family, say the Moore's, I only see the first family in the db. I have to go into the tables behind the forms and find the address associated with the Moore's I'm looking for then open the form, do a search on the address (located on the main form) to find the correct Moore family.

I would like to search for 'Moore' (located on a subform), if that's not the right family then search again and again until I run out of db. I'm not able to that right now. It's the equivalent of a Search w FindNext function but I can't get that to work on a subform, so I'm making my own. If I could get the Search function to work on a subform, then this would all be moot.

Does that help?

 

[MarkK]

I would like to search for 'Moore' (located on a subform), if that's not the right family then search again and again until I run out of db.

Yes, that is what it seems like you are doing, and what I'm trying to say--and what I would do instead--is return all the results at once in a list or subform or popup form (with a list). Why return just one result and force the user to click again and again to see more data? Show it all. It's easier to program, and it's easier to use, and show other fields too, so the user can decide at a glance which 'Moore' is the correct one.

Then you don't need to use bookmarks, and you don't have to incrementally step thru your recordset.

Thats what I would do anyway,
Cheers,

 

[painterz]

Yay!!! I like that! Ok, I'll give that a try as a pop up.

Thanks!!!

 

[painterz]

Do you have a suggestion for resetting the recordset? If I look up 'Moore', then look up 'Asten' I get record not found since I've already passed it to get to 'Moore'. Is there some sort of recordset reset I can use at the end of the search function?

 

[jdraw]

Can I ask you to step back and tell us in simple English terms WHAT you are trying to accomplish?
You are telling us how you have done something that isn't working. We need to understand WHAT before we can comment on HOW. Markk has given you suggestions, and said he wouldn't use bookmarks in your situation. It seems you are intent on pursuing your current approach.
So, for clarity, WHAT is the situation and what are you trying to achieve.