Hello mate,
This function may help you. First of all, here's how to use it:-
Assume you have a table in Access that looks like this:-
Vlookup in Access database.xls |
---|
|
---|
| A | B | C | D | E |
---|
1 | account_id | account_parent | account_description | account_type | account_rollup |
---|
2 | 1000 | | Assets | Asset | ~ |
---|
3 | 2000 | | Liabilities | Liability | ~ |
---|
4 | 3000 | 5000 | NetSales | Income | + |
---|
5 | 3100 | 3000 | GrossSales | Income | + |
---|
6 | 3200 | 3000 | CostofGoodsSold | Income | - |
---|
7 | 4000 | 5000 | TotalExpense | Expense | - |
---|
8 | 4100 | 4000 | General&Administration | Expense | + |
---|
9 | 4200 | 4000 | InformationSystems | Expense | + |
---|
10 | 4300 | 4000 | Marketing | Expense | + |
---|
11 | 4400 | 4000 | Lease | Expense | + |
---|
12 | 5000 | | NetIncome | Income | + |
---|
|
---|
You could use the function DBVlookup like this:-
Here is the code which you should paste into a standard module. You'll also need to change the DatabasePath constant to the path and name of your database.
Let me know how you get on,
Dan<font face=Courier New><SPAN style="color:#00007F">Dim</SPAN> adoCN<SPAN style="color:#00007F">As</SPAN> ADODB.Connection<SPAN style="color:#00007F">Dim</SPAN> strSQL<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN><SPAN style="color:#00007F">Const</SPAN> DatabasePath<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN> = "C:\Program Files\foodmart 2000.mdb"<SPAN style="color:#007F00">'Function argument descriptions</SPAN><SPAN style="color:#007F00">'LookupFieldName - the field you wish to search</SPAN><SPAN style="color:#007F00">'LookupValue - the value in LookupFieldName you're searching for</SPAN><SPAN style="color:#007F00">'ReturnField - the matching field containing the value you wish to return</SPAN><SPAN style="color:#00007F">Public</SPAN><SPAN style="color:#00007F">Function</SPAN> DBVLookUp(TableName<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN>, _
LookUpFieldName<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN>, _
LookupValue<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN>, _
ReturnField<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN>)<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Variant</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> adoRS<SPAN style="color:#00007F">As</SPAN> ADODB.Recordset
<SPAN style="color:#00007F">If</SPAN> adoCN<SPAN style="color:#00007F">Is</SPAN><SPAN style="color:#00007F">Nothing</SPAN><SPAN style="color:#00007F">Then</SPAN> SetUpConnection
<SPAN style="color:#00007F">Set</SPAN> adoRS =<SPAN style="color:#00007F">New</SPAN> ADODB.Recordset
strSQL = "SELECT " & LookUpFieldName & ", " & ReturnField & _
" FROM " & TableName & _
" WHERE " & LookUpFieldName & "=" & LookupValue & ";"
<SPAN style="color:#007F00">' If lookup value is a number then remove the two '</SPAN>
adoRS.Open strSQL, adoCN, adOpenForwardOnly, adLockReadOnly
<SPAN style="color:#00007F">If</SPAN> adoRS.BOF And adoRS.EOF<SPAN style="color:#00007F">Then</SPAN>
DBVLookUp = "Value not Found"
<SPAN style="color:#00007F">Else</SPAN>
DBVLookUp = adoRS.Fields(ReturnField).Value
<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
adoRS.Close<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Function</SPAN><SPAN style="color:#00007F">Sub</SPAN> SetUpConnection()
<SPAN style="color:#00007F">On</SPAN><SPAN style="color:#00007F">Error</SPAN><SPAN style="color:#00007F">GoTo</SPAN> ErrHandler
<SPAN style="color:#00007F">Set</SPAN> adoCN =<SPAN style="color:#00007F">New</SPAN> Connection
adoCN.Provider = "Microsoft.Jet.OLEDB.4.0" <SPAN style="color:#007F00">'Change to 3.51 for Access 97</SPAN>
adoCN.ConnectionString = DatabasePath
adoCN.Open
<SPAN style="color:#00007F">Exit</SPAN><SPAN style="color:#00007F">Sub</SPAN>
ErrHandler:
MsgBox Err.Description, vbExclamation, "An error occurred"<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN></FONT>
Edit (JPG): Modified the posted code so it would copy/show better.