Testing if field exists with VBA

[JoeS01]

Does anyone have any code to test if a Field in an Access table exists, please?

All help and suggestions or links gratefully received

 

[SydneyGeek]

Here's a custom function that does the job, and an example of how to call it.

''==========================================================
''FieldExists function
''Takes: The name of a table and field whose existence we want to test.
''Returns: True if field is found, False otherwise.
''Description: Test that a field exists by trying to assign
''a Field object to it. All we need to do is check the value
''of Err.Number.
''If the field has been found, Err.Number is 0 and the
''FieldExists function returns True; otherwise it is False.
''Created: 04 Dec 2007 by Denis Wright.
''==========================================================
Function FieldExists(strTable As String, strField As String) As Boolean
    Dim rst As Recordset
    Dim fld As DAO.Field
    
    FieldExists = False
    
    On Error GoTo Err_Handle
    
    Set rst = CurrentDb.TableDefs(strTable).OpenRecordset
    
    For Each fld In rst.Fields
        If fld.Name = strField Then
            FieldExists = True
            Exit Function
        End If
    Next fld
    
    rst.Close
    Set rst = Nothing

Err_Exit:
    Exit Function
    
Err_Handle:
    Select Case Err.Number
        Case 3265 'TableDef not found
            MsgBox "This table does not exist in the database. Please check spelling"
        Case Else
            MsgBox "Error " & Err.Number & ": " & Err.Description
    End Select
    Resume Err_Exit
    
End Function

Function CheckField()
    If FieldExists("tblStaff", "StaffName") Then
        MsgBox "Field found"
    Else
        MsgBox "Field not found"
    End If
End Function

Denis

 

[JoeS01]

many thanks Denis , worked like a charm
I have learnt a lot from this one. I particularly liked the error handling routine at the bottom.

 

[JoeS01]

Forgot to mention, I had to disambiguate by specifying which library's recordset I wanted want. Used:
Dim rs As DAO.Recordset
not:
Dim rs As Recordset


hope this helps other users of this great procedure

 

[SydneyGeek]

Thanks Joe,

I should have caught that myself ... :wink:

Denis

 

[topdesk123]

Forgot to mention, I had to disambiguate by specifying which library's recordset I wanted want. Used:
Dim rs As DAO.Recordset
not:
Dim rs As Recordset


hope this helps other users of this great procedure

Hello,

I need to take this one step further please. I need to determine if one of two fields exists and retrieve its value to use in another function.

Thanks in advance!