List-Based Windows Controls: The Combo Box

Introduction to Combo Boxes

Overview

A combo box is a Windows control made of two parts: a text portion and a list. A text part is used to display a selection made from a list of items. To support combo boxes, Microsoft Access provides a class named ComboBox.

Practical Learning: Introducing List-Based Controls

1. Start Microsoft Access
2. Click Blank Desktop Database
3. Set the file name to Fun Department Store2
4. Click Create
5. On the Ribbon, click File and click Options
6. In the left list of the Access Options dialog box, click Current Database
7. In the main list, click Overlapping Windows
8. Click OK on the dialog box
9. Click OK on the message box
10. On the Ribbon, click File and click Close
11. In the list of files, click Fun Department Store2
12. On the Ribbon, click Create and, in the Forms section, click Form Design
13. In the Property Sheet, click the All tab and change the following characteristics:
    Caption: Fun Department Store
    Auto Center: Yes
    Record Selectors: No
    Navigation Buttons: No
    Min Max Buttons: Min Enabled
14. Set the Back Color of the Detail section to
    Red: 240
    Green: 235
    Blue: 225
15. Save the form as Fun Department Store
16. In the Controls section of the Ribbon, click the Button
17. Click the form.
    If a wizard starts, click Cancel
18. While the button is still selected on the form, in the Property Sheet, click the Format tab, click Picture, and click its ellipsis button
19. In the Available Pictures of the Picture Builder dialog box, scroll down and click MS Access Table:

    

20. Click OK
21. Complete the design of the form as follows:

    

    Control		Name	Caption	Other
    Label			Tables	Font Name: Bodoni MT Black (or Garamond or Times New Roman) Font Size: 20 Font Color: Black, Text 1
    Label			Forms	Font Name: Bodoni MT Black (or Garamond or Times New Roman) Font Size: 20 Font Color: Black, Text 1
    Line
    Button		cmdStoreItems	Store Items...
    Button		cmdNewStoreItem	New Store Item...	Hover Color: Accent 1, Lighter 80% Pressed Color: #1F497D Hover Color: Accent 5, Darker 50% Pressed Fore Color: Background 1 (white)
    Button		cmdShoppingSessions	Shopping Sessions...
    Button		cmdNewShoppingSession	New Shopping Session...	Hover Color: Accent 1, Lighter 80% Pressed Color: #1F497D Hover Color: Accent 5, Darker 50% Pressed Fore Color: Background 1 (white)
    Button		cmdEmployees	Employees...
    Button		cmdNewEmployee	New Employee...	Hover Color: Accent 1, Lighter 80% Pressed Color: #1F497D Hover Color: Accent 5, Darker 50% Pressed Fore Color: Background 1 (white)
    Button		cmdClose	Close

22. Save the Fun Department Store form
23. On the form, below the Tables label, right-click the Store Items button and click Build Event...
24. In the Choose Builder dialog box, click Code Builder and click OK
25. Implement the event as follows:

    Private Sub cmdStoreItems_Click()
        DoCmd.RunSQL "CREATE TABLE Manufacturers" & _
                     "(" & _
                     "  ManufacturerID COUNTER, " & _
                     "  Manufacturer   TEXT(40), " & _
                     "  CONSTRAINT PK_Manufacturers PRIMARY KEY(ManufacturerID)" & _
                     ");"
        DoCmd.RunSQL "CREATE TABLE Categories" & _
                     "(" & _
                     "  CategoryID AUTOINCREMENT, " & _
                     "  Category   STRING(40), " & _
                     "  CONSTRAINT PK_Categories PRIMARY KEY(CategoryID)" & _
                     ");"
        DoCmd.RunSQL "CREATE TABLE SubCategories" & _
                     "(" & _
                     "  SubCategoryID Counter(1, 1), " & _
                     "  SubCategory   CHAR(40), " & _
                     "  CONSTRAINT PK_SubCategories PRIMARY KEY(SubCategoryID)" & _
                     ");"
                     
        DoCmd.RunSQL "CREATE TABLE StoreItems" & _
                     "(" & _
                     "  StoreItemID    AUTOINCREMENT(1), " & _
                     "  ItemNumber     Text(10), " & _
                     "  ManufacturerID Long, " & _
                     "  CategoryID     Long, " & _
                     "  SubCategoryID  Long" & _
                     "  ItemName       text(120) NOT NULL, " & _
                     "  ItemSize       string(25), " & _
                     "  UnitPrice      Double not null, " & _
                     "  DiscountRate   Number, " & _
                     "  CONSTRAINT     FK_ItemsManufacturers FOREIGN KEY(ManufacturerID) " & _
                     "      REFERENCES Manufacturers(ManufacturerID)," & _
                     "  CONSTRAINT     FK_ItemsCategories FOREIGN KEY(CategoryID) " & _
                     "      REFERENCES Categories(CategoryID)," & _
                     "  CONSTRAINT     FK_ItemsSubCategories FOREIGN KEY(SubCategoryID) " & _
                     "      REFERENCES SubCategories(SubCategoryID)," & _
                     "  CONSTRAINT     PK_StoreItems PRIMARY KEY(ItemNumber)" & _
                     ");"
    
        cmdStoreItems.Enabled = False
    End Sub

26. In the Object combo box, select cmdEmployees
27. Implement the event as follows:

    Private Sub cmdEmployees_Click()
        DoCmd.RunSQL "CREATE TABLE Employees" & _
                     "(" & _
                     "  EmployeeNumber Text(10), " & _
                     "  FirstName      String(25), " & _
                     "  LastName       Char(25) not null, " & _
                     "  Title          text(100), " & _
                     "  CONSTRAINT     PK_Employees PRIMARY KEY(EmployeeNumber)" & _
                     ");"
                     
        cmdEmployees.Enabled = False
    End Sub

28. In the Object combo box, select cmdShopppingSessions
29. Implement the event as follows:

    Private Sub cmdShopppingSessions_Click()                 
        DoCmd.RunSQL "CREATE TABLE SoldItems" & _
                     "(" & _
                     "  SoldItemID     AUTOINCREMENT(1), " & _
                     "  ReceiptNumber  Long, " & _
                     "  ItemNumber     Text(10), " & _
                     "  ItemName       text(100) NOT NULL, " & _
                     "  ItemSize       string(25), " & _
                     "  UnitPrice      Double not null, " & _
                     "  DiscountRate   Number, " & _
                     "  DiscountAmount Number, " & _
                     "  SalePrice      Number, " & _
                     "  CONSTRAINT     FK_StoreSales FOREIGN KEY(ItemNumber) " & _
                     "      REFERENCES StoreItems(ItemNumber)," & _
                     "  CONSTRAINT     PK_SellingItems PRIMARY KEY(SellingItemID)" & _
                     ");"
                     
        DoCmd.RunSQL "CREATE TABLE StoreSales" & _
                     "(" & _
                     "  ReceiptNumber  COUNTER(100001, 1) " & _
                     "  EmployeeNumber Text(10), " & _
                     "  SaleDate       Char(40), " & _
                     "  SaleTime       VarChar(40), " & _
                     "  OrderTotal     Double NOT NULL, " & _
                     "  AmountTendered Double, " & _
                     "  Change         Double, " & _
                     "  CONSTRAINT     FK_SalesClerk FOREIGN KEY(EmployeeNumber) " & _
                     "      REFERENCES Employees(EmployeeNumber)," & _
                     "  CONSTRAINT     PK_StoreSales PRIMARY KEY(ReceiptNumber)" & _
                     ");"
                     
        cmdShopppingSessions.Enabled = False
    End Sub

30. Return to Microsoft Access and switch the form to Form View
31. On the form, below the Tables label, click Employees, Store Items, and Shopping Sessions
32. Save and close the Department Store form
33. On the Ribbon, click Database Tools and click Relationships
34. Relate the tables

    

35. To close the Relationships window, click its Close button
36. On the Ribbon, click Create and click Form Design
37. In the Property Sheet, click the All tab and change the following characteristics:
    Record Source: Employees
    Caption: Fun Department Store - Employees
    Auto Center: Yes
    Dividing Lines: Yes
    Min Max Buttons: Min Enabled
38. Right-click the body of the form and click Form Header/Footer
39. Set the Back Color of the Form Header section to Red: 225, Green: 215, Blue: 195
40. Set the Back Color of the Detail section to Red: 240, Green: 235, Blue: 225
41. Set the Back Color of the Form Footer section to Accent 2, Darker 50%
42. Save the form as Employees
43. In the Design tab of the Ribbon, in the Tools section, click Add Existing Fields
44. In the Field List, click EmployeeNumber if necessary.
    Press and hold Shift
45. Click Title and release Shift
46. Drag the selection to the form
47. Complete the design of the form as follows:

    

48. Right-click the Close the button and click Build Event...
49. In the Choose Builder dialog box, click Code Builder and click OK
50. Implement the event as follows:

    Private Sub cmdClose_Click()
        DoCmd.Close
    End Sub

51. Save and close the form
52. On the Ribbon, click Create and, in the Forms section, click Form Design
53. Using the Properties window, set the following characteristics:
    Record Source: Manufacturers
    Caption: Fun Department Store - Manufacturers
    Auto Center: Yes
    Dividing Lines: Yes
    Min Max Buttons: Min Enabled
54. Right-click the body of the form and click Form Header/Footer
55. Set the Back Color of the Form Header section to Red: 225, Green: 215, Blue: 195
56. Set the Back Color of the Detail section to Red: 240, Green: 235, Blue: 225
57. Set the Back Color of the Form Footer section to Accent 2, Darker 50%
58. Save the form as Manufacturers
59. Design it as follows:

    

60. Save and close the Manufacturers form
61. In the Navigation Pane, right-click the Manufacturers form and click Copy
62. Right-click an unoccupied area of the Navigation Pane and click Paste
63. Set the name to Categories
64. In the Navigation Pane, right-click the Categories form and click Form Design
65. Using the Properties window, change the Record Source to Categories
66. Design it as follows:

    

67. Save and close the Categories form
68. In the Navigation Pane, right-click the Categories form and click Copy
69. Right-click an unoccupied area of the Navigation Pane and click Paste
70. Set the name to SubCategories
71. In the Navigation Pane, right-click the SubCategories form and click Form Design
72. Using the Properties window, change the Record Source to SubCategories
73. Design it as follows:

    

74. Save and close the Sub-Categories form
75. On the Ribbon, click Create and click Form Design
76. Double-click the Properties button of the form
77. In the Property Sheet, click the All tab, click Record Source, and click its ellipsis button
78. In the Show Table dialog box, double-click Manufacturers, StoreItems, Categories, and SubCategories
79. In the lists of items, double-click ItemNumber, Manufacturer, Category, SubCategory, ItemName, and UnitPrice

    

80. Close the query window
81. When asked whether you want to save, clickYes
82. In the Property Sheet, click the All tab and change the following characteristics:
    Caption: Fun Department Store - Inventory Analysis
    Auto Center: Yes
    Navigation Buttons: No
    Dividing Lines: Yes
    Min Max Buttons: Min Enabled
83. Right-click the body of the form and click Form Header/Footer
84. Save the form as Inventory Analysis
85. Design the form as follows:

    

86. Save and close the Inventory Analysis form
87. On the Ribbon, click Create and click Form Design
88. Double-click the Properties button of the form
89. In the Property Sheet, click the All tab and change the following characteristics:
    Caption: Fun Department Store - New Store Item
    Auto Center: Yes
    Navigation Buttons: No
    Dividing Lines: Yes
    Min Max Buttons: Min Enabled
90. Right-click the body of the form and click Form Header/Footer
91. Set the Back Color of the Form Header section to Red: 225, Green: 215, Blue: 195
92. Set the Back Color of the Detail section to Red: 240, Green: 235, Blue: 225
93. Set the Back Color of the Form Footer section to Accent 2, Darker 50%
94. Save the form as NewStoreItem
95. Complete the design of the form as follows:

    

    Control		Caption	Name
    Label		Fun Department Store
    Label		New Store Item
    Text Box		Item Number:	txtItemNumber

96. Save the form

Creating a Combo Box

There are various ways you can create a combo box in Microsoft Access. The classic way is that, after displaying a form or report in Design View, in the Controls section of the Ribbon, click the Combo Box button and click the form or report. When you do this, if the Control Wizards button is down, a wizard would start. If you want to create a list manually, you can click Cancel. Otherwise, you can continue with the wizard.

To programmatically create a combo box, call the CreateControl() function and pass the ControlType as acComboBox. The first argument is the name of the form or report on which the label will be positioned. Here is an example:

Private Sub cmdCreateControl_Click()
    Dim ctlGenders As Control
    
    Set ctlGenders = CreateControl("Exercise", _
                                   AcControlType.acComboBox)

    Set ctlGenders = Nothing
End Sub

The third argument is the section of the form or report where the control will be positioned. You can pass the fourth argument as the name of the form or report on which the label will be positioned. That is, the first and the fourth argument can be the same.

Practical Learning: Introducing Combo Boxes

1. The New Store Item should still be displaying in the Design View.
   In the Controls section of the Ribbon, click the Combo Box and click the Detail section of the form
2. In the first page of the wizard, make sure the first radio button is selected and click Next
3. In the second page of the wizard, in the list of tables, click Table: Manufacturers

   

4. Click Next
5. In the third page of the wizard, in the Available Fields list, double-click Manufacturer

   

6. Click Next
7. In the 4th page of the wizard, click Next
8. In the 5th page of the wizard, click Next
9. In the 6th page of the wizard, click Finish
10. In the Controls section of the Ribbon, click the Combo Box and click the Detail section of the form
11. In the first page of the wizard, make sure the first radio button is selected and click Next
12. In the second page of the wizard, in the list of tables, click Table: Categories and click Next
13. In the third page of the wizard, in the Available Fields list, double-click Category and click Next
14. In the 4th page of the wizard, click Next
15. In the 5th page of the wizard, click Next
16. In the 6th page of the wizard, click Finish
17. In the Controls section of the Ribbon, click the Combo Box and click the Detail section of the form
18. In the first page of the wizard, make sure the first radio button is selected and click Next
19. In the second page of the wizard, in the list of tables, click Table: SubCategories and click Next
20. In the third page of the wizard, in the Available Fields list, double-click SubCategory and click Next
21. In the 4th page of the wizard, click Next
22. In the 5th page of the wizard, click Next
23. In the 6th page of the wizard, click Finish
24. Complete the design of the form as follows:

    

    Control		Caption	Name
    Label		Fun Department Store
    Label		New Store Item
    Text Box		Item Number:	txtItemNumber
    Combo Box		Manufacturer:	cbxManufacturers
    Combo Box		Category:	cbxCategories
    Combo Box		Sub-Category:	cbxSubCategories
    Text Box		Item Name:	txtItemName
    Text Box		Item Size:	txtItemSize
    Text Box		Unit Price:	txtUnitPrice
    Text Box		Discount Rate:	txtDiscountRate
    Button		Submit	cmdSubmit
    Button		Close	cmdClose

25. Right-click the Close the button and click Build Event...
26. In the Choose Builder dialog box, click Code Builder and click OK
27. Implement the event as follows:

    Private Sub cmdClose_Click()
        DoCmd.Close
    End Sub

28. Save and close the form
29. On the Ribbon, click File and click Open
30. In the list of files, click Kolo Bank1 from Lesson 42
31. In the Navigation Pane, right-click the New Deposit form and click Design View
32. Click the Currency Type text box and press Delete
33. In the Controls section of the Ribbon, click the Combo Box and click the form
34. In the first page of the wizard, make sure the first radio button is selected and click Next
35. In the list of the second page of the wizard, click Table: CurrenciesTypes and click Next
36. In the Available Fields list of the third page of the wizard, double-click CurrencyType and click Next
37. In the 4th page of the wizard, click Next
38. In the 5th page of the wizard, click Next
39. In the 6th page of the wizard, click Finish
40. Position the combo box and its accompanying label as follows:

    

    Control		Caption	Name	Other Properties
    Label		Kolo Bank		Font Color: Yellow
    Line				Border Color: #FFC20E
    Label		Bank Account Deposit		Font Color: White
    Label		Deposit Performed By		Back Color: #727272
    Text Box		Employee #:	txtEmployeeNumber
    Text Box			txtEmployeeName
    Line				Border Width: 2 pt
    Text Box		Deposit Date:	txtDepositDate	Format: Short Date
    Text Box		Deposit Time:	txtDepositTime	Format: Long Time
    Text Box		Location Code:	txtLocationCode
    Text Box			txtLocationName
    Label			Performed For	Back Color: #727272
    Text Box		Account #:	txtAccountNumber
    Text Box			txtCustomerName
    Combo Box		Currency Type:	cbxCurrenciesTypes
    Text Box		Previous Balance:	txtPreviousBalance
    Text Box		Amount Deposited:	txtAmountDeposited
    Text Box		New Balance:	txtNewBalance
    Text Box		Notes:	txtNotes	Special Effect: Shadowed Scroll Bars: Vertical
    Button		Submit	btnSubmit
    Button		Close	cmdClose

41. Close the form
42. When asked whether you want to save, click Yes
43. In the Navigation Pane, right-click NewDeposit and click Copy
44. Right-click an unoccupied area of the Navigation Pane and click Paste
45. Type New Withdrawal as the name of the new form and click OK
46. Right-click an unoccupied area of the Navigation Pane and click Paste
47. Type New Charge as the name of the new form and click OK
48. In the Navigation Pane, right-click New Withdrawal and click Design View
49. Change (only) the following characteristics:
    

    

    Control		Caption	Name
    Label		Bank Account Withdrawal
    Label		Withdrawal Performed By
    Text Box		Withdrawal Date:	txtWithdrawalDate
    Text Box		Withdrawal Time:	txtWithdrawalTime
    Text Box		Amount Withdrawn:	txtAmountWithdrawn

50. Close the form
51. When asked whether you want to save, click Yes
52. In the Navigation Pane, right-click New Charge and click Design View
53. In the Controls section of the Ribbon, click the Combo Box and click the form
54. In the first page of the wizard, make sure the first radio button is selected and click Next
55. In the second page of the wizard, click Table: ChargesReasons and click Next
56. In the Available Fields list of the third page of the wizard, double-click ChargeReason and click Next
57. In the 4th page of the wizard, click Next
58. In the 5th page of the wizard, click Next
59. In the 6th page of the wizard, click Finish
60. Change (only) the following characteristics:
    

    

    Control		Caption	Name
    Label		Bank Account New Charge
    Label		Charge Performed By
    Text Box		Charge Date:	txtChargeDate
    Text Box		Charge Time:	txtChargeTime
    Combo Box		Charge Reason	cbxChargesReasons
    Text Box		Amount Charged:	txtAmountCharged

61. Close the form
62. When asked whether you want to save, click Yes
63. In the Navigation Pane, right-click Account Deposit and click Design View
64. In the Form Footer section, right-click the Submit button and click Build Event...
65. In the Choose Builder dialog box, double-click Code Builder
66. Implement the event as follows:
    

    Private Sub cmdSubmit_Click()
    On Error GoTo cmdSubmit_ClickError
    
        Dim fldCustomer As Field
        Dim dbKoloBank As Database
        Dim fldTransaction As Field
        Dim rstCustomers As Recordset
        Dim strAccountStatus As String
        Dim rstTransactions As Recordset
        Dim BalanceAfterDeposit As Double
        Dim BalanceBeforeDeposit As Double
        Dim rstAccountsHistories As Recordset
    
        ' Get a reference to the current database
        Set dbKoloBank = CurrentDb
        
        ' We will need to find out whether the account is suspended and update it.
        strAccountStatus = ""
        ' First, get the records of customers
        Set rstCustomers = dbKoloBank.OpenRecordset("Customers", _
                                                    RecordsetTypeEnum.dbOpenTable, _
                                                    RecordsetOptionEnum.dbReadOnly, _
                                                    LockTypeEnum.dbPessimistic)
        
        If IsNull(txtEmployeeNumber) Then
            MsgBox "Please enter a valid employee number to identity " & _
                   "the employee who is performing (or performed) the transaction.", _
                   vbOKOnly Or vbInformation, "Kolo Bank"
            Exit Sub
        End If
                  
        If IsNull(txtDepositDate) Then
            MsgBox "Please specify the date the transaction is occurring (or occurred).", _
                   vbOKOnly Or vbInformation, "Kolo Bank"
            Exit Sub
        End If
           
        If IsNull(txtLocationCode) Then
            MsgBox "Please enter the location code where the transaction is taking (or took) place.", _
                   vbOKOnly Or vbInformation, "Kolo Bank"
            Exit Sub
        End If
        
        If IsNull(txtAccountNumber) Then
            MsgBox "Please enter the valid account number of the bank account where money is (or was) deposited.", _
                   vbOKOnly Or vbInformation, "Kolo Bank"
            Exit Sub
        End If
        
        If IsNull(cbxCurrenciesTypes) Then
            MsgBox "Select the type of currency (cash, check, etc).", _
                   vbOKOnly Or vbInformation, "Kolo Bank"
            Exit Sub
        End If
        
        If IsNull(txtAmountDeposited) Then
            MsgBox "Type the amount of money that is (or was) deposited.", _
                   vbOKOnly Or vbInformation, "Kolo Bank"
            Exit Sub
        End If
    
        ' Before doing anything on the Customers table, get to the first record
        rstCustomers.MoveFirst
        
        ' Check all Customers records, ...
        With rstCustomers
            ' ... from beginning to end
            Do While Not .EOF
                ' When you get to a record, ...
                For Each fldCustomer In .Fields
                    ' ... start with the customer's bank account number
                    ' If the account number is the same on the form, ...
                    If (fldCustomer.Name = "AccountNumber") And (fldCustomer.Value = txtAccountNumber) Then
                        ' ... get the status of that account and reserve it
                        strAccountStatus = .Fields("AccountStatus").Value
                        ' Since you have found the account number, stop looking for it
                        Exit For
                    End If
                ' If you have not yet found the account number, keep looking
                Next
                .MoveNext
            Loop
        End With
        
        ' First get a reference to the Transactions table
        Set rstTransactions = dbKoloBank.OpenRecordset("Transactions", _
                                                       RecordsetTypeEnum.dbOpenTable, _
                                                       RecordsetOptionEnum.dbAppendOnly, _
                                                       LockTypeEnum.dbPessimistic)
        
        ' If the deposit is ready, create its record in the Transactions table
        rstTransactions.AddNew
        rstTransactions("EmployeeNumber").Value = txtEmployeeNumber
        rstTransactions("LocationCode").Value = txtLocationCode
        rstTransactions("TransactionDate").Value = txtDepositDate
        rstTransactions("TransactionTime").Value = txtDepositTime
        rstTransactions("AccountNumber").Value = txtAccountNumber
        rstTransactions("TransactionType").Value = "Deposit"
        rstTransactions("CurrencyType").Value = cbxCurrenciesTypes
        rstTransactions("DepositAmount").Value = CDbl(txtAmountDeposited)
        rstTransactions("Balance").Value = txtCurrentBalance
        rstTransactions("Notes").Value = txtNotes
        rstTransactions.Update
        
        ' Let the customer know that the deposit was made.
        MsgBox "The deposit has been made.", _
               vbOKOnly Or vbInformation, "Kolo Bank"
        
        ' Before scanning the list of transactions, get back to the first record
        rstTransactions.MoveFirst
        
        ' Scan the table of transactions to look for the current account number
        With rstTransactions
            Do While Not .EOF
                For Each fldTransaction In .Fields
                    ' If you find the account number of the form, ...
                    If (fldTransaction.Name = "AccountNumber") And (fldTransaction.Value = txtAccountNumber) Then
                        ' ... get the account's current balance
                        BalanceAfterDeposit = CDbl(.Fields("Balance").Value)
                        
                        ' Since you found the account number, stop looking for it.
                        Exit For
                    End If
                Next
                .MoveNext
            Loop
        End With
            
        ' We need to find out if the account is currently suspended.
        ' Before going through the customers' records, get on the first record
        rstCustomers.MoveFirst
        
        ' If the current bank account is suspended, did the customer bring the balance to at least 0?
        If (strAccountStatus = "Suspended") And (BalanceAfterDeposit >= 0#) Then
            ' If so, scan the Customers table to look for our account number
            With rstCustomers
                Do While Not .EOF
                    For Each fldCustomer In .Fields
                        ' If you find the account number of the form, ...
                        If (fldCustomer.Name = "AccountNumber") And (fldCustomer.Value = txtAccountNumber) Then
                            ' ... change its status to Active
                            .Edit
                            .Fields("AccountStatus").Value = "Active"
                            .Update
                            
                            ' Announce the good news to the customer
                            MsgBox "The account has been re-activated.", _
                                    vbOKOnly Or vbInformation, "Kolo Bank"
                            Exit For
                        End If
                    Next
                    .MoveNext
                Loop
            End With
            
            ' Since the account's status has changed, create a new record in the history list
            Set rstAccountsHistories = dbKoloBank.OpenRecordset("AccountsHistories", _
                                                                RecordsetTypeEnum.dbOpenTable, _
                                                                RecordsetOptionEnum.dbDenyRead, _
                                                                LockTypeEnum.dbPessimistic)
        
            rstAccountsHistories.AddNew
            rstAccountsHistories("AccountNumber").Value = txtAccountNumber
            rstAccountsHistories("AccountStatus").Value = "Active"
            rstAccountsHistories("DateChanged").Value = CDate(txtDepositDate)
            rstAccountsHistories("TimeChanged").Value = CDate(txtDepositTime)
            rstAccountsHistories("ShortNote").Value = "The account has been re-actived."
            rstAccountsHistories.Update
        End If
        
        Set rstAccountsHistories = Nothing
        Set rstTransactions = Nothing
        Set rstCustomers = Nothing
        Set dbKoloBank = Nothing
        
        cmdClose_Click
        
        Exit Sub
        
    cmdSubmit_ClickError:
        MsgBox "The withdrawal was not processed because of an error." & vbCrLf & _
               "Error #:     " & Err.Number & vbCrLf & _
               "Description: " & Err.Description, _
               vbOKOnly Or vbInformation, "Kolo Bank"
        Resume Next
    End Sub

67. In the Object combo box, select cmdClose and implement the event as follows:

    Private Sub cmdClose_Click()
        DoCmd.Close
    End Sub

68. In the Project window, double-click Form_Money Withdrawal
69. In the Object combo box, select cmdSubmit and implement the event as follows:

    Private Sub cmdSubmit_Click()
    On Error GoTo cmdSubmit_ClickError
    
        Dim fldCustomer As Field
        Dim dbKoloBank As Database
        Dim fldTransaction As Field
        Dim rstCustomers As Recordset
        Dim amountWithdrawn As Double
        Dim strAccountStatus As String
        Dim bAccountSuspended As Boolean
        Dim rstTransactions As Recordset
        Dim BalanceAfterWithdrawal As Double
        Dim BalanceBeforeWithdrawal As Double
        Dim rstAccountsHistories As Recordset
    
        ' Get a reference to the current database
        Set dbKoloBank = CurrentDb
        
        If IsNull(txtEmployeeNumber) Then
            MsgBox "You must specify the name of the employee who is performing the transaction.", _
                   vbOKOnly Or vbInformation, "Kolo Bank"
            Exit Sub
        End If
        If IsNull(txtWithdrawalDate) Then
            MsgBox "You must indicate the date the transaction is occurring.", _
                   vbOKOnly Or vbInformation, "Kolo Bank"
            Exit Sub
        End If
        If IsNull(txtLocationCode) Then
            MsgBox "You must specify where the transaction is taking place.", _
                   vbOKOnly Or vbInformation, "Kolo Bank"
            Exit Sub
        End If
        If IsNull(txtAccountNumber) Then
            MsgBox "You must provide a valid account number.", _
                   vbOKOnly Or vbInformation, "Kolo Bank"
            Exit Sub
        End If
        If IsNull(cbxCurrenciesTypes) Then
            MsgBox "You must indicate what category of money is withdrawn (cash, check, etc).", _
                   vbOKOnly Or vbInformation, "Kolo Bank"
            Exit Sub
        End If
        If IsNull(txtAmountWithdrawn) Then
            MsgBox "You must specify the amount of money to withdraw or that was withdrawn.", _
                   vbOKOnly Or vbInformation, "Kolo Bank"
            Exit Sub
        Else
            amountWithdrawn = CDbl(Nz(txtAmountWithdrawn))
        End If
        
        If IsNull(txtNewBalance) Then
            MsgBox "You must indicate the new balance of the bank account.", _
                   vbOKOnly Or vbInformation, "Kolo Bank"
            Exit Sub
        End If
    
        Set rstTransactions = dbKoloBank.OpenRecordset("Transactions", 
                                                       RecordsetTypeEnum.dbOpenTable, _
                                                       RecordsetOptionEnum.dbAppendOnly, _
                                                       LockTypeEnum.dbOptimistic)
        
        With rstTransactions
            Do Until .EOF
                For Each fldTransaction In .Fields
                    ' Check the AccountNumber column.
                    ' If the current account number is the same as the one on the form,
                    If rstTransactions("AccountNumber").Value = txtAccountNumber Then
                        ' Get the account's (latest) balance
                        BalanceBeforeWithdrawal = CStr(rstTransactions("Balance").Value)
                        
                        ' Stop looking for the name
                        Exit For
                    End If
                Next
                .MoveNext
            Loop
        End With
        
        BalanceAfterWithdrawal = BalanceBeforeWithdrawal - amountWithdrawn
        
        ' We will not allow a type of withdrawal that would bring the account balance to less than $-20
        If BalanceAfterWithdrawal < -10 Then
            MsgBox "Money withdrawal is not allowed because of non-sufficient fund (NSF).", _
                   vbOKOnly Or vbInformation, "Kolo Bank"
            Exit Sub
        Else
            ' Get a reference to the Transactions table
            Set rstTransactions = dbKoloBank.OpenRecordset("Transactions")
        
            ' We will allow a customer to withdraw money as long as
            ' the new balance does not leave less than $-10 in the account
            ' If CDbl(txtAmount) > (CurrentBalance - 10) Then
            ' Add a new entry in the Transactions table to show the new balance
            rstTransactions.AddNew
            rstTransactions("EmployeeNumber").Value = txtEmployeeNumber
            rstTransactions("LocationCode").Value = txtLocationCode
            rstTransactions("TransactionDate").Value = txtWithdrawalDate
            rstTransactions("TransactionTime").Value = txtWithdrawalTime
            rstTransactions("AccountNumber").Value = txtAccountNumber
            rstTransactions("TransactionType").Value = "Withdrawal"
            rstTransactions("CurrencyType").Value = cbxCurrenciesTypes
            rstTransactions("WithdrawalAmount").Value = -CDbl(txtAmountWithdrawn)
            rstTransactions("Balance").Value = txtNewBalance
            rstTransactions("Notes").Value = txtNotes
            rstTransactions.Update
        End If
        
        Set rstTransactions = dbKoloBank.OpenRecordset("Transactions", 
                                                       RecordsetTypeEnum.dbOpenTable, _
                                                       RecordsetOptionEnum.dbAppendOnly, _
                                                       LockTypeEnum.dbOptimistic)
        
        With rstTransactions
            Do Until .EOF
                For Each fldTransaction In .Fields
                    ' Check the AccountNumber column.
                    ' If the current account number is the same as the one on the form,
                    If rstTransactions("AccountNumber").Value = txtAccountNumber Then
                        ' Get the account's (latest) balance
                        BalanceAfterWithdrawal = CStr(rstTransactions("Balance").Value)
                        
                        ' Stop looking for the name
                        Exit For
                    End If
                Next
                .MoveNext
            Loop
        End With
        
        ' Get the records from the Customers table
        Set rstCustomers = dbKoloBank.OpenRecordset("Customers", 
                                                    RecordsetTypeEnum.dbOpenTable)
        
        If BalanceAfterWithdrawal < 0 Then
            ' Suspend account.
            ' First check each bank account to find the one on the form
            With rstCustomers
                Do While Not .EOF
                    For Each fldCustomer In .Fields
                        If (fldCustomer.Name = "AccountNumber") And (fldCustomer.Value = txtAccountNumber) Then
                            ' Once you have found the record, start editing it
                            .Edit
                            ' Change the status to Suspended
                            .Fields("AccountStatus").Value = "Suspended"
                            .Update
                            
                            MsgBox "The account has been suspended because its balance became negative.", _
                                   vbOKOnly Or vbInformation, "Kolo Bank"
                            ' Since the account has been found and updated, stop looking for it
                            Exit For
                        End If
                    Next
                    .MoveNext
                Loop
            End With
            
            ' Since the status of the account has changed,
            ' add a new entry in the AccountsHistories table
            Set rstAccountsHistories = dbKoloBank.OpenRecordset("AccountsHistories", 
                                                                RecordsetTypeEnum.dbOpenTable, _
                                                                RecordsetOptionEnum.dbAppendOnly, _
                                                                LockTypeEnum.dbOptimistic)
    
            rstAccountsHistories.AddNew
            rstAccountsHistories("AccountNumber").Value = txtAccountNumber
            rstAccountsHistories("AccountStatus").Value = "Suspended"
            rstAccountsHistories("DateChanged").Value = CDate(txtWithdrawalDate)
            rstAccountsHistories("TimeChanged").Value = CDate(txtWithdrawalTime)
            rstAccountsHistories("ShortNote").Value = "The account was suspended because it became negative."
            rstAccountsHistories.Update
        End If
        
        MsgBox "=-= Money Withdrawal Completed =-=" & vbCrLf & _
               "Balance before withdrawal: " & CStr(BalanceBeforeWithdrawal) & vbCrLf & _
               "Amount withdrawn: " & CStr(amountWithdrawn) & vbCrLf & _
               "Balance after withdrawal = " & CStr(BalanceAfterWithdrawal)
        
        Set rstCustomers = Nothing
        Set dbKoloBank = Nothing
        
        cmdClose_Click
        
        Exit Sub
        
    cmdSubmit_ClickError:
        MsgBox "The withdrawal was not processed because of an error." & vbCrLf & _
               "Error #:     " & Err.Number & vbCrLf & _
               "Description: " & Err.Description, _
               vbOKOnly Or vbInformation, "Kolo Bank"
        Resume Next
    End Sub

70. In the Project window, double-click Form_Charge Against Account
71. In the Object combo box, select cmdSubmit
72. Implement the event as follows:

    Private Sub cmdSubmit_Click()
    On Error GoTo cmdSubmit_ClickError
    
        Dim fldCustomer As Field
        Dim dbKoloBank As Database
        Dim amountCharged As Double
        Dim rstCustomers As Recordset
        Dim rstTransactions As Recordset
        Dim balanceAfterCharge As Double
        Dim rstAccountsHistories As Recordset
    
        ' Get a reference to the current database
        Set dbKoloBank = CurrentDb
        
        If IsNull(txtEmployeeNumber) Then
            MsgBox "You must specify the name of the employee who is performing the transaction.", _
                   vbOKOnly Or vbInformation, "Kolo Bank"
            Exit Sub
        End If
        If IsNull(txtChargeDate) Then
            MsgBox "You must indicate the date the charge was made.", _
                   vbOKOnly Or vbInformation, "Kolo Bank"
            Exit Sub
        End If
        If IsNull(txtLocationCode) Then
            MsgBox "You must specify where the charge is taking (or took) place.", _
                   vbOKOnly Or vbInformation, "Kolo Bank"
            Exit Sub
        End If
        If IsNull(txtAccountNumber) Then
            MsgBox "You must provide an account number against which the charge is (was) made.", _
                   vbOKOnly Or vbInformation, "Kolo Bank"
            Exit Sub
        End If
        If IsNull(cbxChargesReasons) Then
            MsgBox "You must indicate the reason a charge is being made.", _
                   vbOKOnly Or vbInformation, "Kolo Bank"
            Exit Sub
        End If
        If IsNull(cbxCurrenciesTypes) Then
            MsgBox "You must indicate what category of money is withdrawn (cash, check, etc).", _
                   vbOKOnly Or vbInformation, "Kolo Bank"
            Exit Sub
        End If
        If IsNull(txtAmountCharged) Then
            MsgBox "You must specify the amount of money to charge or that was withdrawn.", _
                   vbOKOnly Or vbInformation, "Kolo Bank"
            Exit Sub
        Else
            amountCharged = CDbl(Nz(txtAmountCharged))
        End If
        
        If IsNull(txtNewBalance) Then
            MsgBox "You must indicate the new balance of the bank account.", _
                   vbOKOnly Or vbInformation, "Kolo Bank"
            Exit Sub
        End If
        
        Set rstTransactions = dbKoloBank.OpenRecordset("Transactions", 
                                                       RecordsetTypeEnum.dbOpenTable, _
                                                       RecordsetOptionEnum.dbAppendOnly, _
                                                       LockTypeEnum.dbOptimistic)
        
        rstTransactions.AddNew
        rstTransactions("EmployeeNumber").Value = txtEmployeeNumber
        rstTransactions("LocationCode").Value = txtLocationCode
        rstTransactions("TransactionDate").Value = txtChargeDate
        rstTransactions("TransactionTime").Value = txtChargeTime
        rstTransactions("AccountNumber").Value = txtAccountNumber
        rstTransactions("TransactionType").Value = "Service Charge"
        rstTransactions("CurrencyType").Value = cbxCurrenciesTypes
        rstTransactions!ChargeReason = cbxChargesReasons
        rstTransactions("ChargeAmount").Value = -CDbl(txtAmountCharged)
        rstTransactions("Balance").Value = txtCurrentBalance
        rstTransactions("Notes").Value = txtNotes
        rstTransactions.Update
    
        Set rstTransactions = dbKoloBank.OpenRecordset("SELECT Balance " & _
                                                       "FROM Transactions " & _
                                                       "WHERE AccountNumber = '" & txtAccountNumber & "';",
                                                       RecordsetTypeEnum.dbOpenDynamic, _
                                                       RecordsetOptionEnum.dbAppendOnly, _
                                                       LockTypeEnum.dbOptimistic)
    
        If rstTransactions.RecordCount > 0 Then
            rstTransactions.MoveLast
            balanceAfterCharge = rstTransactions!Balance
        End If
        
        ' Get the records from the Customers table
        Set rstCustomers = dbKoloBank.OpenRecordset("Customers")
        
        If balanceAfterCharge < 0 Then
            ' Suspend account.
            ' First check each bank account to find the one on the form
            With rstCustomers
                Do While Not .EOF
                    For Each fldCustomer In .Fields
                        If (fldCustomer.Name = "AccountNumber") And (fldCustomer.Value = txtAccountNumber) Then
                            ' Once you have found the record, start editing it
                            .Edit
                            ' Change the status to Suspended
                            .Fields("AccountStatus").Value = "Suspended"
                            .Update
                            
                            MsgBox "The account has been suspended because its balance became negative.", _
                                   vbOKOnly Or vbInformation, "Kolo Bank"
                            ' Since the account has been found and updated, stop looking for it
                            Exit For
                        End If
                    Next
                    .MoveNext
                Loop
            End With
            
            ' Since the status of the account has changed,
            ' add a new entry in the AccountsHistories table
            Set rstAccountsHistories = dbKoloBank.OpenRecordset("AccountsHistories", 
                                                                RecordsetTypeEnum.dbOpenTable, _
    		                                            RecordsetOptionEnum.dbAppendOnly, _
    		                                            LockTypeEnum.dbOptimistic)
        
            rstAccountsHistories.AddNew
            rstAccountsHistories("AccountNumber").Value = txtAccountNumber
            rstAccountsHistories("AccountStatus").Value = "Suspended"
            rstAccountsHistories("DateChanged").Value = CDate(txtChargeDate)
            rstAccountsHistories("TimeChanged").Value = CDate(txtChargeTime)
            rstAccountsHistories("ShortNote").Value = "The account was suspended because it became negative."
            rstAccountsHistories.Update
        End If
        
        MsgBox "The service charge was applied to the account.", _
                vbOKOnly Or vbInformation, _
               "Kolo Bank"
        
        Set rstCustomers = Nothing
        Set dbKoloBank = Nothing
        
        cmdClose_Click
        
        Exit Sub
        
    cmdSubmit_ClickError:
        MsgBox "The withdrawal was not processed because of an error." & vbCrLf & _
               "Error #:     " & Err.Number & vbCrLf & _
               "Description: " & Err.Description, _
               vbOKOnly Or vbInformation, "Kolo Bank"
        Resume Next
    
    End Sub

73. Return to Microsoft Access
74. Close the form
75. When asked whether you want to save, click Yes
76. Type the following code:

    Private Sub cmdNewDeposit_Click()
        DoCmd.OpenForm "New Deposit"
    End Sub
    
    Private Sub cmdNewWithdrawal_Click()
        DoCmd.OpenForm "New Withdrawal"
    End Sub
    
    Private Sub cmdNewCharge_Click()
        DoCmd.OpenForm "New Charge"
    End Sub
    
    Private Sub cmdClose_Click()
        DoCmd.Close
    End Sub

Characteristics of a Combo Box

The Row Source

To specify the list of items of a combo box, its class is equipped with a property named RowSource. Probably the easiest way to specify the row source is to use a SQL statement. If you create a combo box using a wizard and you selected a table followed by (some of) its fields, the wizard would generate a SQL statement for you. If necessary, you can modify that statement.

Practical Learning: Introducing the Row Source of a Combo Box

1. On the Ribbon, click File and click Open
2. In the list of files, click Fun Department Store1 from Lesson 41
3. In the Navigation Pane, right-click the New Employee form and click Design View

   

4. Click the group box around Marital Status (the rectangle) and press Delete)
5. Click the group box around Filing Status (the rectangle) and press Delete)
6. In the Controls section of the Ribbon, click the Combo Box and click the form
7. In the first page of the wizard, make sure the first radio button is selected and click Next
8. In the second page of the wizard, click Table: MaritalsStatus and click Next
9. In the third page of the wizard and in the Available Fields list, double-click MaritalStatus and click Next
10. In the 4th page of the wizard, click Next
11. In the 5th page of the wizard, click Next
12. In the 6th page of the wizard, click Finish
13. While the combo box is still selected, in the Property Sheet, click the All tab and change the following characteristics:
    Name: cbxMaritalsStatus
    Row Source: SELECT [MaritalsStatus].[MaritalStatusID] & " - " & [MaritalsStatus].[MaritalStatus] FROM MaritalsStatus;
14. Position the new combo box and its label where the Marital Status group box was
15. In the Controls section of the Ribbon, click the Combo Box and click the form
16. In the first page of the wizard, make sure the first radio button is selected and click Next
17. In the list of the second page of the wizard, double-click Table: FilingsStatus
18. In the Available Fields list of the third page of the wizard, double-click FilingStatus and click Next
19. In the 4th page of the wizard, click Next
20. In the 5th page of the wizard, click Next
21. In the 6th page of the wizard, click Finish
22. While the combo box is still selected, in the Property Sheet, change the follow characteristics:
    Name: cbxFilingsStatus Row Source: SELECT [FilingsStatus].[FilingStatusID] & " - " & [FilingsStatus].[FilingStatus] FROM FilingsStatus;
23. Position the combo box and its label where the Filing Status group box was
24. Format the combo boxes like the text boxes and their labels like the other labels on the form:

    

25. Right-click the Submit button and click Build Event
26. In the Choose Builder dialog box, click Code Builder and click OK
27. Implement the event as follows:

    Private Sub cmdSubmit_Click()
        Dim curDatabase As Object
        Dim rstEmployees As Object
        
        Set curDatabase = CurrentDb
        Set rstEmployees = curDatabase.OpenRecordset("Employees")
        
        If IsNull(txtEmployeeNumber) Then
            MsgBox "Make sure you provide an employee number.", _
                   vbOKOnly Or vbInformation, "Fun Department Store"
            Exit Sub
        End If
        
        If IsNull(txtLastName) Then
            MsgBox "Make sure you provide at least a last name for the employee.", _
                   vbOKOnly Or vbInformation, "Fun Department Store"
            Exit Sub
        End If
        
        If IsNull(cbxMaritalsStatus) Then
            MsgBox "Make sure you select the marital status of the employee.", _
                   vbOKOnly Or vbInformation, "Fun Department Store"
            Exit Sub
        End If
        
        If IsNull(cbxFilingsStatus) Then
            MsgBox "Make sure you select the filing status of the employee.", _
                   vbOKOnly Or vbInformation, "Fun Department Store"
            Exit Sub
        End If
        
        rstEmployees.AddNew
        rstEmployees("EmployeeNumber").Value = txtEmployeeNumber
        rstEmployees("FirstName").Value = txtFirstName
        rstEmployees("LastName").Value = txtLastName
        rstEmployees("Address").Value = txtAddress
        rstEmployees("City").Value = txtCity
        rstEmployees("County").Value = txtCounty
        rstEmployees("State").Value = txtState
        rstEmployees("ZIPCode").Value = txtZIPCode
        rstEmployees("MaritalStatus").Value = Left(cbxMaritalsStatus, 1)
        rstEmployees("Exemptions").Value = txtExemptions
        rstEmployees("HourlySalary").Value = txtHourlySalary
        rstEmployees("FilingStatus").Value = Left(cbxFilingsStatus, 1)
        rstEmployees.Update
        
        cmdClose_Click
        
        Set rstEmployees = Nothing
        Set curDatabase = Nothing
    End Sub

28. In the Object combo box, select cmdClose and implement the event as follows:

    Private Sub cmdClose_Click()
        DoCmd.Close
    End Sub

29. Close Microsoft Visual Basic and return to Microsoft Access
30. Right-click the Close button and click Build Event

The Row Source Type of a Combo Box

The primary reason for having a combo box is to display a list of items. There are various ways you can specify or create the list. We saw that one way is to use a SQL statement. Another approach is to manually create a list of items.

When adding a combo box to a form or report, if you use the wizard, it would assist you with creating and even configuring the list of items. If you don't use the wizard, you can create and configure the list yourself. The property that allows you to specify the type of list is called RowSourceType. As it happens, the combo box of Microsoft Access provides three ways to specify the origin of the list. Two options require a table or a query.

If you want to create a list of strings to display in a combo box, set the RowSourceType property to Value List. This can be done as follows:

Private Sub Detail_Click()
    cbxGenders.RowSourceType = "Value List"
End Sub

After specifying this, to assist you with adding the items to the list of the control, the ComboBox class is equipped with a collection property. This property mimics the behavior of the Collection class we reviewed already. To add an item to the control, call its AddItem() method. Here are examples:

Private Sub Detail_Click()
    cbxGenders.RowSourceType = "Value List"
    
    cbxGenders.AddItem "Male"
    cbxGenders.AddItem "Female"
    cbxGenders.AddItem "Unknown"
End Sub

After creating the control, to locate an item in its list, you can use its indexed property.

Many of the combo boxes you will use in your forms or reports get their values from a table through a pre-established relationship. Such combo boxes have their RowSourceType set to Table/Query.

In the SQL, to sort a fiestatements, remember that you can use the CreateQueryDef() method.

Practical Learning: Introducing Sorting Records

1. In the Navigation Pane, right-click Inventory Analysis and click Design View
2. In the Controls section of the Ribbon, click the Combo Box and click the left side under the Form Footer bar
3. In the first page of the Combo Box Wizard, click the second radio button (I Will Type In The Values That I Want) and click Next
4. Click under Col1 and type Item Number
5. Press the down arrow key and type Manufacturer
6. Complete the list with Category, Sub-Category, Item Name, and Unit Price
7. Click Next
8. In the third page of the wizard, accept the first radio button and click Next
9. Change the label to Sort by:
10. Click Finish
11. In the Properties window, change its Name to cbxColumnNames
12. In the Controls section of the Ribbon, click the Combo Box and click on the right side of the previously added combo box in the Form footer section
13. In the first page of the Combo Box Wizard, click the second radio button (I Will Type In The Values That I Want) and click Next
14. Click under Col1 and type Ascending Order
15. Press the down arrow key and type Descending Order

    

16. Click Next
17. In the third page of the wizard, accept the first radio button and click Next
18. Change the label to in and click Finish
19. In the Properties window, change the combo box' Name to cbxSortOrder
20. From the Controls section of the Ribbon, add a Button to the Footer section section
21. In the Property Sheet, change its Name cmdRemoveFilterSort and its Caption to Remove Filter/Sort
    

    

22. On the form, click the Sort By combo box
23. In the Property Sheet, double-click After Update, then click its ellipsis button
24. Implement the event as follows:
    

    Option Compare Database
    Option Explicit
    
    Private strColumnName As String
    Private strSortOrder As String
    
    Private Sub cbxColumnNames_AfterUpdate()
    On Error GoTo cbxColumnNames_AfterUpdate_Error
      
        ' Get the string selected in the Sort By combo box
        ' and find its equivalent column name
        If cbxColumnNames = "Item Number" Then
            strColumnName = "ItemNumber"
        ElseIf cbxColumnNames = "Manufacturer" Then
            strColumnName = "Manufacturer"
        ElseIf cbxColumnNames = "Category" Then
            strColumnName = "Category"
        ElseIf cbxColumnNames = "Sub-Category" Then
            strColumnName = "SubCategory"
        ElseIf cbxColumnNames = "Item Name" Then
            strColumnName = "ItemName"
        ElseIf cbxColumnNames = "Unit Price" Then
            strColumnName = "UnitPrice"
        Else
            strColumnName = ""
        End If
        
        ' Sort the records based on the column name from the combo box
        Me.OrderBy = strColumnName
        Me.OrderByOn = True
        
        ' Set the In combo box to ascending order by default
        cbxSortOrder = "Ascending Order"
    
        Exit Sub
        
    cbxColumnNames_AfterUpdate_Error:
        MsgBox "There was an error when trying to sort the records. " & _
               "Please report the error as follows." & vbCrLf & _
               "Error #: " & Err.Number & vbCrLf & _
               "Description: " & Err.Description & vbCrLf & _
               "Please contact the program vendor if " & _
           "he is not sleeping at this time."
        Resume Next
    End Sub

25. In the Object combo box, select cbxSortOrder
26. In the Procedure combo box, select AfterUpdate
27. Implement the event as follows:
    

    Private Sub cbxSortOrder_AfterUpdate()
    On Error GoTo cbxSortOrder_AfterUpdate_Error
        
        ' Unless the user selects Descending Order...
        If cbxSortOrder = "Descending Order" Then
            strSortOrder = "DESC"
        Else ' We will consider that it should be sorted in ascending order
            strSortOrder = "ASC"
        End If
        
        Me.OrderBy = strColumnName & " " & strSortOrder
        Me.OrderByOn = True
        Exit Sub
        
    cbxSortOrder_AfterUpdate_Error:
        MsgBox "There was an error when trying to sort the records. " & _
               "Please report the error as follows." & vbCrLf & _
               "Error #: " & Err.Number & vbCrLf & _
               "Description: " & Err.Description & vbCrLf & _
               "Please contact the program vendor if " & _
           "he is not sleeping at this time."
        Resume Next
    End Sub

28. In the Object combo box, select cmdRemoveFilterSort
29. Implement the event as follows:
    

    Private Sub cmdRemoveFilterSort_Click()
        Me.OrderBy = ""
        Me.OrderByOn = False
        
        Me.cbxColumnNames = "Item Number"
        Me.cbxSortOrder = "Ascending Order"
    End Sub

30. Return to the form and save it
31. Using the combo box in the Form Footer section, try sorting the records by the Manufacturer
32. Click the Remove Filter/Sort button
33. Sort the records by Unit Price in descending order
34. Close the form
35. When asked whether you want to save, click Yes

Re-Querying a Combo Box

To make data entry convenient, you can allow the user to add a value from the form or report where the combo box resides. Unfortunately, after adding the new value, the combo box is not automatically updated. You or the user must manually update the combo box. The user can change the form's view to design and switch it back to Form View. This is inconvenient and most users don't know that this is possible. Fortunately, the ComboBox class is equipped with a method to update itself. The method is called Requery.

Updating a Combo Box

As we saw in previous sections, a combo box is a prime candidate for dealing with records in relationship-based objects. Usually, when using a combo box, if you change the values on the table that holds its data, and then get back to the form, the combo box would not have the new value. To solve this problem, you have many options.

To update the contents of a combo box, you can call its Requery() method.

The Not In List Event

When using a combo box, a user may want to select a value that is not in the list. To do this, the user may click the text box part of the combo box, type a value, and press Enter or Tab. If the user does this, the database engine would produce an error and fire the Not In List event. You can implement this event to do what is necessary to assist the user. For example, you can get the value the user had typed and add it to the table that owns the records of the combo box.

Practical Learning: Refreshing a Combo Box

1. In Navigation Pane, right-click the New Ssore Item form and click Design View
2. On the form, click the Manufacturer combo box
3. In the Property Sheet, click the Event tab and double-click On Not In List
4. Click its ellipsis button and implement the event as follows:
   

   Private Sub cbxManufacturers_NotInList(NewData As String, Response As Integer)
   On Error GoTo cbxManufacturers_NotInList_Error
       Dim NewManufacturerID As Long
       
       If IsNull(ManufacturerID) Then
           ' Set the value of the combo box empty
           ManufacturerID = ""
       Else
           ' If the foreign key currently has a value,
           ' assign that value to the declared value
           NewManufacturerID = ManufacturerID
           ' Set the foreign key to null
           ManufacturerID = Null
       End If
       
       ' The combo box is ready to receive a new value.
       ' To make it happen, display the Manufacturers form
       ' as a dialog box so the user would not use
       ' the Store Items form while the Manufacturers form is opened
       ' When opening the Manufacturers form, create a new record
       ' and display the new manufacturer in it
       If MsgBox("The '" & NewData & "' manufacturer does not exist in the database. " & _
                 "Do you want to add it?", _
                 vbYesNo, "Fun Department Store - FunDS") = vbYes Then
           DoCmd.OpenForm "Manufacturers", , , , acFormAdd, AcWindowMode.acDialog, NewData
       
           ' After using the Manufacturers dialog box, let the user close it.
           ' When the user closes the Manufacturers form, refresh the ManufacturerID combo box
           ManufacturerID.Requery
       
           ' If the user had created a new manufacturer,
           ' assign its ManufacturerID to the variable we had declared
           If ManufacturerID <> 0 Then
               ManufacturerID = NewManufacturerID
           End If
           
           ' Assuming that the manufacturer was created, ignore the error
           Response = acDataErrAdded
       Else
           ' If the manufacturer was not created, indicate an error
           Response = acDataErrContinue
       End If
       
   cbxManufacturersNotInList_Exit:
       Exit Sub
       
   cbxManufacturers_NotInList_Error:
       MsgBox "An error occured when trying to update the list." & vbCrLf & _
              "=- Report the error as follows -=" & vbCrLf & _
              "Error #: " & Err.Number & vbCrLf & _
              "Error Message: " & Err.Description
              Resume cbxManufacturersNotInList_Exit
   End Sub

5. In the Object combo box, select cbxCategories
6. In the Procedure combo box, select NotInList
7. Implement the event as follows:

   Private Sub cbxCategories_NotInList(NewData As String, Response As Integer)
   On Error GoTo cbxCategories_NotInList_Error
       
       Dim NewCategoryID As Long
       
       If IsNull(cbxCategories) Then
           cbxCategories = ""
       Else
           NewCategoryID = cbxCategories
           cbxCategories = Null
       End If
       
       If MsgBox(NewData & " is not a valid category of this database. " & _
                 "Do you want to add it?", _
                 vbYesNo, "Fun Department Store - FunDS") = vbYes Then
           DoCmd.OpenForm "Categories", , , , acFormAdd, AcWindowMode.acDialog, NewData
       
           cbxCategories.Requery
       
           If CategoryID <> 0 Then
               cbxCategories = NewCategoryID
           End If
           
           Response = acDataErrAdded
       Else
           Response = acDataErrContinue
       End If
       
   cbxCategories_NotInList_Exit:
       Exit Sub
       
   cbxCategories_NotInList_Error:
       MsgBox "An error occured when trying to update the list." & vbCrLf & _
              "=- Report the error as follows -=" & vbCrLf & _
              "Error #: " & Err.Number & vbCrLf & _
              "Error Message: " & Err.Description, _
              vbOKOnly, "Fun Department Store - FunDS"
       Resume cbxCategories_NotInList_Exit
   End Sub

8. In the Object combo box, select cbxSubCategories
9. In the Procedure combo box, select NotInList
10. Implement the event as follows:

    Private Sub cbxSubCategories_NotInList(NewData As String, Response As Integer)
    On Error GoTo cbxSubCategories_NotInList_Error
        
        Dim NewSubCategoryID As Long
        
        If IsNull(cbxSubCategories) Then
            cbxSubCategories = ""
        Else
            NewSubCategoryID = cbxSubCategories
            cbxSubCategories = Null
        End If
        
        If MsgBox(NewData & " is not a valid sub-category of this database. " & _
                  "Do you want to add it?", _
                  vbYesNo, "Fun Department Store - FunDS") = vbYes Then
            DoCmd.OpenForm "SubCategories", , , , acFormAdd, AcWindowMode.acDialog, NewData
        
            cbxSubCategories.Requery
        
            If cbxSubCategories <> 0 Then
                cbxSubCategories = NewSubCategoryID
            End If
            
            Response = acDataErrAdded
        Else
            Response = acDataErrContinue
        End If
        
    cbxSubCategories_NotInList_Exit:
        Exit Sub
        
    cbxSubCategories_NotInList_Error:
        MsgBox "An error occured when trying to update the list." & vbCrLf & _
               "=- Report the error as follows -=" & vbCrLf & _
               "Error #: " & Err.Number & vbCrLf & _
               "Error Message: " & Err.Description, _
               vbOKOnly, "Fun Department Store - FunDS"
        Resume cbxSubCategories_NotInList_Exit
    End Sub

11. In the Object combo box, select cmdSubmit and implement its event as follows:

    Private Sub cmdSubmit_Click()
        If IsNull(txtItemNumber) Then
            MsgBox "You must enter an item number.", _
                   vbOKOnly, "Fun Department Store - New Store Item"
            Exit Sub
        End If
        If IsNull(txtItemName) Then
            MsgBox "You must enter the name (or a short description) of the item.", _
                   vbOKOnly, "Fun Department Store - New Store Item"
            Exit Sub
        End If
        If IsNull(txtUnitPrice) Then
            MsgBox "You must enter an item number.", _
                   vbOKOnly, "Fun Department Store - New Store Item"
            Exit Sub
        End If
        
        DoCmd.RunSQL "INSERT INTO StoreItems(ItemNumber, ManufacturerID, CategoryID, " & _
                     "                       SubCategoryID, ItemName, ItemSize, UnitPrice, DiscountRate) " & _
                     "VALUES('" & txtItemNumber & "', " & cbxManufacturers & ", " & _
                     cbxCategories & ", " & cbxSubCategories & ", '" & txtItemName & "', '" & _
                     txtItemSize & "', " & CDbl(Nz(txtUnitPrice)) & ", " & CDbl(Nz(txtDiscountRate)) & ");"
        
        MsgBox "The new item has been created.", _
                   vbOKOnly, "Fun Department Store - New Store Item"
        
        DoCmd.Close
    End Sub

12. In the Object combo box, select cmdClose and implement its event as follows:

    Private Sub cmdClose_Click()
    On Error GoTo cmdClose_Click_Err
    
        DoCmd.Close , ""
    
    cmdClose_Click_Exit:
        Exit Sub
    
    cmdClose_Click_Err:
        MsgBox Error$
        Resume cmdClose_Click_Exit
    End Sub

13. In the Project window, double-click Form_Department Store
14. In the Object combo box, select cmdNewStoreItem and implement its event as follows:

    Private Sub cmdNewStoreItem_Click()
        DoCmd.OpenForm "NewStoreItem"
    End Sub

15. In the Object combo box, select cmdEmployeesRecords and implement its event as follows:

    Private Sub cmdEmployeesRecords_Click()
        DoCmd.OpenForm "Employees"
    End Sub

16. Return to Microsoft Access
17. In the Navigation Pane, right-click the Manufacturers form and click Design View
18. In the Property Sheet, click Event and double-click On Load
19. Click its ellipsis button to switch to Microsoft Visual Basic
20. Implement the event as follows:

    Private Sub Form_Load()
        ' When this form opens, find out if it received an external 
        ' value from another object (such as the StoreItemEditor form).
        If Not IsNull(Me.OpenArgs) Then
        	' If it did, put that value in the Manufacturer text box
            Manufacturer = Me.OpenArgs
            ' Since our database allows up to three different names for 
            ' a manufacturer, the user will optionnally fill the other two text boxes
        End If
    End Sub
    
    Private Sub cmdClose_Click()
    On Error GoTo cmdClose_Click_Err
    
        DoCmd.Close , ""
    
    cmdClose_Click_Exit:
        Exit Sub
    
    cmdClose_Click_Err:
        MsgBox Error$
        Resume cmdClose_Click_Exit
    End Sub

21. Return to Microsoft Visual Access
22. In the Navigation Pane, right-click the Categories form and click Design View
23. In the Event section of the Properties window, double-click On Load
24. Click its ellipsis button and implement the event as follows:

    Private Sub Form_Load()
        If Not IsNull(Me.OpenArgs) Then
            Category = Me.OpenArgs
        End If
    End Sub
    
    Private Sub cmdClose_Click()
    On Error GoTo cmdClose_Click_Err
    
        DoCmd.Close , ""
    
    cmdClose_Click_Exit:
        Exit Sub
    
    cmdClose_Click_Err:
        MsgBox Error$
        Resume cmdClose_Click_Exit
    End Sub

25. Return to Microsoft Visual Access
26. In the Navigation Pane, right-click the SubCategories form and click Design View
27. In the Event section of the Properties window, double-click On Load
28. Click its ellipsis button and implement the event as follows:

    Private Sub Form_Load()
        If Not IsNull(Me.OpenArgs) Then
            SubCategory = Me.OpenArgs
        End If
    End Sub
    
    Private Sub cmdClose_Click()
    On Error GoTo cmdClose_Click_Err
    
        DoCmd.Close , ""
    
    cmdClose_Click_Exit:
        Exit Sub
    
    cmdClose_Click_Err:
        MsgBox Error$
        Resume cmdClose_Click_Exit
    End Sub

29. Close Microsoft Visual Basic and return to Microsoft Access
30. Close Microsoft Access
31. When asked whether you want to save it, click Yes
32. On the Ribbon, click File and click Open
33. In the list of files, click WattsALoan1 from Lesson 41
34. In the Navigation Pane, right-click the LoanAllocation form and click Design View
35. On the form, click the Loan Type text box and press Delete
36. In the Controls section of the Ribbon, click the Combo Box and click the Detail section of the form
37. In the first page of the wizard, make sure the first radio button is selected and click Next
38. In the second page of the wizard, in the list of tables, click Table: LoansTypes and click Next
39. In the third page of the wizard, in the Available Fields list, double-click LoanType and click Next
40. In the 4th page of the wizard, click Next
41. In the 5th page of the wizard, click Next
42. In the 6th page of the wizard, click Finish
43. Complete the design of the form as follows:

    

44. Save and close the form
45. On the Ribbon, click File and click Open
46. In the list of files, click Kolo Bank1 used earlier (the resources that accompany these lessons include a Microsoft Excel spreadsheet named Kolo Bank access its Transactions workbook and create some records: deposits, withdrawals, and charges)
47. Close Microsoft Access