CODEWITHSUNDEEP
vbams-accessrecordset
PanKleks
PanKleks

Reputation: 21

Access vba - getting runtime error 3420 (invalid object) when working with recordset in loop

I got following problem I cannot get pass. I am trying to create recordset in loop. Idea is to add to database some entries related to user selected date ranges in user form. When user selects just one date code works fine but when user selects date ranges it crashes with above runtime error on second loop pass - invalid object or has no values. Here is my code (part of it in fact, a bit simplified to show the problem):

Dim sql, sql2 As String
Dim x, daty As Integer

Dim dbs As DAO.Database
Set dbs = CurrentDb
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset

For x = 0 To 3   '''replaced user selected date ranges with 3 to simplify code
     sql = "INSERT INTO tDzialaniaRejestracja (IdSzkolenia, TerminRozpoczecia) VALUES (" & Me.IdSzkolenia & ", '" & Format((Me.DataRozpoczecia + x), "dd.mm.yyyy")  & "')"
     With dbs
        Set qdf = dbs.CreateQueryDef("", sql)    '''here i get runtine error 3420 on second loop pass
        qdf.Execute dbFailOnError
        sql2 = "SELECT @@IDENTITY"
        Set rs = .OpenRecordset(sql2, dbOpenDynaset)
        lastID = rs.Fields(0)
        rs.Close
        dbs.Close
     End With

     Me.IdDzialania = lastID
     Me.ProwadzacyFirmaZewn.Value = 23

     Set qdf = Nothing
     Set rs = Nothing

Next x

Any tips greatly appreciated. Thank you very much in advance.

regards

Upvotes: 2

Views: 1796

Answers (2)

Erik A
Erik A

Reputation: 32632

Don't create a new query for every loop iteration. Just use one query, and use parameters to insert different data.

The real problem, as Vityata pointed out, is that you're closing dbs on the first iteration. I've fixed that as well. But I just couldn't not optimize this code...

Dim sql, sql2 As String
Dim x, daty As Integer

Dim dbs As DAO.Database
Set dbs = CurrentDb
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
sql = "INSERT INTO tDzialaniaRejestracja (IdSzkolenia, TerminRozpoczecia) VALUES (@Param1, @Param2)"
Set qdf = dbs.CreateQueryDef("", sql)  
For x = 0 To 3   
     qdf.Parameters("@Param1") = Me.IdSzkolenia
     qdf.Parameters("@Param2") = Format((Me.DataRozpoczecia + x), "dd.mm.yyyy")
     With dbs
        qdf.Execute dbFailOnError
        sql2 = "SELECT @@IDENTITY"
        Set rs = .OpenRecordset(sql2, dbOpenDynaset)
        lastID = rs.Fields(0)
        rs.Close
     End With

     Me.IdDzialania = lastID
     Me.ProwadzacyFirmaZewn.Value = 23
     Set rs = Nothing
Next x
'Not necessary, no benefit whatsoever in the following code, but it goes here if you want it
dbs.Close
Set dbs = Nothing
Set qdf = Nothing

This has many additional advantages, such as allowing Access to compile the query only once, simplifying your code, avoiding possible SQL injection, etc.

Upvotes: 2

Vityata
Vityata

Reputation: 43575

I have tried this small piece and it gave me 3420 on the second loop as well. 

Sub TestMe()
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef

    Set dbs = CurrentDb
    Dim x As Long

    For x = 0 To 3
        Set qdf = dbs.CreateQueryDef("", "SELECT * FROM TABELLE1")
        dbs.Close
    Next x

End Sub

Thus, the problem is that you are closing the dbs in the first iteration and then you are refering against to it.

Upvotes: 3

Related Questions