Check if Field exists in Recordset

[aliendan]

I need to check to see if a field exists in a recordset. I've tryed the following code but it doesn't work. It returns the same result whether it exists or not. Does anyone have a better idea? It will be greatly appreciated.

Dim fld as Field

On Error Resume Next
Set fld = Recordset.Fields("FieldName")
If Err.Number <> 0 Then
' Field does NOT exist
Else
' It does
End If

BTW, I got this code from a much earlier thread I found.

Thanks,
Dan


Dan Rogotzke
Dan_Rogotzke@oxy.com

 

[Golom]

Seems to work OK for me. Just to be explicit I used

Private Sub Command2_Click()
    Dim db                          As DAO.Database
    Dim rs                          As DAO.Recordset
    Dim fd                          As DAO.Field
    Set db = DAO.DBEngine(0).OpenDatabase(".\myDatabase.mdb")
    Set rs = db.OpenRecordset("Select * From client")
    On Error Resume Next

    Err.Clear
    [COLOR=black cyan]' This field DOES NOT EXIST[/color]
    Set fd = rs.Fields("NONEXIST")
    If Err.Number <> 0 Then
        MsgBox "'NONEXIST' Does NOT exist"
    Else
        MsgBox "'NONEXIST' DOES exist"
    End If

    Err.Clear
    [COLOR=black cyan]' This field DOES EXIST[/color]
    Set fd = rs.Fields("CL_CODE")
    If Err.Number <> 0 Then
        MsgBox "'CL_CODE' Does NOT exist"
    Else
        MsgBox "'CL_CODE' DOES exist"
    End If

    Set fd = Nothing
    Set rs = Nothing
    Set db = Nothing

End Sub

 

[JoeAtWork]

Always insert Err.Clear before the line of code that you are testing to see if it raises an error. This resets Err.Number to zero. Otherwise, Err.Number may contain any previous error that occurred.

 

[cmmrfrds]

Seems cleaner to me to explicitly ask for the field instead of checking for an error.

Dim fld As ADODB.Field

For Each fld In Me.Recordset.Fields
If fld.Name = "yourfieldname" Then
Debug.Print "The Field Name = "; fld.Name
Exit For
End If
Next

 

[aliendan]

Thank you all for the solutions. While I was waiting for replies I found this solution and it works great.

Err.Clear
Dim bExists As Boolean, s As String
On Error Resume Next
s = rst.Fields("FieldName").Name
bExists = (Err.Number = 0)
On Error GoTo 0
If bExists then
'it exists.
Else
'it doesn't exist.
End If


Thanks again to you all and I hope someday I might have an idea that would help each of you.

Dan


Dan Rogotzke
Dan_Rogotzke@oxy.com