DEVelopers HUT

'---------------------------------------------------------------------------------------
' Procedure : DoesTblFieldExist
' Author    : CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Determine if a field exists with the specified table
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sTableName: Name of the table to check the existance of the field in
' sFieldName: Name of the field to check the existance of
'
' Usage Example:
' ~~~~~~~~~~~~~~~~
' DoesTblFieldExist("Table1","Field1")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2010-Feb-02             Initial Release
'---------------------------------------------------------------------------------------
Function DoesTblFieldExist(sTableName As String, sFieldName As String) As Boolean
   Dim db            As DAO.Database
   Dim tdf           As TableDef
   Dim I             As String
   Dim bTableExists  As Boolean
 
On Error GoTo Error_Handler
   'Initialize our variables
   DoesTblFieldExist = False
   bTableExists = False
 
   Set db = CurrentDb()
   Set tdf = db.TableDefs(sTableName)
 
   bTableExists = True  'If we made it to here without triggering an error
                        'the table exists
  
   I = tdf.Fields(sFieldName).Name
 
   DoesTblFieldExist = True   'If we made it to here without triggering an
                              'error the table field exists

Error_Handler_Exit:
   On Error Resume Next
   Set tdf = Nothing
   Set db = Nothing
   Exit Function
 
Error_Handler:
   If Err.Number = 3265 And bTableExists = False Then
      'Table not found in the current database
      MsgBox "The specified Table could not be found in the current database", vbCritical
   ElseIf Err.Number = 3265 And bTableExists = True Then
      'Field not found in the specified table
     
   Else
      MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
      Err.Number & vbCrLf & "Error Source: DoesTblFieldExist" & vbCrLf & "Error Description: " & _
      Err.Description, vbCritical, "An Error has Occured!"
   End If
   Resume Error_Handler_Exit
End Function