当前位置: 动力学知识库 > 问答 > 编程问答 >

excel - vba fill in two , two listboxes from database

问题描述:

The question is to make a form and it should display two, two-column listboxes, one for customers, and one for products. When the user selects a customer and a product, the application should display OrderDate, QuotedPrice, QuantityOrdered, and the ExtendedPrice for each order for the selected product placed by the selected customer.

and this is what I have so far and keep getting this error " the connection can not be used its either closed or invalid"

Private Sub UserForm_Initialize()

Dim rowCount As Integer

Dim productArray(100, 2) As Variant ' Assume no more than 100 products.

Dim rowCount1 As Integer

Dim customerArray(100, 2) As Variant

' Populate the two-column list box with items from the recordset.

rowCount = 0

With rs

Do Until .EOF

productArray(rowCount, 0) = .Fields("ProductID")

productArray(rowCount, 1) = .Fields("ProductName")

rowCount = rowCount + 1

.MoveNext

Loop

End With

lbProducts.List = productArray

lbProducts.ListIndex = 0

rowCount = 0

With rs2

Do Until .EOF

productArray(rowCount1, 0) = .Fields("CustomerID")

productArray(rowCount1, 1) = .Fields("CustFirstName")

rowCount1 = rowCount1 + 1

.MoveNext

Loop

End With

lbCustomers.List = customerArray

lbCustomers.ListIndex = 0

End Sub

Sub GetOrderInfo()

Dim SQL As String

Dim rowCount As Integer

Range("F1") = productName

' Define SQL statement to get order info for selected product.

SQL = "SELECT O.OrderID, O.OrderDate, L.QuantityOrdered, " _

& "L.QuotedPrice, L.QuantityOrdered * L.QuotedPrice AS ExtendedPrice " _

& "FROM Orders O INNER JOIN LineItems L ON O.OrderID = L.OrderID " _

& "WHERE L.ProductID =" & productID & " " _

& "ORDER BY O.OrderDate, O.CustomerID"

' Run the query and use results to fill Orders sheet.

With rs

.Open SQL, cn

rowCount = 0

Do While Not .EOF

rowCount = rowCount + 1

topCell.Offset(rowCount, 1) = .Fields("OrderDate")

topCell.Offset(rowCount, 2) = .Fields("QuotedPrice")

topCell.Offset(rowCount, 3) = .Fields("QuantityOrdered")

topCell.Offset(rowCount, 4) = .Fields("ExtendedPrice")

.MoveNext

Loop

.Close

End With

End Sub

Sub GetOrderInfobycustomer()

Dim SQL As String

Dim rowCount As Integer

Range("B1") = customerName

' Define SQL statement to get order info for selected product.

SQL = "SELECT O.OrderID, O.OrderDate, L.QuantityOrdered, " _

& "L.QuotedPrice, L.QuantityOrdered * L.QuotedPrice AS ExtendedPrice " _

& "FROM Orders O INNER JOIN LineItems L ON O.OrderID = L.OrderID " _

& "WHERE L.CustomerID=" & customerID & " " _

& "ORDER BY O.OrderDate, O.CustomerID"

' Run the query and use results to fill Orders sheet.

With rs2

.Open SQL, cn

rowCount = 0

Do While Not .EOF

rowCount = rowCount + 1

topCell.Offset(rowCount, 1) = .Fields("OrderDate")

topCell.Offset(rowCount, 2) = .Fields("QuotedPrice")

topCell.Offset(rowCount, 3) = .Fields("QuantityOrdered")

topCell.Offset(rowCount, 4) = .Fields("ExtendedPrice")

.MoveNext

Loop

.Close

End With

End Sub

 Sub Main()

' Delete any previous results.

wsOrders.Range("B1") = ""

Set topCell = wsOrders.Range("A3")

With topCell

Range(.Offset(1, 0), .Offset(1, 4).End(xlDown)).ClearContents

End With

' Open connection to database.

With cn

.ConnectionString = "Data Source=" & ThisWorkbook.Path & "\Sales Orders.mdb"

.Provider = "Microsoft.ACE.OLEDB.12.0"

.Open

End With

Call GetProductList

Call GetOrderInfo

Call GetCustomerList

Call GetOrderInfobycustomer

' Close the connection.

cn.Close

wsOrders.Range("A2").Select

End Sub

Sub GetProductList()

Dim SQL As String

SQL = "SELECT ProductID, ProductName FROM Products"

rs.Open SQL, cn

frmProducts.Show

rs.Close

End Sub

Sub GetCustomerList()

Dim SQL As String

SQL = "SELECT CustomerID, CustFirstName FROM Customers"

rs2.Open SQL, cn

frmProducts.Show

rs2.Close

End Sub

分享给朋友:
您可能感兴趣的文章:
随机阅读: