Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Location
    Atlanta, GA
    Posts
    7

    Unanswered: Joining Recordsets in VB

    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
    ' [Inside_Agent_Payscale]



    On Error GoTo err_MkCrtsnAgentsProductsTest

    go_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;"

    Set rst1 = myQry1.OpenRecordset()

    Dim rst2 As DAO.Recordset
    Dim myQry2 As QueryDef

    Set myQry2 = myDb.CreateQueryDef("")

    myQry2.SQL = _
    "SELECT " & rst1!DateEntered & ", " & _
    rst1!AgentGrouping & ", " & rst1!LastName & ", " & _
    rst1!AgentID & ", " & rst1!AdministratorID & ", " & _
    rst1!ProductID & ", " & _
    rst1!ProductName & ", inside_agent_payscale_defaults.method , " & _
    "inside_agent_payscale_defaults.factor " & _
    "FROM (inside_agent_payscale_defaults INNER JOIN " & rst1.Name & " ON " & _
    "(inside_agent_payscale_defaults.Administrator ID = " & _
    rst1.Fields("AdministratorID") & ") AND " & _
    "(inside_agent_payscale_defaults.ProductID = " & _
    rst1!ProductID & ")) LEFT JOIN inside_agent_payscale ON " & _
    "( " & rst1!AgentGrouping & " = inside_agent_payscale.AgentGrouping) " & _
    "AND (" & rst1!ProductID & "= inside_agent_payscale.ProductID) " & _
    "WHERE (((inside_agent_payscale.AgentGrouping) Is Null) And " & _
    "((inside_agent_payscale.ProductID) Is Null)) " & _
    "ORDER BY " & rst1!LastName & ", " & _
    rst1!AdministratorID & ", " & rst1!ProductID & ";"

    Set rst2 = myQry2.OpenRecordset()

    Dim cnt As Integer

    cnt = rst2.RecordCount

    Debug.Print cnt


    myQry1.Close
    myQry2.Close
    Set rst1 = Nothing
    Set rst2 = Nothing


    Exit Sub

    err_MkCrtsnAgentsProductsTest:
    If Err.Number = 3012 Then
    Call DpCrtsnAgentsProducts
    GoTo go_MkCrtsnAgentsProductsTest
    End If

    Call errMsg("MkCrtsnAgentsProductsTest", "s")


    End Sub

    Any ideas, anyone? tks in advance for any insights, here!

    S

  2. #2
    Join Date
    Feb 2004
    Location
    CT,USA
    Posts
    250
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •