Object invalid or no long set

[elwi]

Access 2013
Code that has been working is suddenly causing the error "Object invalid or no longer set". Line causing the error is marked.

Anyone run into this before? I've spent half the day on this getting nowhere fast.

Private Sub CreateTable()
    Dim dbsDb As dao.Database
    Dim tblTemp As dao.TableDef
    Dim fldTemp As dao.Field
    Dim rst As dao.Recordset
    Dim rstRsF As dao.Recordset
    
    Set dbsDb = CurrentDb
    
    'Select distinct values of strCltCdK to be used as table names
    Set rst = dbsDb.OpenRecordset("SELECT DISTINCT qselNewCltTbl.strCltCdK FROM qselNewCltTbl ORDER BY qselNewCltTbl.strCltCdK;")
    
    'loop to each distinct value of strCltCdK
    While Not rst.EOF
        
        'if the table to be created exists, delete it
        If fncExists("ttbl" & rst!strCltCdK) Then
            DeleteTable ("ttbl" & rst!strCltCdK)
        End If
        
        'code to initialize creation of table
        Set tblTemp = dbsDb.CreateTableDef("ttbl" & rst!strCltCdK)
        
        'create recordset to select all records equal to the current table name(strCltCdK) and use the value in the field strFldNmK as the field name
        Set rstRsF = dbsDb.OpenRecordset("SELECT qselNewCltTbl.intSq, qselNewCltTbl.strFldNmK, qselNewCltTbl.txtDscr, qselNewCltTbl.strTpK FROM qselNewCltTbl WHERE (((qselNewCltTbl.strCltCdK) = '" & rst!strCltCdK & "')) ORDER BY qselNewCltTbl.strCltCdK, qselNewCltTbl.intSq ")
        While Not rstRsF.EOF
            
            'since data type stated in field strTpK is not correct syntax, we'll use select case to change to appropriate data type used by Access
            Select Case rstRsF!strTpK
                Case "auto number"
                    Set fldTemp = tblTemp.CreateField(rstRsF!strFldNmK, dbLong)
                Case "Currency"
                    Set fldTemp = tblTemp.CreateField(rstRsF!strFldNmK, dbDouble)
                Case "Date/Time"
                    Set fldTemp = tblTemp.CreateField(rstRsF!strFldNmK, dbDate)
                Case "Double"
                    Set fldTemp = tblTemp.CreateField(rstRsF!strFldNmK, dbDouble)
            
            'code to create field
>>            tblTemp.Fields.Append fldTemp
            Set fldTemp = Nothing
            rstRsF.MoveNext
        Wend
        rstRsF.Close
        
        'add table
        dbsDb.TableDefs.Append tblTemp
        rst.MoveNext
        Set tblTemp = Nothing
    Wend
    rst.Close
    dbsDb.Close
End Sub

 

[MarkK]

Hey, welcome to the forum.

Looks like there is no End Select statement at the end of your Select Case block, but that shouldn't even compile. Maybe that happened when you copied your code???

Does it make a difference if you reference the .Value property of the fields here . . .

    Set fldTemp = tblTemp.CreateField(rstRsF!strFldNmK[COLOR="Red"].Value[/COLOR], dbLong)

. . . otherwise you are passing that whole field into the CreateField method.

 

[elwi]

You're right. When I cut part of the code to shorten it, I and accidently cut the End Select. Code does compile.

I added the .value in the code but still no joy. Still erring with the same message.

 

[MarkK]

Does it error on the first loop or is there something about the data in the recordsets that makes a difference?

 

[elwi]

First loop.

 

[Gasman]

So is tblTemp actually set?

 

[jdraw]

? Do you have a Case Else in case there is a value you are not testing?
As Gasman asked, are you sure tblTemp is set --you can check by stepping thru the code and watching the locals window.
If tblTemp is not actually set, then you could append a field???

 

[elwi]

You got it! I added a Debug.Print on rsF(strFldNmK) and it spit out a single record. Turns out that record was missing a value in strTpK. Soon as I filled that in, I was off to the races.

Why didn't I think of doing that hours ago!! Elementary. Thanks for digging me out.

 

[speakers_86]

I guess you might want to handle that case in your code to avoid this in the future.

 

[elwi]

Absolutely. I'm a recordset newbie who is learning the hard way.