For a person to have a better erection in you he will have to make sure that you are purchasing from a platform that is able to protect the personal information and monetary transactions of its customers and members. Learn More generic levitra After the age of 45 years, the average dopamine levels in our brain tend cheap viagra for women to decline with age. It is cialis for sale online recommended for you very important to know and understand how to drive smart and safe. Mast Mood capsule offers the effective way to treat cialis canadian generic symptoms of depression with anti-inflammatory drugs including non-steroidal anti-inflammatory drugs (NSAIDs).
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.
