Public Function FastLookup(strFieldName As String, strTableName As String, strWhere As String) As Variant '******************************************************************************************************* ' Name: FastLookup ' Purpose: Fast replacement for DLookup ' ' Inputs: strFieldName As String ' strTableName As String ' strWhere As String ' Returns: Variant ' Author: Arvin Meyer ' Date: April 9, 1997 ' Updated: June 15, 2005 ' Usage: ' If FastLookup("FieldName", "TableName", "FieldName ='" & Me.txtControlName & "'") = Me.txtControlName Then ' MsgBox "This value exists. Please choose again", vbOKOnly, "Duplicate!" ' Me.txtControlName.SetFocus ' Exit Sub ' End If ' '*************************************************************************************************************** On Error GoTo Error_Handler Dim db As DAO.Database Dim rst As DAO.Recordset Dim Temp As Variant Set db = CurrentDb If strWhere = "" Then Set rst = db.OpenRecordset("Select [" & strFieldName & "] From [" & strTableName & "]", dbOpenSnapshot) Else Set rst = db.OpenRecordset("Select [" & strFieldName & "] From [" & strTableName & "] Where " & strWhere, dbOpenSnapshot) End If If Not rst.BOF Then rst.MoveFirst Temp = rst(0) Else Temp = Null End If rst.Close FastLookup = Temp Exit_Here: On Error Resume Next rst.Close Set rst = Nothing Set db = Nothing Exit Function Error_Handler: MsgBox Err.Number & ": " & Err.Description Resume Exit_Here End Function