Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

ListView & Microsoft Access - Part 2 (Master-Sub & Column total)

ListView & Access

ListView & Microsoft Access - Part 2 (Master-Sub & Column total)

by  ZmrAbdulla  Posted    (Edited  )
This is the Second part of ListView & Microsoft Access

ListView & Microsoft Access - Part 1 - (Load Data) faq702-6025

In this FAQ I will try to explain how to create a Master-Sub ListViews including a column total.
We need to create some queries so that we can retrive name of customers,employees, product than their respective IDs.

Here is the SQL for the master list.(QryOrders)
Code: 
SELECT
    Orders.OrderID
  , Orders.EmployeeID
  , Orders.OrderDate
  , Sum(Orders.Freight) AS SumOfFreight
  , Employees!LastName & " " & Employees!FirstName AS Employee
  , Customers.CompanyName
FROM
   ((Orders 
INNER JOIN
   Employees 
ON
   Orders.EmployeeID = Employees.EmployeeID) 
INNER JOIN
   Customers 
ON
   Orders.CustomerID = Customers.CustomerID) 
INNER JOIN
   [Order Details] 
ON
   Orders.OrderID = [Order Details].OrderID
GROUP BY
   Orders.OrderID
  , Orders.EmployeeID
  , Orders.OrderDate
  , Employees!LastName & " " & Employees!FirstName
  , Customers.CompanyName;
And here is one for the sub list(QryOrderDetails)
Code: 
SELECT  DISTINCT
    [Order Details].OrderID
  , [Order Details].ProductID
  , Products.ProductName
  , [Order Details].UnitPrice
  , [Order Details].Quantity
  , [Order 
Details].Discount
  , CCur([Order Details].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100 AS ExtendedPrice
FROM
   [Order Details] 
INNER JOIN
   Products 
ON
   [Order Details].ProductID = Products.ProductID
ORDER BY
   [Order Details].OrderID;
Now we need two ListViews on the form (ListViewOrders & ListViewOrderDetails)

Next is to write code to fill the Listviews.
Remember to set reference to Microsoft DAO library.
Code: 
Private Sub FillOrders()
[b][color green]'=================ListView FillOrders===================[/color][/b]
    On Error GoTo ErrorHandler
    Dim rs As DAO.Recordset
    Dim db As Database
    Dim lstItem As ListItem
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("SELECT  * FROM QryOrders")
    DoCmd.Echo False
    With Me.ListViewOrders
        .View = lvwReport
        .ListItems.Clear
        .ColumnHeaders.Clear
    End With
    [color green]'Set up column headers[/color]
    With Forms!frmListViewMasterSub.ListViewOrders.ColumnHeaders
        .Add , , "Ord-ID", 1000, lvwColumnLeft
        .Add , , "Customer", 2800, lvwColumnLeft
        .Add , , "Employee", 2800, lvwColumnLeft
        .Add , , "OrderDate", 1300, lvwColumnLeft
        .Add , , "Freight", 1500, lvwColumnRight
    End With
    rs.MoveFirst
    Do Until rs.EOF
        [color green]' Add items and subitems to list control.[/color]
        Set lstItem = Forms!frmListViewMasterSub.ListViewOrders.ListItems.Add()
        lstItem.Text = rs!OrderID
        lstItem.SubItems(1) = rs!CompanyName
        lstItem.SubItems(2) = rs!Employee
        lstItem.SubItems(3) = Format(rs!OrderDate, "Medium Date")
        lstItem.SubItems(4) = rs!SumOfFreight
        rs.MoveNext
    Loop
    rs.Close
    DoCmd.Echo True
ErrorHandlerExit:
    Exit Sub
ErrorHandler:
    If Err = 3021 Then    [color green]' no current record[/color]
        Resume Next
    Else
        MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
        Resume ErrorHandlerExit
    End If
End Sub
Code: 
Private Sub FillOrderDetails()
[b][color green]'=================ListView Order Details===================[/color][/b]
    On Error GoTo ErrorHandler
    Dim rs As DAO.Recordset
    Dim db As Database
    Dim lstItem As ListItem
    Dim strOrderSQL As String
    Set db = CurrentDb()
    strOrderSQL = "SELECT  * FROM QryOrderDetails where [OrderID] =" & Forms!FrmListViewMasterSub.ListViewOrders.SelectedItem.Text
    Set rs = db.OpenRecordset(strOrderSQL)
    With Me.ListViewOrderDetails
        .View = lvwReport
        .ListItems.Clear
        .ColumnHeaders.Clear
    End With
   [color green] 'Set up column headers[/color]
    With Me.ListViewOrderDetails.ColumnHeaders
        .Add , , "Ord-ID", 1000, lvwColumnLeft
        .Add , , "Product Name", 3000, lvwColumnLeft
        .Add , , "Unit Price", 1200, lvwColumnRight
        .Add , , "Quantity", 1200, lvwColumnRight
        .Add , , "Discount %", 1400, lvwColumnRight
        .Add , , "Extended Price", 1800, lvwColumnRight
    End With
    rs.MoveFirst
    Do Until rs.EOF
       [color green] ' Add items and subitems to list control.[/color]
        Set lstItem = Forms!frmListViewMasterSub.ListViewOrderDetails.ListItems.Add()
        lstItem.Text = rs!OrderID
        lstItem.SubItems(1) = rs!ProductName
        lstItem.SubItems(2) = Format(rs!UnitPrice, "#,##,0.00#")
        lstItem.SubItems(3) = rs!Quantity
        lstItem.SubItems(4) = Format(rs!Discount, "#,##,0%")
        lstItem.SubItems(5) = Format(rs!ExtendedPrice, "#,##,0.00#")
        rs.MoveNext
    Loop
    rs.Close
    DoCmd.Echo True
ErrorHandlerExit:
    Exit Sub
ErrorHandler:
    If Err = 3021 Then   [color green] ' no current record[/color]
        Resume Next
    Else
        MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
        Resume ErrorHandlerExit
    End If
End Sub
Fill the ListViewOrders
Code: 
Private Sub Form_Load()
    Call FillOrders   
End Sub
Your master list will be populated when you open the form.

To view details of respective records of the order selected on the master list in the sub list you need to call the sub when you click the list.
Code: 
Private Sub ListViewOrders_Click()
    Call FillOrderDetails    
End Sub
Your sub list will be populated with details of respective order selected.

Now you need to have column total of the order amount. The code below calculates the total and add it to the sublistview(ListViewOrderDetails)
Code: 
Private Sub CalculateTotal()
    Dim dblTotal As Double
    Dim mItem As ListItem
    Dim j As Single
    For j = 1 To Me.ListViewOrderDetails.ListItems.Count
        [color green]'Calculation total of all Items in the Order[/color]
        dblTotal = dblTotal + CDbl(Me.ListViewOrderDetails.ListItems(j).SubItems(5))
    Next j
    With Me.ListViewOrderDetails.ListItems
       [color green] 'Add an Empty row[/color]
        Set mItem = .Add()
        mItem.SubItems(1) = " "
        mItem.SubItems(5) = " "
        [color green]'Add the total row[/color]
        Set mItem = .Add()
        mItem.SubItems(1) = "Grand Total"
        mItem.SubItems(5) = Format(dblTotal, "###,##0.00")
    End With
End Sub
Now you need to change the ListViewOrders_Click a little so that the column total also will appear when you click on it.
Code: 
Private Sub ListViewOrders_Click()
    Call FillOrderDetails
    Call CalculateTotal
End Sub
If you need to display the Grand Total on textbox or a label then you can call it like below.
Code: 
 Me.txtGrandTotal = Format(dblTotal, "###,##0.00")
 Me.LblGrandTotal.Caption = "Grand Total:" & " " & Format(dblTotal, "###,##0.00")


[link http://www.geocities.com/zameerabd/dl/LVMasterSub.zip]Download Sample[/link](168 kb zip) [Access 2000 file format (688 kb)]

Hope this helps






Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top