Home

Operations on Record Sets
 

Overview of Record Operations 
 

Introduction

In Lesson 13, we saw various means of maintaining records using either database objects or SQL. In the same ways, the various libraries that ship with Microsoft Access provide their own approach. As you will see in the various sections, the Microsoft Access Object Library and DAO use the same techniques. ADO follows the same approach but sometimes add a few steps.

Consider the following list of videos:

 

 
 

Practical Learning Practical Learning: Introducing Record Operations
  1. Start Microsoft Access and create a blank database named Helena Department Store
  2. To create a new table, in the Tables section of the Database window, double-click Create Table in Design View and create the following columns
     
    Field Name Data Type Caption Other Properties
    ItemCategoryID AutoNumber Item Category ID Primary Key
    ItemCategory   Item Category  
  3. Save the table as ItemCategories and switch it to Datasheet View
  4. Create the following item categories:
     
    Item Category ID Item Category
    1 Women
    2 Men
    3 Girls
    4 Boys
    5 Toddlers
    6 Miscellaneous
  5. Close the table
  6. To create a new table, in the Tables section of the Database window, double-click Create Table in Design View and create the following columns:
     
    Field Name Data Type Caption Field Size Other Properties
    InventoryItemID AutoNumber Inventory Item ID   Primary Key
    DateEntered Date/Time Date Entered   Format: Medium Dale
    Input Mask: 99\->L<LL\-00;0;_
    ItemNumber   Item # 10  
    ItemName     80  
    ItemCategoryID        
    ItemSize   Item Size    
    OriginalPrice Number Original Price Double Format: Fixed
    UnitsInStock Number Units In Stock Integer  
  7. Save the table as InventoryItems
  8. Set the Data Type of ItemCategoryID to Lookup Wizard
  9. In the first page of the wizard, accept the first radio button and click Next
  10. In the second page of the wizard, click ItemCategories and click Next
  11. In the Available Fields, double-click ItemCategory and click Next
  12. Click Next and click Finish
  13. Save and close the table
  14. Using the New Object: AutoForm button, generate a form and save it with the default name
  15. Right-click the form and click Form Header/Footer
  16. In the Toolbox, click Command Button and click the Form Footer section
  17. Follow the wizard to create a button used to close the form. Set its caption to Close and its name to cmdClose
  18. Change its design as follows:
     
  19. Save and close the form
  20. In the Tables section of the Database window, right-click InventoryItems and click Save As...
  21. Type SaleItems as the name of the new table and click OK
  22. Right-click SaleItems and click Design View
  23. Make the following changes (you will rename some fields and add new others):
     
    Field Name Data Type Caption Field Size Other Properties
    SaleItemID   Sale Item ID    
    DateEntered        
    ItemNumber        
    ItemName        
    ItemCategoryID        
    ItemSize        
    MarkedPrice   Marked Price    
    DiscountRate Number Discount Rate Double Format: Percent
    Default Value: 0
    Validation Rule: <1
    Validation Text: Enter a value such as 0.0775 for a 7.75% tax rate
    PriceAfterDiscount Number Price Discount Double Format: Fixed
    UnitsInStock Number Units In Stock Integer  
  24. Save and close the table
  25. To create a new table, in the Tables section of the Database window, double-click Create Table in Design View and create the following columns:
     
    Field Name Data Type Caption Field Size Other Properties
    CustomerOrderID AutoNumber Order ID   Primary Key
    OrderDate Date/Time Order Date   Format: Medium Date
    Input Mask: 99\->L<LL\-00;0;_
    Item1Number     10  
    Item1Name     80  
    Item1Size        
    Item1MarkedPrice Number   Double Format: Fixed
    Item1DiscountRate Number   Double Format: Percent
    Default Value: 0
    Validation Rule: <1
    Validation Text: Enter a value such as 0.20 for a 20% discount
    Item1PriceAfterDiscount Number Price Discount Double Format: Fixed
    Item1Quantity Number   Integer  
    Item1SubTotal Number   Double Format: Fixed
    Item2Number     10  
    Item2Name     80  
    Item2Size        
    Item2MarkedPrice Number   Double Format: Fixed
    Item2DiscountRate Number   Double Format: Percent
    Default Value: 0
    Validation Rule: <1
    Validation Text: Enter a value such as 0.20 for a 20% discount
    Item2PriceAfterDiscount Number Price Discount Double Format: Fixed
    Item2Quantity Number   Integer  
    Item2SubTotal Number   Double Format: Fixed
    Item3Number     10  
    Item3Name     80  
    Item3Size        
    Item3MarkedPrice Number   Double Format: Fixed
    Item3DiscountRate Number   Double Format: Percent
    Default Value: 0
    Validation Rule: <1
    Validation Text: Enter a value such as 0.20 for a 20% discount
    Item3PriceAfterDiscount Number Price Discount Double Format: Fixed
    Item3Quantity Number   Integer  
    Item3SubTotal Number   Double Format: Fixed
    Item4Number     10  
    Item4Name     80  
    Item4Size        
    Item4MarkedPrice Number   Double Format: Fixed
    Item4DiscountRate Number   Double Format: Percent
    Default Value: 0
    Validation Rule: <1
    Validation Text: Enter a value such as 0.20 for a 20% discount
    Item4PriceAfterDiscount Number Price Discount Double Format: Fixed
    Item4Quantity Number   Integer  
    Item4SubTotal Number   Double Format: Fixed
    Item5Number     10  
    Item5Name     80  
    Item5Size        
    Item5MarkedPrice Number   Double Format: Fixed
    Item5DiscountRate Number   Double Format: Percent
    Default Value: 0
    Validation Rule: <1
    Validation Text: Enter a value such as 0.20 for a 20% discount
    Item5PriceAfterDiscount Number Price Discount Double Format: Fixed
    Item5Quantity Number   Integer  
    Item5SubTotal Number   Double Format: Fixed
    ItemsTotal Number Items Total Double Format: Fixed
    SalesTaxRate Number Sales Tax Rate Double Format: Percent
    Default Value: 0.0775
    Validation Rule: <1
    Validation Text: Enter a value such as 0.20 for a 20% discount
    OrderTotal Number Order Total Double Format: Fixed
  26. Save the table as CustomersOrders and close it
  27. Create form based on the CustomersOrders table
  28. Save it as CustomersOrders and design it as follows:
     
  29. Save the form
  30. On the form, click the first text box under Item #
  31. In the properties window, double-click On Lost Focus
  32. Click its ellipsis button and implement the event as follows:
      
    Private Sub EvaluateOrder()
    Dim dblItemsTotal As Double
    Dim dblTaxAmount As Double
    Dim dblNetPrice As Double
    
    dblItemsTotal = Val(Nz([Item1SubTotal]) + Nz([Item2SubTotal]) + _
                       Nz([Item3SubTotal]) + Nz([Item4SubTotal]) + _
                       Nz([Item5SubTotal]))
    dblTaxAmount = CLng(dblItemsTotal * Nz([SalesTaxRate]) * 100) / 100
    dblNetPrice = dblItemsTotal + dblTaxAmount
    
    Me.ItemsTotal = dblItemsTotal
    Me.SalesTaxAmount = dblTaxAmount
    Me.OrderTotal = dblNetPrice
End Sub

Private Sub Item1Number_LostFocus()
On Error GoTo ErrorOccurred
    
    If Not IsNull(DLookup("ItemName", "SaleItems", "ItemNumber = '" & Item1Number & "'")) Then
        [Item1Name] = DLookup("ItemName", "SaleItems", "ItemNumber = '" & Item1Number & "'")
        [Item1Size] = DLookup("ItemSize", "SaleItems", "ItemNumber = '" & Item1Number & "'")
        [Item1MarkedPrice] = DLookup("MarkedPrice", "SaleItems", "ItemNumber = '" & Item1Number & "'")
        [Item1DiscountRate] = DLookup("DiscountRate", "SaleItems", "ItemNumber = '" & Item1Number & "'")
        [Item1PriceAfterDiscount] = CDbl([Item1MarkedPrice]) - CLng(Nz([Item1MarkedPrice]) * Nz([Item1DiscountRate]) * 100) / 100
        [Item1Quantity] = "1"
        [Item1SubTotal] = [Item1PriceAfterDiscount]
        
        EvaluateOrder
    Else
        [Item1Number] = "000000"
        [Item1Name] = "Miscellaneous"
        [Item1Size] = "N/A"
        [Item1MarkedPrice] = "0.00"
        [Item1DiscountRate] = "0.00%"
        [Item1PriceAfterDiscount] = "0.00"
        [Item1Quantity] = "0"
        [Item1SubTotal] = "0.00"
        [Item1Name].SetFocus
    End If

GetOuttaHere:
    Exit Sub
    
ErrorOccurred:
    If Err.Number = -2147352567 Then
        Resume GetOuttaHere
    Else
        MsgBox "An error occured when retrieving the product information" & vbCrLf & _
               "Please call the program vendor and report the error as follows:" & vbCrLf & _
               "Error #: " & Err.Number & vbCrLf & _
               "Reason:  " & Err.Description
        Resume Next
    End If
End Sub
  33. Return to Microsoft Access and, on the form, click the second text box under Item #
  34. In the Events tab of the Properties window, double-click On Lost Focus and click its ellipsis button to implement the event as follows:
      
    Private Sub Item2Number_LostFocus()
On Error GoTo ErrorOccurred
    
    If Not IsNull(DLookup("ItemName", "SaleItems", "ItemNumber = '" & Item2Number & "'")) Then
        [Item2Name] = DLookup("ItemName", "SaleItems", "ItemNumber = '" & Item2Number & "'")
        [Item2Size] = DLookup("ItemSize", "SaleItems", "ItemNumber = '" & Item2Number & "'")
        [Item2MarkedPrice] = DLookup("MarkedPrice", "SaleItems", "ItemNumber = '" & Item2Number & "'")
        [Item2DiscountRate] = DLookup("DiscountRate", "SaleItems", "ItemNumber = '" & Item2Number & "'")
        [Item2PriceAfterDiscount] = CDbl([Item2MarkedPrice]) - CLng(Nz([Item2MarkedPrice]) * Nz([Item2DiscountRate]) * 100) / 100
        [Item2Quantity] = "1"
        [Item2SubTotal] = [Item2PriceAfterDiscount]
        
        EvaluateOrder
    Else
        [Item2Number] = "000000"
        [Item2Name] = "Miscellaneous"
        [Item2Size] = "N/A"
        [Item2MarkedPrice] = "0.00"
        [Item2DiscountRate] = "0.00%"
        [Item2PriceAfterDiscount] = "0.00"
        [Item2Quantity] = "0"
        [Item2SubTotal] = "0.00"
        [Item2Name].SetFocus
    End If

GetOuttaHere:
    Exit Sub
    
ErrorOccurred:
    If Err.Number = -2147352567 Then
        Resume GetOuttaHere
    Else
        MsgBox "An error occured when retrieving the product information" & vbCrLf & _
               "Please call the program vendor and report the error as follows:" & vbCrLf & _
               "Error #: " & Err.Number & vbCrLf & _
               "Reason:  " & Err.Description
        Resume Next
    End If
End Sub
  35. Return to Microsoft Access and, on the form, click the third text box under Item #
  36. In the Events tab of the Properties window, double-click On Lost Focus and click its ellipsis button to implement the event as follows:
      
    Private Sub Item3Number_LostFocus()
On Error GoTo ErrorOccurred
    
    If Not IsNull(DLookup("ItemName", "SaleItems", "ItemNumber = '" & Item3Number & "'")) Then
        [Item3Name] = DLookup("ItemName", "SaleItems", "ItemNumber = '" & Item3Number & "'")
        [Item3Size] = DLookup("ItemSize", "SaleItems", "ItemNumber = '" & Item3Number & "'")
        [Item3MarkedPrice] = DLookup("MarkedPrice", "SaleItems", "ItemNumber = '" & Item3Number & "'")
        [Item3DiscountRate] = DLookup("DiscountRate", "SaleItems", "ItemNumber = '" & Item3Number & "'")
        [Item3PriceAfterDiscount] = CDbl([Item3MarkedPrice]) - CLng(Nz([Item3MarkedPrice]) * Nz([Item3DiscountRate]) * 100) / 100
        [Item3Quantity] = "1"
        [Item3SubTotal] = [Item3PriceAfterDiscount]
        
        EvaluateOrder
    Else
        [Item3Number] = "000000"
        [Item3Name] = "Miscellaneous"
        [Item3Size] = "N/A"
        [Item3MarkedPrice] = "0.00"
        [Item3DiscountRate] = "0.00%"
        [Item3PriceAfterDiscount] = "0.00"
        [Item3Quantity] = "0"
        [Item3SubTotal] = "0.00"
        [Item3Name].SetFocus
    End If

GetOuttaHere:
    Exit Sub
    
ErrorOccurred:
    If Err.Number = -2147352567 Then
        Resume GetOuttaHere
    Else
        MsgBox "An error occured when retrieving the product information" & vbCrLf & _
               "Please call the program vendor and report the error as follows:" & vbCrLf & _
               "Error #: " & Err.Number & vbCrLf & _
               "Reason:  " & Err.Description
        Resume Next
    End If
End Sub
  37. Return to Microsoft Access and, on the form, click the fourth text box under Item #
  38. In the Events tab of the Properties window, double-click On Lost Focus and click its ellipsis button to implement the event as follows:
      
    Private Sub Item4Number_LostFocus()
On Error GoTo ErrorOccurred
    
    If Not IsNull(DLookup("ItemName", "SaleItems", "ItemNumber = '" & Item4Number & "'")) Then
        [Item4Name] = DLookup("ItemName", "SaleItems", "ItemNumber = '" & Item4Number & "'")
        [Item4Size] = DLookup("ItemSize", "SaleItems", "ItemNumber = '" & Item4Number & "'")
        [Item4MarkedPrice] = DLookup("MarkedPrice", "SaleItems", "ItemNumber = '" & Item4Number & "'")
        [Item4DiscountRate] = DLookup("DiscountRate", "SaleItems", "ItemNumber = '" & Item4Number & "'")
        [Item4PriceAfterDiscount] = CDbl([Item4MarkedPrice]) - CLng(Nz([Item4MarkedPrice]) * Nz([Item4DiscountRate]) * 100) / 100
        [Item4Quantity] = "1"
        [Item4SubTotal] = [Item4PriceAfterDiscount]
        
        EvaluateOrder
    Else
        [Item4Number] = "000000"
        [Item4Name] = "Miscellaneous"
        [Item4Size] = "N/A"
        [Item4MarkedPrice] = "0.00"
        [Item4DiscountRate] = "0.00%"
        [Item4PriceAfterDiscount] = "0.00"
        [Item4Quantity] = "0"
        [Item4SubTotal] = "0.00"
        [Item4Name].SetFocus
    End If

GetOuttaHere:
    Exit Sub
    
ErrorOccurred:
    If Err.Number = -2147352567 Then
        Resume GetOuttaHere
    Else
        MsgBox "An error occured when retrieving the product information" & vbCrLf & _
               "Please call the program vendor and report the error as follows:" & vbCrLf & _
               "Error #: " & Err.Number & vbCrLf & _
               "Reason:  " & Err.Description
        Resume Next
    End If
End Sub
  39. Return to Microsoft Access and, on the form, click the fifth text box under Item #
  40. In the Events tab of the Properties window, double-click On Lost Focus and click its ellipsis button to implement the event as follows:
      
    Private Sub Item5Number_LostFocus()
On Error GoTo ErrorOccurred
    
    If Not IsNull(DLookup("ItemName", "SaleItems", "ItemNumber = '" & Item5Number & "'")) Then
        [Item5Name] = DLookup("ItemName", "SaleItems", "ItemNumber = '" & Item5Number & "'")
        [Item5Size] = DLookup("ItemSize", "SaleItems", "ItemNumber = '" & Item5Number & "'")
        [Item5MarkedPrice] = DLookup("MarkedPrice", "SaleItems", "ItemNumber = '" & Item5Number & "'")
        [Item5DiscountRate] = DLookup("DiscountRate", "SaleItems", "ItemNumber = '" & Item5Number & "'")
        [Item5PriceAfterDiscount] = CDbl([Item5MarkedPrice]) - CLng(Nz([Item5MarkedPrice]) * Nz([Item5DiscountRate]) * 100) / 100
        [Item5Quantity] = "1"
        [Item5SubTotal] = [Item5PriceAfterDiscount]
        
        EvaluateOrder
    Else
        [Item5Number] = "000000"
        [Item5Name] = "Miscellaneous"
        [Item5Size] = "N/A"
        [Item5MarkedPrice] = "0.00"
        [Item5DiscountRate] = "0.00%"
        [Item5PriceAfterDiscount] = "0.00"
        [Item5Quantity] = "0"
        [Item5SubTotal] = "0.00"
        [Item5Name].SetFocus
    End If

GetOuttaHere:
    Exit Sub
    
ErrorOccurred:
    If Err.Number = -2147352567 Then
        Resume GetOuttaHere
    Else
        MsgBox "An error occured when retrieving the product information" & vbCrLf & _
               "Please call the program vendor and report the error as follows:" & vbCrLf & _
               "Error #: " & Err.Number & vbCrLf & _
               "Reason:  " & Err.Description
        Resume Next
    End If
End Sub
  41. Return to the form and save it
  42. On the form, click the first text box under Marked Price
  43. In the Events tab of the Properties window, double-click On Lost Focus and click its ellipsis button to implement the event as follows:
      
    Private Sub Item1MarkedPrice_LostFocus()
    Dim dblMarkedPrice As Double
    Dim dblDiscountRate As Double
    Dim dblDiscountAmount As Double
    Dim dblPriceAfterDicsount As Double
    Dim intQuantity As Integer
    Dim dblSubTotal As Double
    
    ' Get the current (marked) price of the item, in case the clerk changed it
    dblMarkedPrice = CDbl(Nz(Me.Item1MarkedPrice))
    ' Get the discount rate, whether it was entered automatically or manually
    dblDiscountRate = CDbl(Nz(Me.Item1DiscountRate))
    ' Calculate the amount of the discount based on the unit price and the rate
    dblDiscountAmount = CLng(dblMarkedPrice * dblDiscountRate * 100) / 100
    ' Subtract the discount amount, if any, from the marked price
    dblPriceAfterDicsount = dblMarkedPrice - dblDiscountAmount
    ' Get the quantity order by the customer
    intQuantity = CInt(Nz(Me.Item1Quantity))
    ' Calcute the sub-total based on the discounted price and the quantity
    dblSubTotal = dblPriceAfterDicsount * intQuantity
    
    ' Display the values in the appropriate text boxes
    Me.Item1PriceAfterDiscount = dblPriceAfterDicsount
    Me.Item1SubTotal = dblSubTotal
    
    ' Re-calculate the order if necessary
    EvaluateOrder
End Sub
  44. Return to Microsoft Access and, on the form, click the second text box under Marked Price
  45. In the Events tab of the Properties window, double-click On Lost Focus and click its ellipsis button to implement the event as follows:
      
    Private Sub Item2MarkedPrice_LostFocus()
    Dim dblMarkedPrice As Double
    Dim dblDiscountRate As Double
    Dim dblDiscountAmount As Double
    Dim dblPriceAfterDicsount As Double
    Dim intQuantity As Integer
    Dim dblSubTotal As Double
    
    dblMarkedPrice = CDbl(Nz(Me.Item2MarkedPrice))
    dblDiscountRate = CDbl(Nz(Me.Item2DiscountRate))
    dblDiscountAmount = CLng(dblMarkedPrice * dblDiscountRate * 100) / 100
    dblPriceAfterDicsount = dblMarkedPrice - dblDiscountAmount
    intQuantity = CInt(Nz(Me.Item2Quantity))
    dblSubTotal = dblPriceAfterDicsount * intQuantity
    
    Me.Item2PriceAfterDiscount = dblPriceAfterDicsount
    Me.Item2SubTotal = dblSubTotal
    EvaluateOrder
End Sub
  46. Return to Microsoft Access and, on the form, click the third text box under Marked Price
  47. In the Events tab of the Properties window, double-click On Lost Focus and click its ellipsis button to implement the event as follows:
      
    Private Sub Item3MarkedPrice_LostFocus()
    Dim dblMarkedPrice As Double
    Dim dblDiscountRate As Double
    Dim dblDiscountAmount As Double
    Dim dblPriceAfterDicsount As Double
    Dim intQuantity As Integer
    Dim dblSubTotal As Double
    
    dblMarkedPrice = CDbl(Nz(Me.Item3MarkedPrice))
    dblDiscountRate = CDbl(Nz(Me.Item3DiscountRate))
    dblDiscountAmount = CLng(dblMarkedPrice * dblDiscountRate * 100) / 100
    dblPriceAfterDicsount = dblMarkedPrice - dblDiscountAmount
    intQuantity = CInt(Nz(Me.Item3Quantity))
    dblSubTotal = dblPriceAfterDicsount * intQuantity
    
    Me.Item3PriceAfterDiscount = dblPriceAfterDicsount
    Me.Item3SubTotal = dblSubTotal
    EvaluateOrder
End Sub
  48. Return to Microsoft Access and, on the form, click the fourth text box under Marked Price
  49. In the Events tab of the Properties window, double-click On Lost Focus and click its ellipsis button to implement the event as follows:
      
    Private Sub Item4MarkedPrice_LostFocus()
    Dim dblMarkedPrice As Double
    Dim dblDiscountRate As Double
    Dim dblDiscountAmount As Double
    Dim dblPriceAfterDicsount As Double
    Dim intQuantity As Integer
    Dim dblSubTotal As Double
    
    dblMarkedPrice = CDbl(Nz(Me.Item4MarkedPrice))
    dblDiscountRate = CDbl(Nz(Me.Item4DiscountRate))
    dblDiscountAmount = CLng(dblMarkedPrice * dblDiscountRate * 100) / 100
    dblPriceAfterDicsount = dblMarkedPrice - dblDiscountAmount
    intQuantity = CInt(Nz(Me.Item4Quantity))
    dblSubTotal = dblPriceAfterDicsount * intQuantity
    
    Me.Item4PriceAfterDiscount = dblPriceAfterDicsount
    Me.Item4SubTotal = dblSubTotal
    EvaluateOrder
End Sub
  50. Return to Microsoft Access and, on the form, click the fifth text box under Marked Price
  51. In the Events tab of the Properties window, double-click On Lost Focus and click its ellipsis button to implement the event as follows:
      
    Private Sub Item5MarkedPrice_LostFocus()
    Dim dblMarkedPrice As Double
    Dim dblDiscountRate As Double
    Dim dblDiscountAmount As Double
    Dim dblPriceAfterDicsount As Double
    Dim intQuantity As Integer
    Dim dblSubTotal As Double
    
    dblMarkedPrice = CDbl(Nz(Me.Item5MarkedPrice))
    dblDiscountRate = CDbl(Nz(Me.Item5DiscountRate))
    dblDiscountAmount = CLng(dblMarkedPrice * dblDiscountRate * 100) / 100
    dblPriceAfterDicsount = dblMarkedPrice - dblDiscountAmount
    intQuantity = CInt(Nz(Me.Item5Quantity))
    dblSubTotal = dblPriceAfterDicsount * intQuantity
    
    Me.Item5PriceAfterDiscount = dblPriceAfterDicsount
    Me.Item5SubTotal = dblSubTotal
    EvaluateOrder
End Sub
  52. Return to the form and save it
  53. On the form, click the first text box under Discount Rate
  54. In the Events tab of the Properties window, double-click On Lost Focus and click its ellipsis button to implement the event as follows:
      
    Private Sub Item1DiscountRate_LostFocus()
    Item1MarkedPrice_LostFocus
End Sub
  55. Return to Microsoft Access and, on the form, click the second text box under Quantity
  56. In the Events tab of the Properties window, double-click On Lost Focus and click its ellipsis button to implement the event as follows:
      
    Private Sub Item2DiscountRate_LostFocus()
    Item2MarkedPrice_LostFocus
End Sub
  57. Return to Microsoft Access and, on the form, click the third text box under Quantity
  58. In the Events tab of the Properties window, double-click On Lost Focus and click its ellipsis button to implement the event as follows:
      
    Private Sub Item3DiscountRate_LostFocus()
    Item3MarkedPrice_LostFocus
End Sub
  59. Return to Microsoft Access and, on the form, click the fourth text box under Quantity
  60. In the Events tab of the Properties window, double-click On Lost Focus and click its ellipsis button to implement the event as follows:
      
    Private Sub Item4DiscountRate_LostFocus()
    Item4MarkedPrice_LostFocus
End Sub
  61. Return to Microsoft Access and, on the form, click the fifth text box under Quantity
  62. In the Events tab of the Properties window, double-click On Lost Focus and click its ellipsis button to implement the event as follows:
      
    Private Sub Item5DiscountRate_LostFocus()
    Item5MarkedPrice_LostFocus
End Sub
  63. Return to the form and save it
  64. On the form, click the first text box under Qty
  65. In the Events tab of the Properties window, double-click On Lost Focus and click its ellipsis button to implement the event as follows:
      
    Private Sub Item1Quantity_LostFocus()
    Item1MarkedPrice_LostFocus
End Sub
  66. Return to Microsoft Access and, on the form, click the second text box under Qty
  67. In the Events tab of the Properties window, double-click On Lost Focus and click its ellipsis button to implement the event as follows:
      
    Private Sub Item2Quantity_LostFocus()
    Item2MarkedPrice_LostFocus
End Sub
  68. Return to Microsoft Access and, on the form, click the third text box under Qty
  69. In the Events tab of the Properties window, double-click On Lost Focus and click its ellipsis button to implement the event as follows:
      
    Private Sub Item3Quantity_LostFocus()
    Item3MarkedPrice_LostFocus
End Sub
  70. Return to Microsoft Access and, on the form, click the fourth text box under Qty
  71. In the Events tab of the Properties window, double-click On Lost Focus and click its ellipsis button to implement the event as follows:
      
    Private Sub Item4Quantity_LostFocus()
    Item4MarkedPrice_LostFocus
End Sub
  72. Return to Microsoft Access and, on the form, click the fifth text box under Qty
  73. In the Events tab of the Properties window, double-click On Lost Focus and click its ellipsis button to implement the event as follows:
      
    Private Sub Item5Quantity_LostFocus()
    Item5MarkedPrice_LostFocus
End Sub
  74. Return to the form and save it
  75. In the Database window, click Forms.
    To create a new form, in the Forms section of the Database window, double-click Create Form in Design View
  76. Save the form as NewInventoryItem
  77. Design the form as follows:
     
    Control Caption Name Additional Properties
    Text Box Date Entered: txtDateEntered Format: Medium Date
    Input Mask: 99\->L<LL\-00;0;_
    Text Box Item Number: txtItemNumber  
    Text Box Item Name: txtItemName  
    Text Box Item Size: txtItemSize  
    Text Box Original Price: txtOriginalPrice Format: Fixed
    Text Box Units In Stock: txtUnitsInStock Format: General Number
    Decimal Places: 0
  78. Save the form
  79. On the Toolbox, make sure the Control Wizard button is down.
    Click Combo Box and click the section between the txtItemName and the txtItemSize text boxes
  80. On the first page of the wizard, accept the first radio button and click Next
  81. On the second page, click ItemCategories and click Next
  82. In the Available Fields list of the third page, double-click ItemCategory and click Next
  83. Click Next and click Finish
  84. Change the caption of the new label to Item Category:
  85. Change the name of the combo box to cboItemCategoryID
     
  86. Right-click anywhere under the Detail bar and click Tab Order
  87. Click Auto Order and click OK
  88. Save the form and switch it to Form View to preview
  89. Switch it back to Design View
  90. On the Toolbox, click the Command Button and click the left section of the Form Footer bar. If/when the wizard starts, click Cancel
  91. Set the button Caption to Submit and its Name to cmdSubmit
  92. On the Toolbox, click the Command Button and click the left section of the Form Footer bar. If/when the wizard starts, click Cancel
  93. Set the button Caption to Reset and its Name to cmdReset
  94. On the Toolbox, click the Command Button and click the left section of the Form Footer bar
  95. Follow the wizard to create a button used to close the form. Set the caption of the button to Close and set its name to cmdClose
  96. Arrange the positions of the buttons as you see fit. Here is an example:
     
  97. Save and close the form
  98. In the Forms section of the Database window, right click NewInventoryItem and click Save As...
  99. Type InventoryItemUpdate as the name of the new forms and click OK
  100. Add a Check Box to the form and set its Name to chkUpdateSales
  101. Change the caption of the accompanying label to Update Sales Item Also
  102. Change the value of the Submit button as follows:
    Caption: Submit Change
    Name: cmdSubmitChange
  103. Save the form
     
  104. Save the form

Record Location

As mentioned in Lesson 13 on record maintenance, editing a record consists of changing the value of one or more columns. As done visually using a table, a query or a form, on a record set, editing a record is done in various steps. First, you must locate the record. If you know exactly the index of the record that contains the value you want to edit, you can call the Move() method to jump to it. From the above table, imagine that you have finally found out the name of the director of the video titled Congo. By looking at the table, we see that it is the 7th video as indicated by the text box on the status bar. Before editing the value, you can first jump to the record number 6. Here is an example that use Microsoft Access Object library:

Private Sub cmdMovePosition_Click()
   Dim dbVideoCollection As Object
   Dim rstVideos As Object

   Set dbVideoCollection = CurrentDb
   Set rstVideos = dbVideoCollection.OpenRecordset("Videos")

   rstVideos.Move 6
End Sub

Although this uses the Microsoft Access Object Library, you can also apply it to DAO by simply changing the names of the objects to the appropriate ones:

Private Sub cmdMovePosition_Click()
   Dim dbVideoCollection As DAO.Database
   Dim rstVideos As DAO.Recordset

   Set dbVideoCollection = CurrentDb
   Set rstVideos = dbVideoCollection.OpenRecordset("Videos")

   rstVideos.Move 6
End Sub

We also saw that you could call one of the other Move-oriented methods (MoveFirst(), MovePrevious(), MoveNext(), or MoveLast()). Once you get to a record, you can then perform the necessary operation. For example, you can retrieve the values held by that record.

 

Practical Learning Practical Learning: Locating a Record
  1. On the InventoryItemUpdate form in Design View, click the txtItemNumber text box to select it
  2. In the Properties window, click Events and double-click On Lost Focus
  3. Click its ellipsis button and implement the event as follows:
      
    Private Sub txtItemNumber_LostFocus()
    Dim rstInventoryItems As ADODB.Recordset
    Dim blnFound As Boolean
    ' This flag will allow us to know whether the item number was found
    Dim fldItem As ADODB.Field
    
    ' Since we are only starting, we assume that no item number has been found
    blnFound = False
    
    ' If there is no value in the Item Number text box, don't do nothing
    If Me.txtItemNumber = "" Then Exit Sub
    
    Set rstInventoryItems = New ADODB.Recordset
    rstInventoryItems.Open "SELECT * FROM InventoryItems WHERE ItemNumber = '" & txtItemNumber & "'", _
                           CurrentProject.Connection, _
                           adOpenStatic, adLockReadOnly, adCmdText
  
    With rstInventoryItems
        ' Check each record
        While Not .EOF
            ' Check the name of the column
            For Each fldItem In .Fields
                ' If the current column is ItemNumber
                If fldItem.Name = "ItemNumber" Then
                    ' Check its value
                    ' If the current column holds the item number that the user entered
                    If fldItem.Value = txtItemNumber Then
                        ' ... then get the record and display its values in the controls
                        Me.txtItemName = rstInventoryItems.Fields("ItemName")
                        Me.cboItemCategoryID = rstInventoryItems.Fields("ItemCategoryID")
                        Me.txtItemSize = rstInventoryItems.Fields("ItemSize")
                        Me.txtOriginalPrice = rstInventoryItems.Fields("OriginalPrice")
                        Me.txtUnitsInStock = rstInventoryItems.Fields("UnitsInStock")
                        ' Set the found flag to true (we will use it later on)
                        blnFound = True
                    End If
                End If
            Next
            ' In case you didn't find it, move to the next record
            .MoveNext
        Wend
    End With
    
    ' If the item number was not found, ...
    If blnFound = False Then
        ' ... let the user know, ...
        ' MsgBox "The item number you entered is not in our list of products"
        ' ... and reset the form
        cmdReset_Click
        Me.txtItemNumber.SetFocus
    End If
                        
    rstInventoryItems.Close
    Set rstInventoryItems = Nothing
End Sub
  4. Return to the form and save it
 
Previous Copyright © Yevol, 2007 Next