How to Add Item that is not in the list of combo box




How to Add Item not in the List of Combo box

A combo box is mostly a list of items linked to table or query. It also can be a list of item that created inside the combo box as a value list. In Access, mostly the item in the combo box is linked to the ID or primary key field of that item from its table. However, the ID field is usually hidden and will display only the second field of the data source of combo box. Also mostly the item in the drop-down list is set as limit to the list due to there are more than one field as a record source of combo box.

 For example below, I will use the combo box of customer on the Orders form. The record source or row source of Customer combo box comes from the Customers table. So the company name or customer name in the list of Customer combo box is limited to the company name in the Customers table. If the company name is not in the drop-down list of Customer combo box then we can add a new name to the Customers table and show it in the combo box. In this case, the company name cannot be added directly to the combo box without the function or VB code since there are many fields in the Row source of combo box. It is linked to the CustomerID field, not the company name as shown in the picture below.

 

Row Source of Customer combo box

 

 

Company list from Customers table

 How to do it

 Step #1  Add Event Procedure under the On Not In List

        Open the Orders form under the form Design View mode

        Double Click on the CustomerID combo box to open the Property Sheet

        Select Event tab of Property Sheet

        Select “Event Procedure” the On Not in List event

        Click on three dots () next to the Event Procedure to open the VBA

   

Step #2 Update VB code

Enter the vb code below under the Not in List procedure. How this code works?

        A message box will pop-up to ask if the user want to add this customer if the customer name not in the list is entered as shown in the picture below.

        If the answer is “No” then will clear the customer combo box and set focus on customer combo box again for the user to select the customer from the drop-down list.

        If the answer is “Yes” then will open the Customers form and go to new record. Set a company name of Customers form to the new customer name on the Orders form. Set the focus to ContactName field on the Customers form. At the same time, set the Customer combo box of Orders form to blank.

 



Step #3 Add New Customer

  • A new customer (company name) that is not in the list of combo box is entered
  • A message box will pop-up to ask if the user want to add this customer as shown in the picture below.
  • Click “Yes” to process the next step
  • Or click “No” to cancel adding new customer then just select the customer from the list.

 

 Step #4  Open Customers form

  • After click “Yes” on the previous step, it will open the Customers form with auto-filled in company name field with the customer from the Orders form. Then the user can update the additional information. 
  • Click Save & Close button to close this form and update the Customer combo box to this current customer.

Step #5  Update VB for Save & Close button

 Enter the vb code below under the Save & Close button. How this code works?

  • It will check if the Orders form is loaded
  • If the Orders form is loaded then it will update the customer list in the Customer combo box and set the customer name to the current customer (company name) on Customers form. Then close the Customers form.
  • If the Orders form is not loaded or is opened under the Design view mode then the Customers form will close.

 

Note: The part of VB code to test if the form is loaded can be put separately as a function. Then we can call the function to test if the form is loaded or not. More details at Function Check If Form is Loaded on my website http://www.iaccessworld.com/function-check-if-form-is-loaded/

Step #6  Finish

After the Customers form is closed, the Customer name will update to “Nancy Lopez” as the name that is newly entered and is not in the drop-down list at the beginning. Now this name is on the list of Customer combo box and also updated in the Customers table as shown below.





Related posts