7/04/2012

Create new database file and transfer tables using VBA

Also learn how to populate underlying table using a popup form when new entries are keyed into combo box in Access 2007 | Access 2003/XP/2000/97.
Question: In Access 2003/XP/2000/97, I have a combo box that is populated from a table/query. How would one add an item to a combo box via popup form if a user enters a value that is not in the list?

Answer: We've provided a download to demonstrate how to code a combo box so that whenever you enter a value in a combo box that is not in the list of values, you can have the value added to the underlying table.
Download version in Access 2000
Let's take a look at the example. Below is a form that contains a combo box called ProductID (which displays values based on the CategoryID selected).


When a new value is entered in the ProductID combo box that is not in the list of values and you tab off, the following window will appear. In our example, we've entered the Product called "Sausages".


If you select Yes to the message box, a popup form will appear that will allow you to enter Sausage as a new Product. We've automatically pulled across the Category and Product Name values into this form to get the user started.


Once the user enters the information and clicks on the Done button, the popup form will close and the new Product will be added to the ProductID combo box.


Please note: If you try applying this technique to your own database and you receive a database variable "not defined" error, you can fix this problem by going to our associated page.

No comments:

Post a Comment