INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

ListView & Access

ListView & Microsoft Access - Part 2 (Master-Sub & Column total) by ZmrAbdulla
Posted: 12 Aug 05 (Edited 21 Jan 06)

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()
'=================ListView FillOrders===================
    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
    'Set up column headers
    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
        ' Add items and subitems to list control.
        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    ' no current record
        Resume Next
    Else
        MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
        Resume ErrorHandlerExit
    End If
End Sub

CODE

Private Sub FillOrderDetails()
'=================ListView Order Details===================
    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
    'Set up column headers
    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
        ' Add items and subitems to list control.
        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    ' no current record
        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
        'Calculation total of all Items in the Order
        dblTotal = dblTotal + CDbl(Me.ListViewOrderDetails.ListItems(j).SubItems(5))
    Next j
    With Me.ListViewOrderDetails.ListItems
        'Add an Empty row
        Set mItem = .Add()
        mItem.SubItems(1) = " "
        mItem.SubItems(5) = " "
        'Add the total row
        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")


Download Sample(168 kb zip) [Access 2000 file format (688 kb)]

Hope this helps







Back to Microsoft: Access Forms FAQ Index
Back to Microsoft: Access Forms Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close