Open a rs within a rs (1 Viewer)

[MarkK]

Do you have a suggestion for resetting the recordset?

Just close it and open another one. There is nothing sacred about a recordset. Open exactly the one you need, then close it, and open exactly the next one you need. Recordsets and like nails, very useful, but if you drop a few you don't even bother to pick them up, that's how cheap they are. Don't recycle them. Just open a new one.

It's fine to construct new SQL and open a new recordset for every keystroke in a search form.

Hope this helps,

 

[arnelgp]

you can filter the previous recordset (rs) and return the resulting records to new recordset (rs1).

you need to modify your sqlstr1 first, ie:

Case subformname!FirstName.ControlSource
sqlstr1 = "(tblPeople.FirstName)= '" & inputstring & "' "
...
...

    'set the next recordset within the first recordset
        If Not rs.EOF Then
       rs.filter = sqlstrall    
           Set rs1 = rs.OpenRecordset()

 

[painterz]

Hello All,

To JDraw, I'm still working on the last project you helped with. I have a neighborhood address form and on it a family subform and a people subform, which contains a phone subsubform. I would like to look up double click the field for people by first or last name, email address and phone number to return a record on the form. Then make my changes to the data and continue to my next one.

My search has worked for the first person encountered. However, I usually need to go past that record for the next one. I'm not able to do that with my current design. It has been a bug I've been dealing with for a long time and decided it's time to fix it. I like MarkK elegant solution but don't know if my skills are strong enough to implement it. (It's been 20 years since I was an Access developer.) Arnelgp seems to have something for me but I need to look at code again to see how to apply it.

My end goal is to find the person I'm looking for without having to go into the tables to make the changes and then have my underlying data reset back to the beginning so I stop getting "Record not found" because the pointer is past where I'm looking.

I was just thinking I could pass my rs1 bookmark to rs (since it embodies the whole database w no filters) and then pass from rs to the form. At this point I think a glass of wine is sounding really good because I am confusing myself.

Hope this provides clarity.

 

[jdraw]

However, I usually need to go past that record for the next one. I'm not able to do that with my current design. It has been a bug I've been dealing with for a long time and decided it's time to fix it.

Absolutely--if the design doesn't work --which really means does not support my business- then it's time to get the design correct, and tested--then program that design change(s).

I'm not sure I understand searching for a record and then having to go past it to get another one...

My guess is that a clear example - showing your raw data, the data you're searching for; the result of the search; and the conditions that make you go beyond that to find the next one.... would help readers.
I'm sure arnelgp has a solution, but test it and make sure it fits and solves the whole issue -not just a symptom.

I think Markk understands your issue and can offer some options.

My advice is to be prepared to rework and test the design. Don't keep coding in hopes of "it getting solved". Design, test and build----pretty straight forward approach.

Good luck.

 

[painterz]

Hi All,

Sorry for the long delay--I get a chance to work on this every few months. I liked MarkK's idea about opening the results of my search in a listbox and started working on it. My function opens a pop up form containing an unbound listbox then I realized how do I get the answer from the listbox and go to the record in the db? I thought if I called the listbox through another function (intPeepID = openresultsform()) I could capture the value of the listbox's bound column (PeopleID) but haven't found much info on how to get info from a listbox using vba.

Here's MarkK's original response (post #9)

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 . . .
Code:
Set Me.lstSearchResult.Recordset = CurrentDb.OpenRecordset(SQL)
... and *poof* the list shows the result set.

So now, I have the search results in the listbox. How do I pass the result back to the function that was running? Or do I add code to the listbox? It seems like once I open the form with the listbox I'm beyond the scope of the function. I'd like to pause the function, open the results form with the listbox, and then pass the PeopleID of the selection back to my function so it will pull up the right record in my db. I'm totally clueless as to where to even start. Here's my code:

Function recordSearch(controlname As Control, PeopleID As Integer)

Dim db As Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim sqlstr As String
Dim sqlstrnames As String
Dim inputstring As String
Dim strsearch As String
Dim ctlcontrol As Control
Dim strcontrol As String
Dim strreccount As Integer
Dim strcontrolname As String
Dim frm As Form


'DoCmd.Hourglass True
 Application.SetOption "Default Find/Replace Behavior", 2


'On Error GoTo ErrRecordSearch

sqlstr = "SELECT [StreetNo] & ' ' & [StreetName] AS Address, tblPeople.PeopleID, tblPeople.LastName, " & _
    "tblPeople.FirstName, tblPeople.EmailAddress, tblPhone.PhoneNumber, tblRelationships.Relationship " & _
    "FROM tblAddress INNER JOIN (tblRelationships RIGHT JOIN ((tblFamily INNER JOIN " & _
    "tblPeople ON tblFamily.FamilyID = tblPeople.FamilyID) LEFT JOIN tblPhone ON tblPeople.PeopleID = " & _
    "tblPhone.PeopleID) ON tblRelationships.PKRelationship = tblPeople.PKRelationship) ON " & _
    "tblAddress.AddressID = tblFamily.AddressID "

Set ctlcontrol = controlname
strcontrol = ctlcontrol.Name
inputstring = InputBox("Find:", "Search")

If inputstring <> "" Then
Set frm = Forms("Neighborhood Input Form")!People.Form

        Select Case strcontrol
            Case frm!FirstName.ControlSource
                sqlstr = sqlstr & "WHERE (((tblPeople.FirstName)= '" & inputstring & "')) "
            Case frm!LastName.ControlSource
                sqlstr = sqlstr & "WHERE (((tblPeople.LastName) like '*" & inputstring & "')) "
            Case frm!EmailAddress.ControlSource
                sqlstr = sqlstr & "WHERE (((tblPeople.EmailAddress)='" & inputstring & "')) "
            Case frm.Form![Phone]!PhoneNumber.ControlSource  'this is on a sub-subform
                sqlstr = sqlstr & "WHERE (((tblPhone.PhoneNumber)='" & inputstring & "')) "
        End Select
sqlstr = sqlstr & "ORDER BY tblFamily.AddressID, tblPeople.PKRelationship "

Debug.Print sqlstr

    Set db = CurrentDb
    Set rs = db.OpenRecordset(sqlstr)
    rs.MoveLast
    strreccount = rs.RecordCount
    rs.MoveFirst
        
Debug.Print strreccount

If Not rs.BOF And Not rs.EOF Then
    Set rs1 = Forms![Neighborhood Input Form].RecordsetClone

    If strreccount = 0 Then
        MsgBox "No records found."
    ElseIf strreccount = 1 Then
        Forms![Neighborhood Input Form].bookmark = rs1.bookmark
    Else
        'open frmResults
        DoCmd.OpenForm "frmResults"
        'set recordsource to sqlstr
        Set Forms!frmResults!lboResults.Recordset = CurrentDb.OpenRecordset(sqlstr)
        
        
        
    End If
    
    rs1.Close
    rs.Close
    Set rs1 = Nothing
    Set rs = Nothing
    Set frm = Nothing
    End If
End If
Exit_RecordSearch:
    Set ctlcontrol = Nothing
    DoCmd.Hourglass False
    Exit Function

ErrRecordSearch:
    MsgBox Err.Description
    Resume Exit_RecordSearch

End Function

Thanks!

 

[MarkK]

So now, I have the search results in the listbox. How do I pass the result back to the function that was running?

Well, you put the search results in a listbox, you actually have them in a recordset.

Which function was running? Do you mean the search function?

What do you use the listbox for? Does further selection occur in the listbox, and if not, what is the purpose of the listbox?

 

[painterz]

Hi MarkK,

        'open frmResults
        DoCmd.OpenForm "frmResults"
        'set recordsource to sqlstr
        Set Forms!frmResults!lboResults.Recordset = CurrentDb.OpenRecordset(sqlstr)

You're right! I thought I was setting the control source. That wasn't intentional, what's the difference between the control source and recordset properties of a list box? Aren't both restricted to the records given, in this case by sqlstr?

The recordsearch() function was running. I'm still trying to figure out if opening the listbox places it out of scope of my calling function.

The listbox shows when more than one record is returned by the sqlstr so the user can decide which record to select. So the part where I'm getting confused is the person selects which record to view--then what? There's where my code is now.

 

[painterz]

I'm getting an error "Object doesn't support this property or method" when I changed my code from

Set Forms!frmResults!lboResults.Recordset = CurrentDb.OpenRecordset(sqlstr)

to

Set Forms!frmResults!lboResults.ControlSource = CurrentDb.OpenRecordset(sqlstr).

Your original example used recordset and it worked great. Why can't I keep it as recordset?

 

[mjdemaris]

Mark is right. But again, we don't know exactly what you are attempting to do. Information on the process goes a long way.

The list box is very useful in something like this, as is a datasheet form used as a subform.

So, the more information you can provide on what you WANT to do, the more help you can get with HOW to do it.

 

[MarkK]

I don't understand the problem. Does the search tool work? If not, how does it fail? Or is this not a problem with the search tool anymore?

 

[mjdemaris]

why don't you post a sample database and describe what you want to do, from start to finish?

 

[painterz]

Hi All,

I probably should have started this as a new thread but since MarkK had given me the solution and I had follow up questions I stayed with this thread not knowing if he would read my new thread.

As a quick overview: I am trying to search within a subform for the next matching record if the current record isn't the one I want. MarkK suggested I put all of the matching records in a listbox and then choose which one I wanted rather than repeating the search again and again until I found the right record.

So what I've managed to successfully do is write the list to an unbound listbox on a pop up form. I can see all of the records with the matching criteria but I don't know what to do next. I want to select the record and have the form show the record. I think I need to pass the PeopleID from the pop up form to the calling function but don't think this is possible.

I've attached a test db for the forum. The Neighborhood Input Form will open once the content is enabled. The pop up form is called frmResults. The calling function is recordSearch() located in the Utilities module.

If you'll double click on Last Name in the Neighborhood Input Form and type in 'George', you'll see two different families with the last name = 'George'. I'd like to chose 'Boy George', PeopleID=2490, and have the form show that record.

So can I pass the selected PeopleID back to the calling function?

Thanks!

 

[mjdemaris]

Why don't you try using something like this? See pic.

Using combo boxes to filter your datasheet, then select your record and get the details below.

Otherwise, you'd need to add a click event to the listbox, get the PeopleID from that column, and set the frmFamily record, I think, to that of the PeopleID.

Or, if that form is tied to the address ID in the Neighborhood Input form, then you'd probably have to get the address ID from the listbox and set the Neighborhood Input form record to that address ID, and make sure that the subform updates. Though that may not give you "Boy George" exactly.

 

[painterz]

Hi MJ,

Thanks for looking at my db. So would the combo boxes always show or would they be on a pop up form? As you can see from the size of Neighborhood Input Form, there isn't much room for additional forms or footers.

I think the unbound pop up form with an unbound listbox is what's causing me the (mental) problem, plus I'm really rusty with Access. I'm currently working with creating a temporary table, so the pop up form, frmResults, is bound to tblResults with the listbox being bound to the PeopleID field. tblResults is based on the fields in my sqlstr before the parameters of WHERE and ORDER BY. I made a query based on tblResults, qryResults, that will take the arguments from the finished sqlstr, in searchRecords(), to populate the table. Not sure if any of this will work but that's what I'm testing right now.

My intention is that only the selected record is stored in the table, but I think all records meeting my search criteria will be there.

My current problem is

sqlstr = db.querydefs("qryResults").SQL

passes along the final ";" so when I concatenate the WHERE and ORDER clauses I'm getting an error because there are characters at the end of my SQL statement. I'm trying to figure out LEFT and LEN to cut off the ";", then see if my current idea has any merit.

Thanks

 

[The_Doc_Man]

Let's say you have two recordsets, rsA and rsB, and you open both to the same original data stream (be it table or the same query). If you then look up up something from rsA and want to point rsB to it, using rsA's bookmark to directly set rsB doesn't work. The correct method is to find what you want by doing a .FindFirst from rsB based on criteria using data from the (currently selected) fields of rsA.

I could imagine using recordset clones with recordsets if I were following a linked list through a self-referential table, perhaps. Otherwise, the .RecordSetClone's greatest use is to move the selected record on a bound form by searching through the form's .RecordSource's .RecordSetClone to find the desired record.

 

[painterz]

Yep, you're seeing my problems before I've even encountered them. I'm still trying to get my sqlstr to read correctly. Okay, so there needn't be an rsA and rsB. PeopleID is a unique identifier so once a record is selected on my listbox, IF I can capture the PeopleID, then I can do a findfirst on my rs. I remember doing something like this in a temp table and it was so easy--that was 20 years ago. Now, I only remember that I did it--not how I did it. There doesn't seem to be an emoji for 'ARRRRRRG!'.

So it sounds like when searchRecords() is called and more than one record is returned, meaning my pop up form shows, then searchRecords() has done it's job and now I code the pop up form to get my user on the correct record.

 

[MarkK]

My 2c, if you want to work with People, is make a top level form that draws from that table. Your main form here is Address, and your People are on a subform. In this case, all "search and navigate" operations will require you to...
1) select a person
2) figure out that person's address
3) navigate to that address in the main form
4) navigate to that person in the subform.
...which is much more than twice the work. It adds complexity to an operation that should be simple. And looking at your system, it's the people that are you main objective anyway, their email addresses, phone numbers, opt-ins for newsletters, and so on! Make your life easier and make a "People" main form.

Hope this helps,

 

[painterz]

Hi Mark,

I'm not trying to be stubborn about setting the People as the top of the hierarchy. This would be a total teardown of everything I've built (20 years ago) and I no longer have the skillset to do this. I've actually forgotten how listbox works and am now stumbling around trying to get it to work.

If the code sets the subform (frmPeople) to the correct record, the other forms don't follow through, the datasheet (frmFamily) and main form (Neighborhood Input Form)? If that's the case, I do have the AddressID in the recordset and I can include FamilyID to get everything to the correct person's record.

I noticed there is a control source and record source for a listbox. I tried figuring out what each does with no success. What does each do? The control source seems to show the info. If the record source is where the selected info writes to then maybe I can write a make table sql statement and it would be a temp table storing AddressID, FamilyID, and PeopleID then deleted at the end of the code.

Thanks

 

[mjdemaris]

Suggestion: Google listbox members. The MSDN site, along with many others, have a ton of useful information.

The RecordSource is from where the control pulls its data. The ControlSource is what it is bound to - what it reads from and writes to.

You can set a variable = listbox.column(index number of PeopleID) and use that to get the PeopleID.

Why are you using a temp table to store one record?

 

[MarkK]

Here's an approach you can take for running the search. Check out frmResults, which opens the Address form if it needs to.

But I couldn't figure out your data. The search query I wrote, connecting Address->Family->Person, seems to return people at addresses, but those same people do not appear in the People subform at those same addresses. Maybe you can figure that out. I'm outta time.

Hope this helps,
Mark