Open a rs within a rs (1 Viewer)

[painterz]

Hi MJ,

Using one table to store one record was my attempt at storing the selection from the listbox. The table would get rewritten every time the code executes.

Thank you for the suggestion. I have googled listbox but have yet to find someone using vba to get the listbox's selection. Your piece of code is what I was looking for. Also, I appreciate the explanation of record source and control source. I thought that's how it might work but couldn't successfully test it. So it sounds like I could execute a make table statement in the control source.

Thanks

 

[painterz]

Hi Mark,

Thanks for the effort! In the search form, I typed in 'George'. More records were returned than should have been. The hierarchy you used is correct so I'm trying to figure out your sql statement. Sorry, I write like an end user not a programmer. What does 'LIKE p0' mean (where p=tblPeople)? I don't understand the syntax of p0 -- first column in the table??? It's referenced several times in the WHERE clause.

Thanks

 

[mjdemaris]

Very interesting, Mark. I would like to know what p0 references as well. I've never seen that, and I like the use of aliases, as well as the SQL Const.

Mike

 

[MarkK]

"p0" is the parameter in the query. If there were more there would be a p1 and a p2 and so on, but we are comparing various field values, and concatenated field values, to the search text entered by the user, and we re-do this for every keystroke. So at in this routine...

Private Sub DoSearch(text As String)
    With Me.qdf
        .Parameters(0) = "*" & text & "*"  [COLOR="Green"] 'set query parameter[/COLOR]
        Set Me.lstResult.Recordset = .OpenRecordset()   [COLOR="Green"]'open a new recordset, and assign to list[/COLOR]
    End With
End Sub

...we might also write the parameter value assignment as...

        .Parameters("p0") = "*" & text & "*"   'set query parameter

...using the name we used in the SQL, rather than the ordinal position in the Parameters collection. But it's a place holder in the SQL which we replace with a real value at runtime, and then use that to open the recordset which we assign to the list.

 

[MarkK]

Also, the names "p0" and "p1" are not sacred, they are just names I like that are short. But you could use [prm0] and [prm1] or [pSearchText] or whatever you want.

 

[painterz]

Sorry, I didn't realize you posted a response to the "p0" question. I'll get started on this one again and follow up.