Open Rowsource as a Select Query

[MykelDL]

Thank you in advance for your help.



I have a working form where I use comboboxes to filter an unbound listbox to winnow my record selection options. Everything works as expected. Now I want to create an "export" of the listbox result, by opening up a select query so that I can copy and paste it into Outlook.

As an aside, the reason I want/need to do it this way is because the output will have SharePoint hyperlinks to documents and folders, which Outlook (and only Outlook, it seems) conveniently converts the into legible titles (so you know what it is) that can be clicked on directly and it will open up the document or folder in SharePoint. If I could get an Excel export or PDF of an Access report to do this, I probably would.

Given the programming done to create and filter the rowsource for the listbox, I was hoping to use the rowsource and just open a query, like this:

Private Sub cmdExport_Click()

Dim strSQL as String

strSQL = me.lstMaterials.Rowsource

DoCmd.OpenQuery strSQL, acViewNormal

End Sub

I get Run-time Error 7874, "...can't find the Object 'Select....'" which I think tells me I need an Object (a query, in this case). I know I could recreate the rowsource as a query object using the parameters from the listboxes, but if there's a simpler way....

[Micron]

Output the rowsource in the immediate window. If it looks ok (which it probably is not) then copy/paste into sql view of a new query and try to run it. My guess is that even if that works it won't produce the results you expect. If you're filtering the listbox that would mean that the rowsource sql is whatever it started out with, not what you've filtered the listbox to. If none of that is true, post the value of strSQL.

[MykelDL]

Output the rowsource in the immediate window. If it looks ok (which it probably is not) then copy/paste into sql view of a new query and try to run it. My guess is that even if that works it won't produce the results you expect. If you're filtering the listbox that would mean that the rowsource sql is whatever it started out with, not what you've filtered the listbox to. If none of that is true, post the value of strSQL.

I already did that, and it all works as expected. I actually had to do that work to respond to a question someone had. Now that I know this is the type of information people will want, I was trying to make it easier to do by creating the Command to "generate" the query. My assumption is that the problem is that a string isn't a Query Object, not that it's a faulty SQL statement. So my question is how do I either create a query object on the fly, or is there a different command to open an SQL in a query/table view?

[Micron]

It may only be an issue of quote marks, which I why I asked you to post the sql.

[MykelDL]

Well shame on me for skipping a step and not doing a deeper Google search of Run-time error 7874, which confirmed DoCmd.OpenQuery must use the name of a pre-saved/compiled query object and cannot use an SQL string. My answer comes from https://www.access-programmers.co.uk...object.268090/

I got what I want by:
1. Creating a temporary select query object "TempQRY". I just created it with a single field from any table.
2. Based on the what was in the answer, I updated my code to:

Private Sub cmd Export_Click()
Dim strSQL as String 'probably moot
strSQL = me.lstMaterials.RowSource 'probably moot
CurrentDb.QueryDefs("TempQRY").SQL = strSQL 'could probably just use me.lstMaterials.Rowsource
DoCmd.OpenQuery "TempQRY", acViewNormal
End Sub

Problem solved. Experience gained.

[MykelDL]

It may only be an issue of quote marks, which I why I asked you to post the sql.

Thanks for diving in and trying to assist.

[Micron]

Kudos to you, not only for solving it but for posting the solution! I had a notion to research the method because I haven't locked into my brain the differences between methods like OpenQuery, RunSql and db.Execute but the kitchen was demanding my attention (alas, I am the chief cook and bottle washer, even for the family dog ).

FYI, to create a truly temporary queryDef (query definition) you don't give it a name, so something like
CurrentDb.CreateQueryDef("","SELECT * FROM...")
which might well fit your scenario where the rowsource is dynamic but you don't want to save what makes up that rowsource sql.

[MykelDL]

Kudos to you, not only for solving it but for posting the solution! I had a notion to research the method because I haven't locked into my brain the differences between methods like OpenQuery, RunSql and db.Execute but the kitchen was demanding my attention (alas, I am the chief cook and bottle washer, even for the family dog ).

FYI, to create a truly temporary queryDef (query definition) you don't give it a name, so something like
CurrentDb.CreateQueryDef("","SELECT * FROM...")
which might well fit your scenario where the rowsource is dynamic but you don't want to save what makes up that rowsource sql.

Among my multitude of Access aficionado weaknesses is my lackluster understanding of SQL statements. I've gotten to understand some basics by using the query builder and looking at the SQL result, but I've never devoted time to learning SQL properly. In the end, I needed different fields for my output query than were in the filtered rowsource, so I just made a TEMPQry and used an INNER JOIN on the IDs to get a quick-and-easy output of what I needed without starting over. Another approach might have been to just copy the WHERE statement used to filter the Rowsource and apply it to a SQL statement that had the fields I needed, but the data feeding the rowsource was already complicated, so I just applied what I felt was the easiest to get what I needed. The only way I knew to do this, was to name the INNER join query as a fixed item--I don't know how I would have done this if I didn't give it a name.

[Micron]

I don't know how I would have done this if I didn't give it a name.

Probably just as I showed you, where the name is a zls (zero length string - "" ).