Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    California
    Posts
    2

    Unanswered: DAO recordset/querydef question

    Hello,
    I've search quite a ways back in this forum and could not find a answer for my question. So here goes.

    I have a small piece of vba code that will create a table from the SQL that is passed to it. The problem is I cannot seem to get the SQL to run without error 3219, Invalid Operation. Here is the code. curmon is passed from another sub and is defined as the Current Month Abbreviation. curmon = Feb
    Code:
    Function get_rep_quotas(curmon)
    
        Dim DB As Database
        Dim QDF As QueryDef
        Dim RS As Recordset
        Dim sql As String
        Dim num_holidays As Integer
        Dim num_recs As Integer
        Set DB = CurrentDb()
    
    
        sql = "SELECT ref_rep_mgrs.repid AS Rep, " & _
              "ref_rep_mgrs.RepName, " & _
              "Rep_furn_margin_quotas." & curmon & "Quota AS FurnMarginQ, " & _
              "Rep_furn_sales_quotas." & curmon & "Quota  AS FurnSalesQ, " & _
              "Rep_op_margin_quotas." & curmon & "Quota   AS OPMarginQ, " & _
              "Rep_op_sales_quotas." & curmon & "Quota    AS OPSalesQ " & _
              "INTO tmp_rep_info " & _
              "FROM (((ref_rep_mgrs " & _
              "INNER JOIN Rep_furn_margin_quotas ON ref_rep_mgrs.RepID = Rep_furn_margin_quotas.RepID) " & _
              "INNER JOIN Rep_furn_sales_quotas ON ref_rep_mgrs.RepID = Rep_furn_sales_quotas.RepID) " & _
              "INNER JOIN Rep_op_margin_quotas ON ref_rep_mgrs.RepID = Rep_op_margin_quotas.RepID) " & _
              "INNER JOIN Rep_op_sales_quotas ON ref_rep_mgrs.RepID = Rep_op_sales_quotas.RepID " & _
              "WHERE (((ref_rep_mgrs.RepID)=10317512)); "
    
     
        Set RS = DB.OpenRecordset(sql)

    I have also tried using a Querydef to do the same thin g. A simple SELECT statement works, but not with INSERT.

    Any help would be appreciated.

    Thanks,
    Brian
    Last edited by bhamm; 02-13-04 at 17:40.

  2. #2
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    Hi Brian,

    Try this
    Code:
    Function get_rep_quotas(curmon As String)
    
       CurrentDb.Execute _
          "INSERT INTO tmp_rep_info (" & _
             "Rep, " & _
             "RepName, " & _
             "FurnMarginQ, " & _
             "FurnSalesQ, " & _
             "OPMarginQ, " & _
             "OPSalesQ) " & _
          "SELECT " & _
             "ref_rep_mgrs.repid AS Rep, " & _
             "ref_rep_mgrs.RepName, " & _
             "Rep_furn_margin_quotas." & curmon & "Quota, " & _
             "Rep_furn_sales_quotas." & curmon & "Quota, " & _
             "Rep_op_margin_quotas." & curmon & "Quota, " & _
             "Rep_op_sales_quotas." & curmon & "Quota " & _
          "FROM (((ref_rep_mgrs " & _
             "INNER JOIN Rep_furn_margin_quotas ON ref_rep_mgrs.RepID = Rep_furn_margin_quotas.RepID) " & _
             "INNER JOIN Rep_furn_sales_quotas ON ref_rep_mgrs.RepID = Rep_furn_sales_quotas.RepID) " & _
             "INNER JOIN Rep_op_margin_quotas ON ref_rep_mgrs.RepID = Rep_op_margin_quotas.RepID) " & _
             "INNER JOIN Rep_op_sales_quotas ON ref_rep_mgrs.RepID = Rep_op_sales_quotas.RepID " & _
          "WHERE (((ref_rep_mgrs.RepID)=10317512));"
     
    End Function

  3. #3
    Join Date
    Feb 2004
    Location
    California
    Posts
    2
    Thank You. I have been trying all day trying to get querydef.execute or openrecordset to work.

    I never tried the db.execute...or putting the Insert before the SELECT.

    Thanks Again I almost gave up.

    Brian

Posting Permissions

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