Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
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;
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;
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
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
Private Sub Form_Load()
Call FillOrders
End Sub
Private Sub ListViewOrders_Click()
Call FillOrderDetails
End Sub
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
Private Sub ListViewOrders_Click()
Call FillOrderDetails
Call CalculateTotal
End Sub
Me.txtGrandTotal = Format(dblTotal, "###,##0.00")
Me.LblGrandTotal.Caption = "Grand Total:" & " " & Format(dblTotal, "###,##0.00")