Ok so how are you getting the query to run from excel? How do you tell the query what Part number? will you want the macro to get you the one result? or work through the list? the code below should work for a single item, and can be adapted to work down the whole list of item#'s. I can't test as it would mean having to make a database with relevant fields.... try it and let me know.
Sub bitto()
Dim dbs As ADODB.Connection
Dim Rset1 As ADODB.Recordset ' ticket data
Dim sql, item As String
Set dbs = New ADODB.Connection
dbs.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\database.accdb" 'replace C:\database.accdb with your database info/name
Set Rset1 = New ADODB.Recordset
Set Rset1.ActiveConnection = dbs
item = Worksheets("Sheet1").Cells(3, 3).Value
sql = "SELECT * FROM yourtablename WHERE itemnumberfieldhere = '" & item & "' " 'put in your real table name and field in the table for item number
Rset1.Open sql, dbs, adOpenStatic, adLockOptimistic
If Rset1.RecordCount = 0 Then MsgBox ("Sorry the Item# was not found")
If Rset1.RecordCount = 1 Then
With Rset1
.MoveFirst
Worksheets("Sheet1").Cells(3, 5).Value = ![yourpricefieldnamehere] 'put in the real name of your price field
End With
If Rset1.RecordCount > 1 Then MsgBox ("There seems to be more than one entry for this item number")
Set Rset1 = Nothing
Set dbs = Nothing
End Sub
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks