ms access runtime error 3709....it is either closed or invalid in this context

Hi All,

I am trying to select more than 1 record from a table (if any records exist that is). Then insert part of this record into another table along with an extra value.

The code i am using is below but i keep getting the "runtime error 3709....it is either closed or invalid in this context"

Dim rs As ADODB.Recordset
        Set rs = New ADODB.Recordset
       
        rs.Open "SELECT tblQuotes_Endts.Endt_ID FROM tblQuotes_Endts " & _
        "WHERE tblQuotes_Endts.IFAPrem_ID = " & Forms!frmQuote_Update!txtIFAPremID & ", CurrentProject.Connection, adOpenStatic"
       
        Dim strSQL As String
       
        If Not rs.EOF Then
            Set rs = New ADODB.Recordset
            Do Until rs.EOF
                strSQL = "INSERT INTO tblPolicies_Endts (Policy_ID, Endt_ID) " & _
                "VALUES (" & Me.txtPolicyID & ", " & rs.Fields.Item("Endt_ID").Value & ")"
                CurrentDb.Execute strSQL, dbFailOnError ' dbSeeChanges
            rs.MoveNext
            Loop
        End If
       
        rs.Close
        Set rs = Nothing

Any suggestions would be gratefully received
andrewpiconnectAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
try this, copy and paste

rs.Open "SELECT tblQuotes_Endts.Endt_ID FROM tblQuotes_Endts " _
           & "WHERE tblQuotes_Endts.IFAPrem_ID = " & Forms!frmQuote_Update!txtIFAPremID,  CurrentProject.Connection, adOpenStatic



i remove the quote at the end of the statement  adOpenStatic"


if that does not work, upload a copy of the db
0
 
Rey Obrero (Capricorn1)Commented:
which line is raising the error ?


        If Not rs.EOF Then

'            Set rs = New ADODB.Recordset    ' Remove this line and  run the codes


            Do Until rs.EOF
                strSQL = "INSERT INTO tblPolicies_Endts (Policy_ID, Endt_ID) " & _
                "VALUES (" & Me.txtPolicyID & ", " & rs!Endt_ID & ")"
                CurrentDb.Execute strSQL, dbFailOnError ' dbSeeChanges
            rs.MoveNext
            Loop
        End If
0
 
Dale FyeCommented:
I assume you are getting the error here:

=> strSQL = "INSERT INTO tblPolicies_Endts (Policy_ID, Endt_ID) " _
                  & "VALUES (" & Me.txtPolicyID & ", " & rs.Fields.Item("Endt_ID").Value & ")"

This is most likely due to your reinstantiating the "rs" recordset variable.  Try:
Dim rs As ADODB.Recordset
     Set rs = New ADODB.Recordset
       
     rs.Open "SELECT tblQuotes_Endts.Endt_ID FROM tblQuotes_Endts " _
           & "WHERE tblQuotes_Endts.IFAPrem_ID = " & Forms!frmQuote_Update!txtIFAPremID
           & ", CurrentProject.Connection, adOpenStatic"
       
     Dim strSQL As String
       
     While Not rs.EOF

           strSQL = "INSERT INTO tblPolicies_Endts (Policy_ID, Endt_ID) " _
                  & "VALUES (" & Me.txtPolicyID & ", " & rs.Fields.Item("Endt_ID").Value & ")"
           CurrentDb.Execute strSQL, dbFailOnError ' dbSeeChanges
           rs.MoveNext
   
     Wend
       
     rs.Close
     Set rs = Nothing

Open in new window

0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
andrewpiconnectAuthor Commented:
Hi Guys,

Neither of your options worked unfortunately.

I am getting the error here if it helps?

rs.Open "SELECT tblQuotes_Endts.Endt_ID FROM tblQuotes_Endts " _
           & "WHERE tblQuotes_Endts.IFAPrem_ID = " & Forms!frmQuote_Update!txtIFAPremID
           & ", CurrentProject.Connection, adOpenStatic"
0
 
Rey Obrero (Capricorn1)Commented:
is the FORM "frmQuote_Update"  open when your running the codes


try this first

rs.Open "SELECT tblQuotes_Endts.Endt_ID FROM tblQuotes_Endts " _
           & "WHERE tblQuotes_Endts.IFAPrem_ID = " & Forms!frmQuote_Update!txtIFAPremID,  CurrentProject.Connection, adOpenStatic"


if the field "IFAPrem_ID" is Text data type, use this

rs.Open "SELECT tblQuotes_Endts.Endt_ID FROM tblQuotes_Endts " _
           & "WHERE tblQuotes_Endts.IFAPrem_ID = '" & Forms!frmQuote_Update!txtIFAPremID
           & "'", CurrentProject.Connection, adOpenStatic"
0
 
andrewpiconnectAuthor Commented:
yep, the form "frmQuote_Update" is open and the control txtIFAPremID is a number and is visible on that form, so i know it is there ready for collection.

i have checked and rechecked the table names and fields to make sure i have not misspelled anything.

The error keeps suggesting the connection is either closed or invalid for some strange reason
0
 
andrewpiconnectAuthor Commented:
SUCCESSSSSSSSSS!!!!!!!

i have been stuck on this for a cpl of hours and it turns out to be a simple removal of a quote sign.

Thank you so so much!!!!

Phewwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwww
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.