DEVelopers HUT

'---------------------------------------------------------------------------------------
' Procedure : ValInTbl
' Author    : CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Verifies if a value is found in a 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:
' ~~~~~~~~~~~~~~~~
' sTable    : Name of the table to search for the specified value
' sField    : Name of the field in the specified table to search for the specified value
' sValue    : Value to search for
'
' Usage:
'~~~~~~~
' ValInTbl("tbl_Units", "UnitNo", 54201)
' ValInTbl("tbl_Employees", "FirstName", "Daniel")
' ValInTbl("tbl_Followup", "AttendInitSession", True)
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2009-Mar-14             Initial Release
' 2         2010-Oct-13             Adjust the sSQL string based on the table field type
'---------------------------------------------------------------------------------------
Function ValInTbl(sTable As String, sField As String, sValue As String) As Boolean
On Error GoTo Error_Handler
    Dim db      As DAO.Database
    Dim rs      As DAO.Recordset
    Dim sSQL    As String
 
    Set db = CurrentDb()
    Select Case db.TableDefs(sTable).Fields(sField).Type
        Case dbByte, dbInteger, dbLong, dbSingle, dbDouble, dbBoolean
            sSQL = "SELECT [" & sField & "] FROM [" & sTable & "] WHERE [" & sField & "]=" & sValue
        Case dbText, dbMemo
            sSQL = "SELECT [" & sField & "] FROM [" & sTable & "] WHERE [" & sField & "]='" & sValue & "'"
        Case dbDate
            sSQL = "SELECT [" & sField & "] FROM [" & sTable & "] WHERE [" & sField & "]=#" & sValue & "#"
    End Select
    Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
 
    If rs.RecordCount <> 0 Then
        ValInTbl = True
    Else
        ValInTbl = False
    End If
 
Error_Handler_Exit:
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: ValInTbl" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function