CODEWITHSUNDEEP
vbams-access
rohrl77
rohrl77

Reputation: 3337

vba access error 3349 reading record in linked table

I have a linked excel table in my access db. I need to read out individual records from this table. Some of the cells unfortunately contain errors of #NA type in the cells. When my code comes to such a cell it throws a Run-time error 3349.

How can I either capture or simply read out the value in the cell?

The run time error appears on the If rs.Fields("SFDR_SUSTAINABLE") = 1 Then line

Here is all of my code:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT SFDR_SUSTAINABLE, E_SUSTAINABLE, SDG_E_CONTRIBUTION, SDG_S_CONTRIBUTION FROM ShareClasses WHERE ShareClassISIN='" & ISIN & "'")

Dim strMsg As String
If rs.RecordCount = 0 Then
    Exit Function
Else
    If rs.Fields("SFDR_SUSTAINABLE") = 1 Then strMsg = vbNewLine & "SFDR Sustainable" 'SFDR Sustainable
    If rs.Fields("E_SUSTAINABLE") = 1 Then strMsg = strMsg & vbNewLine & "Envir. Sust." 'Environmentally Sustainable
    If rs.Fields("SDG_E_CONTRIBUTION") = 1 Then strMsg = strMsg & vbNewLine & "Sust. Cont. (Envir.)" 'Sustainable Contribution (Environmental)
    If rs.Fields("SDG_S_CONTRIBUTION") = 1 Then strMsg = strMsg & vbNewLine & "Sust. Cont. (Social)" 'Sustainable Contribution (Social)
End If

strMsg = Mid(strMsg, 3)
ESG_Rating = strMsg

End Function```

Upvotes: 0

Views: 227

Answers (1)

Gustav
Gustav

Reputation: 55806

The simple method is to ignore the error:

If rs.RecordCount = 0 Then
    Exit Function
Else
    ' Ignore errors and continue.
    On Error Resume Next

    If rs.Fields("SFDR_SUSTAINABLE") = 1 Then
        If Err.Number = 0 Then     
            strMsg = vbNewLine & "SFDR Sustainable" 'SFDR Sustainable
        End If    
    End If
    If rs.Fields("E_SUSTAINABLE") = 1 Then 
        If Err.Number = 0 Then     
            strMsg = strMsg & vbNewLine & "Envir. Sust." 'Environmentally Sustainable
        End If    
    End If
    If rs.Fields("SDG_E_CONTRIBUTION") = 1 Then 
        If Err.Number = 0 Then     
            strMsg = strMsg & vbNewLine & "Sust. Cont. (Envir.)" 'Sustainable Contribution (Environmental)
        End If
    End If
    If rs.Fields("SDG_S_CONTRIBUTION") = 1 Then 
        If Err.Number = 0 Then     
            strMsg = strMsg & vbNewLine & "Sust. Cont. (Social)" 'Sustainable Contribution (Social)
        End If    
    End If
    
    ' Resume error handling.
    On Error Goto 0
End If

That will simply skip the field if it holds N/A.

Upvotes: 1

Related Questions