Home

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 LearningPractical 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 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 Browse
  19. In the Available Pictures of the Picture Builder dialog box, scroll down and click MS Access Table:

    Picture Builder

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

    Fun Department Store - Database Switchboard

    Control Name Caption Other
    Label Label   Tables Font Name: Bodoni MT Black (or Garamond or Times New Roman)
    Font Size: 20
    Font Color: Black, Text 1
    Label Label   Forms Font Name: Bodoni MT Black (or Garamond or Times New Roman)
    Font Size: 20
    Font Color: Black, Text 1
    Line Line      
    Button Button cmdStoreItems   Store Items...  
    Button 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 Button cmdShoppingSessions   Shopping Sessions...  
    Button 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 Button cmdEmployees   Employees...  
    Button 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 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

    Relationships

  35. To close the Relationships window, click its Close button Close
  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:

    Fun Department Store - Employees Switchboard

  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:

    Fun Department Store - Manufacturers

  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:

    Fun Department Store - Categories

  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:

    Fun Department Store - Sub-Categories

  74. Save and close the Sub-Categories form
  75. On the Ribbon, click Create and click Form Design
  76. Double-click the Properties button Properties 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

    Inventory Analysis

  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:

    Inventory Analysis

  86. Save and close the Inventory Analysis form
  87. On the Ribbon, click Create and click Form Design
  88. Double-click the Properties button Properties 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:

    Fun Department Store - Form Design and Text Boxes

    Control  Caption Name 
    Label Label  Fun Department Store  
    Label Label  New Store Item  
    Text Box 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 Combo Box 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 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

    Combo Box Wizard

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

    Combo Box Wizard

  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 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 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:

    Fun Department Store - Form Design and Combo Boxes

    Control  Caption Name 
    Label Label  Fun Department Store  
    Label Label  New Store Item  
    Text Box Text Box Item Number:  txtItemNumber
    Combo Box Combo Box Manufacturer: cbxManufacturers
    Combo Box Combo Box Category: cbxCategories
    Combo Box Combo Box Sub-Category: cbxSubCategories
    Text Box Text Box Item Name:  txtItemName
    Text Box Text Box Item Size:  txtItemSize
    Text Box Text Box Unit Price: txtUnitPrice
    Text Box Text Box Discount Rate: txtDiscountRate
    Button Button Submit cmdSubmit
    Button 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 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:

    Kolo Bank - New Deposit

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

    Kolo Bank - New Withdrawal

    Control Caption Name
    Label Label Bank Account Withdrawal  
    Label Label Withdrawal Performed By  
    Text Box Text Box Withdrawal Date: txtWithdrawalDate
    Text Box Text Box Withdrawal Time: txtWithdrawalTime
    Text Box 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 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:

    Kolo Bank - New Charge

    Control Caption Name
    Label Label Bank Account New Charge  
    Label Label Charge Performed By  
    Text Box Text Box Charge Date: txtChargeDate
    Text Box Text Box Charge Time: txtChargeTime
    Combo Box Combo Box  Charge Reason  cbxChargesReasons
    Text Box 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

    Fun Department Store - New Employee

  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 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 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:

    Fun Department Store - New Employee

  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 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

    Combo Box Wizard

  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 Button to the Footer section section
  21. In the Property Sheet, change its Name cmdRemoveFilterSort and its Caption to Remove Filter/Sort

    Store Items Inventory

  22. On the form, click the Sort By combo box
  23. In the Property Sheet, double-click After Update, then click its ellipsis button Ellipsis
  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 Browse 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 Browse 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 Browse 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 Browse 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 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:

    Watts' A Loan - Loans Allocations - Form Design

  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
 
 
   
 

Previous Copyright © 2000-2016, FunctionX, Inc. Next