Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2005
    Posts
    240

    Unanswered: Error 3219 Invalid Operation

    Hi Folks,

    I require your expertise and vast knowledge to sort out my problem.

    I keep getting the run-time error 3219: Invalid Operation when trying to open a recordset based on QueryDefs.

    This is my code (the red text is where the error occurs):

    Code:
    Public Function MailMergeCriteria()
    Set QDef = CurrentDb.QueryDefs("qryMailMerge")
    
    QDef.Parameters("[Forms]![frmMailMerge]![txtTown]").Value = [Forms]![frmMailMerge]![txtTown]
    QDef.Parameters("[Forms]![frmMailMerge]![txtCounty]").Value = [Forms]![frmMailMerge]![txtCounty]
    QDef.Parameters("[Forms]![frmMailMerge]![txtPCode]").Value = [Forms]![frmMailMerge]![txtPCode]
    QDef.Parameters("[Forms]![frmMailMerge]![cmboBArea]").Value = [Forms]![frmMailMerge]![cmboBArea]
    QDef.Parameters("[Forms]![frmMailMerge]![cmboLetterName]").Value = [Forms]![frmMailMerge]![cmboLetterName]
    
    Set rs = QDef.OpenRecordset
    
    If rs.EOF Then
        MsgBox "No data found. Please check the criteria.", vbOKOnly, "RECEPE"
    Else
        'Turn off warning messages
            DoCmd.SetWarnings False
        'Run Mail Merge query
            DoCmd.OpenQuery "qryMailMerge", , acAdd
    
    'If cmboLetterName.Value = "Train2GainOpenDayLetter" Then
        'DoCmd.OpenQuery "qryOpenDay", , acAdd
    'End If
    
        Set rs1 = CurrentDb().OpenRecordset("tblMailMerge", dbOpenDynaset)
    
        CurrentDb.Execute ("UPDATE tblMailMerge SET LetterBody ='" & Me!txtLetter & "'")
    
        'Turn warnings back on
        DoCmd.SetWarnings True
        
        MailMergeIt (CurrentProject.path & "\Letters\T2G New Year Letter.doc")
    End If
    End Function
    Before I forget the rs, rs1 and QDef variables are assigned in general declarations, incase anyone was wondering.

    And the query it is opening is as follows:

    Code:
    PARAMETERS [Forms]![frmMailMerge]![txtTown] Text ( 255 ), [Forms]![frmMailMerge]![txtCounty] Text ( 255 ), [Forms]![frmMailMerge]![txtPCode] Text ( 255 ), [Forms]![frmMailMerge]![cmboBArea] Text ( 255 ), [Forms]![frmMailMerge]![cmboLetterName] Text ( 255 );
    SELECT dbo_tblEmployer.*, dbo_tblEmployerContacts.Name, dbo_tblEmployerContacts.EmpPosition, qryMailMergeLink.LetterBody INTO tblMailMerge
    FROM (((dbo_tblEmployer LEFT JOIN dbo_tblEmployerContacts ON dbo_tblEmployer.EmpID = dbo_tblEmployerContacts.EmpID) LEFT JOIN dbo_tblBAreaLink ON dbo_tblEmployer.EmpID = dbo_tblBAreaLink.EmpID) LEFT JOIN dbo_tblBArea ON dbo_tblBAreaLink.BAreaID = dbo_tblBArea.BAreaID) INNER JOIN qryMailMergeLink ON dbo_tblEmployer.EmpID = qryMailMergeLink.EmpID
    WHERE (((dbo_tblEmployer.Train2Gain) In (True,False)) AND (([Addr4] & "") Like Nz("*" & [Forms]![frmMailMerge]![txtTown] & "*","*")) AND (([Addr5] & "") Like Nz("*" & [Forms]![frmMailMerge]![txtCounty] & "*","*")) AND (([PostCode] & "") Like Nz([Forms]![frmMailMerge]![txtPCode] & "*","*")) AND (([BArea] & "") Like Nz([Forms]![frmMailMerge]![cmboBArea] & "*","*")) AND ((qryMailMergeLink.LetterName)=[Forms]![frmMailMerge]![cmboLetterName]));
    Thanks

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Set rs = QDef.OpenRecordset

    should be

    Code:
    Set rs = CurrentDb.OpenRecordset(QDef, dbOpenDynaset)
    HTH,
    Sam

  3. #3
    Join Date
    Sep 2005
    Posts
    240
    When I do that it comes up with:

    Compile Error:
    Type Mismatch

    Code:
    Set rs = CurrentDb.OpenRecordset(QDef, dbOpenDynaset)

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    In your Dim statement, how do you define rs? Also, are you using ADO or DAO?

    Sam

  5. #5
    Join Date
    Sep 2005
    Posts
    240
    Quote Originally Posted by Sam Landy
    In your Dim statement, how do you define rs? Also, are you using ADO or DAO?
    I'm using DAO, I've moved them into the function now.

    Code:
    Public Function MailMergeCriteria()
    Dim rs As DAO.Recordset
    Dim rs1 As DAO.Recordset
    Dim QDef As QueryDef
    Set QDef = CurrentDb.QueryDefs("qryMailMerge")
    
    QDef.Parameters("[Forms]![frmMailMerge]![txtTown]").Value = [Forms]![frmMailMerge]![txtTown]
    QDef.Parameters("[Forms]![frmMailMerge]![txtCounty]").Value = [Forms]![frmMailMerge]![txtCounty]
    QDef.Parameters("[Forms]![frmMailMerge]![txtPCode]").Value = [Forms]![frmMailMerge]![txtPCode]
    QDef.Parameters("[Forms]![frmMailMerge]![cmboBArea]").Value = [Forms]![frmMailMerge]![cmboBArea]
    QDef.Parameters("[Forms]![frmMailMerge]![cmboLetterName]").Value = [Forms]![frmMailMerge]![cmboLetterName]
    
    Set rs = CurrentDb.OpenRecordset(QDef, dbOpenDynaset)
    
    If rs.EOF Then
        MsgBox "No data found. Please check the criteria.", vbOKOnly, "RECEPE"
    Else
        'Turn off warning messages
            DoCmd.SetWarnings False
        'Run Mail Merge query
            DoCmd.OpenQuery "qryMailMerge", , acAdd
    
    'If cmboLetterName.Value = "Train2GainOpenDayLetter" Then
        'DoCmd.OpenQuery "qryOpenDay", , acAdd
    'End If
    
        Set rs1 = CurrentDb().OpenRecordset("tblMailMerge", dbOpenDynaset)
    
        CurrentDb.Execute ("UPDATE tblMailMerge SET LetterBody ='" & Me!txtLetter & "'")
    
        'Turn warnings back on
        DoCmd.SetWarnings True
        
        MailMergeIt (CurrentProject.path & "\Letters\T2G New Year Letter.doc")
    End If
    End Function

  6. #6
    Join Date
    Sep 2005
    Posts
    240
    UPDATE: I have managed to get this to work now, didn't realise you couldn't use QueryDef with Make Table queries.

    Quick question, seeing as I've had to change the query to a select query, is there a recordset property that will save the recordset into a table?

    Thanks

  7. #7
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Kev,

    If qryMailMerge is a Make Table query, you don't need a separate rs as a recordset to tell what happened. Simply do this:

    After assigning the parameter values, add

    Code:
    Qdef.Execute
    If CurrentDb.TableDefs("Your New Table Name").RecordCount = 0 then
    ...
    By the way, I wouldn't use a Make Table query for this, as the above algorithm will cause a trappable error if qryMailMerge doesn't return any records. I would make qryMailMerge an Append query, wiping out the CONTENTS of the table each time running the program, but not the table itself. That way the table always exists, and can contain 0 or more records. The Execute method works on Append queries also. It works on all queries that return records, but NOT on Select queries.

    Sam

Posting Permissions

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