excel to access

[Guest]

I am trying to get excel to have a drop down menu with a list of products
allowed for a particular job, so say i wanted to order a light switch and i
needed 4 of them
it would read 120 volt light switch then quantity 4
then automatically take that and place it in access, for ordering, can
anyone help me

 

[moon]

Option Explicit

'Using Excel 2000 here
'Reference: Microsoft ActiveX Data Objects 2.8 Library

'- In Sheet1 is a DropDown, called: DropDown_Products
'- The quantity is in Cell F4
'- There is a CommandButton, called Button_Process
' to export both product and quantity to an Access
' database called ORDERS.mdb
'- ORDERS.mdb has two columns: Product & Quantity

Private Sub Button_Process_Click()

Dim dc
Dim db

Set dc = CreateObject("ADODB.Connection")
dc.Provider = "Microsoft.Jet.OLEDB.4.0"
dc.Open "C:\ORDERS.mdb"

Set db = CreateObject("ADODB.Recordset")
db.Open "ORDERS", dc, adOpenDynamic, adLockOptimistic, adCmdTable
db.AddNew

db.Fields("Product") = DropDown_Products.Text
db.Fields("Quantity") = Cells(4, 6).Value

db.Update
db.Close
dc.Close

Set db = Nothing
Set dc = Nothing

End Sub

'- Play around