Ok, this one's got me stumped. If i can get it to work, i've got several places where this would be very useful, but so far, no go.
What I'm trying to do is create a temporary recordset in memory, and then join that query to existing tables.
I can and have created the 1st tmp recordset correctly, and can manipulate said with no problems. Where my second recordset blows up is in the FROM statement, where I attempt to select fields from the temp. recordset. I think that the problem is that i'm not correctly referencing that recordset by name... typically, it's just the name of the query, but i get error messages when i use rst1.Name, so i'm kinda stumped here.
running Access2002, if that helps. here's the entire subroutine, in all it's glory:
Public Sub MkCrtsnAgtsPrdctsTest()
' This Subroutine creates a temporary query describing a cartesian join of all Inside agents
' with all products, selecting only the pertinent fields from each table.
' This should end up being a table that exists in memory only for a short period of time,
' during which, the next step would be to insert new agents into the table
On Error GoTo err_MkCrtsnAgentsProductsTest
Dim myDb As Database
Dim rst1 As DAO.Recordset
Dim myQry1 As QueryDef
Set myDb = CurrentDb
Set myQry1 = myDb.CreateQueryDef("")
myQry1.SQL = _
"SELECT Date() AS DateEntered, Agents.[UserDefinedListBox1] AS AgentGrouping, " & _
"Agents.AgentID, Agents.LastName, Agents.FirstAndMiddleName, " & _
"Products.AdministratorID, Products.ProductID, Products.ProductName, " & _
"Administrators.Name AS Carrier " & _
"FROM Agents, Products INNER JOIN Administrators ON " & _
"Products.AdministratorID = Administrators.AdministratorID " & _
"IN '\\192.168.10.28\gbswin32\data\gbsdata.mdb' " & _
"WHERE (((Agents.[UserDefinedListBox1]) <> ""House, Inside"" And " & _
"(Agents.[UserDefinedListBox1]) <> ""Fuchs, Sam"" And " & _
"(Agents.[UserDefinedListBox1]) <> ""Will, Brian"" And " & _
"(Agents.[UserDefinedListBox1]) <> ""Smith, Ayana"") And " & _
"((Agents.AgentID) <> 77 And (Agents.AgentID) <> 106) And " & _
"((IIf([AgencyID] = 9 Or [AgencyID] = 10 Or [AgencyID] = 15, " & _
"""Inside"", ""Outside"")) = ""Inside"") And " & _
"((Agents.IsAgency) = 0) And ((Products.ProductID) <> 13 And " & _
"(Products.ProductID) <> 30 And (Products.ProductID) <> 31 And " & _
"(Products.ProductID) <> 32)) " & _
"ORDER BY Agents.[UserDefinedListBox1], Products.ProductID;"
If your select fields match in order and type of field (text to text) the first and second query statements you could use a union query. I recommend you create two separate queries in design mode then create a third where you paste the second one in, add the word union between the two, and get rid of the first ";" from the first query. That would give you the right code and help you to match appropriate fields w/ the visual interface.