Code to 'lookup' value in Access database

[maacmaac]

Please help!!!

I am trying to lookup a value in Access but am having problems with th
code. I have seen the code others have used on this forum, but I jus
can't seem to get it working. I keep getting "#VALUE!" as an error

I have two files [1] Excel file called "FrontLine" [2] Access fil
called "Frontline" and table within Access file is also calle
"Frontline".

I am trying to lookup a value in B5 & B6. B5 should return a value o
"Lettuce"; B6 should return a value of "Orange".

The Excel file looks as follows:

A B C D
1 ProductNumber Package Price UPC
2 252 Orange 5.44 346347245
3 502 Apple 8.73 493245778
4 319 Cucumber 3.99 421188445
5 783 #VALUE! 2.15 945651478
6 252 #VALUE!

code in B5 =dbvlookup("FrontLine","ProductNumber",$A5,"Package")
code in B6 =dbvlookup("FrontLine","ProductNumber",$A6,"Package")

The Access File looks as follows:

ProductNumber Package FrontLinePrice UPC
252 Orange 3 564987987
502 Apple 6 849789333
319 Cucumber 2 463333798
783 Lettuce 8 442777332



The VBA code is as follows:

Dim strSQL As String

Const DatabasePath As String = "C:\Documents and Settings\Mike\M
Documents\FrontLine.mdb"

Public Function DBVLookUp(FrontLine As String, _
ProductNumber As String, _
A6 As String, _
Package As String) As Variant

If adoCN Is Nothing Then SetUpConnection

Set adoRS = New ADODB.Recordset
strSQL = "SELECT " & ProductNumber & ", " & Package & _
" FROM " & FrontLine & _
" WHERE " & ProductNumber & "=" & A6 & ";"
adoRS.Open strSQL, adoCN, adOpenForwardOnly, adLockReadOnly
If adoRS.BOF And adoRS.EOF Then
DBVLookUp = "Value not Found"
Else
DBVLookUp = adoRS.Fields(Package).Value
End If
adoRS.Close
End Function

Sub SetUpConnection()
On Error GoTo ErrHandler
Set adoCN = New Connection
adoCN.Provider = "Microsoft.Jet.OLEDB.4.0"
adoCN.ConnectionString = DatabasePath
adoCN.Open
Exit Sub
ErrHandler:
MsgBox Err.Description, vbExclamation, "An error occurred"
End Sub


I have also attached the Excel & Access files with same info.

I appreciate any help I can get. Thanks

+-------------------------------------------------------------------
|Filename: FrontLineExcel_ZIP.zip
|Download: http://www.excelforum.com/attachment.php?postid=3829
+-------------------------------------------------------------------

 

[Guest]

I think your query is not correct. Maybe more like...

strSQL = "SELECT Package FROM " & FrontLine & _
" WHERE ProductNumber =" & A6 & ";"

Please help!!!

I am trying to lookup a value in Access but am having problems with the
code. I have seen the code others have used on this forum, but I just
can't seem to get it working. I keep getting "#VALUE!" as an error

I have two files [1] Excel file called "FrontLine" [2] Access file
called "Frontline" and table within Access file is also called
"Frontline".

I am trying to lookup a value in B5 & B6. B5 should return a value of
"Lettuce"; B6 should return a value of "Orange".

The Excel file looks as follows:

A B C D
1 ProductNumber Package Price UPC
2 252 Orange 5.44 346347245
3 502 Apple 8.73 493245778
4 319 Cucumber 3.99 421188445
5 783 #VALUE! 2.15 945651478
6 252 #VALUE!

code in B5 =dbvlookup("FrontLine","ProductNumber",$A5,"Package")
code in B6 =dbvlookup("FrontLine","ProductNumber",$A6,"Package")

The Access File looks as follows:

ProductNumber Package FrontLinePrice UPC
252 Orange 3 564987987
502 Apple 6 849789333
319 Cucumber 2 463333798
783 Lettuce 8 442777332



The VBA code is as follows:

Dim strSQL As String

Const DatabasePath As String = "C:\Documents and Settings\Mike\My
Documents\FrontLine.mdb"

Public Function DBVLookUp(FrontLine As String, _
ProductNumber As String, _
A6 As String, _
Package As String) As Variant

If adoCN Is Nothing Then SetUpConnection

Set adoRS = New ADODB.Recordset
strSQL = "SELECT " & ProductNumber & ", " & Package & _
" FROM " & FrontLine & _
" WHERE " & ProductNumber & "=" & A6 & ";"
adoRS.Open strSQL, adoCN, adOpenForwardOnly, adLockReadOnly
If adoRS.BOF And adoRS.EOF Then
DBVLookUp = "Value not Found"
Else
DBVLookUp = adoRS.Fields(Package).Value
End If
adoRS.Close
End Function

Sub SetUpConnection()
On Error GoTo ErrHandler
Set adoCN = New Connection
adoCN.Provider = "Microsoft.Jet.OLEDB.4.0"
adoCN.ConnectionString = DatabasePath
adoCN.Open
Exit Sub
ErrHandler:
MsgBox Err.Description, vbExclamation, "An error occurred"
End Sub


I have also attached the Excel & Access files with same info.

I appreciate any help I can get. Thanks.


+-------------------------------------------------------------------+
|Filename: FrontLineExcel_ZIP.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3829 |
+-------------------------------------------------------------------+