Dim adoCN As ADODB.Connection
Dim strSQL As String
Public Function DBVLookUp(TableName As String, LookUpFieldName As String, LookupValue As String, ReturnField As String) As Variant
Dim adoRS As ADODB.Recordset
If adoCN Is Nothing Then SetUpConnection
Set adoRS = New ADODB.Recordset
strSQL = "SELECT " & LookUpFieldName & ", " & ReturnField & " FROM " & TableName & " WHERE " & LookUpFieldName & _
"='" & LookupValue & "';" ' If lookup value is a number then remove the two '
adoRS.Open strSQL, adoCN, adOpenForwardOnly, adLockReadOnly
DBVLookUp = adoRS.Fields(ReturnField).Value
adoRS.Close
End Function
Sub SetUpConnection()
On Error GoTo ErrHandler
Set adoCN = New Connection
adoCN.Provider = "Microsoft.Jet.OLEDB.4.0" 'Change to 3.51 for Access 97
adoCN.ConnectionString = "K:financemanagement reportingfinance reporting 2002.mdb" 'Change to your DB path
adoCN.Open
Exit Sub
ErrHandler:
MsgBox Err.Description, vbExclamation, "An error occurred"
End Sub