List items edit form

Question

I have a combo box on a form.  If the text entered is not on the list it is set to open a form to edit the list. All well and good but a bit cumbersome to have to re-enter the text in the list items edit form. Any idea on a solution ?

Answer 1

Take a look at the file NotInList.zip in my public databases folder at:

https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

You might have to copy the text of the link into your browser's address bar (not the link location).  For some reason it doesn't always seem to work as a hyperlink.

This file demonstrates the use of the NotInlist event procedure of a combo box in various contexts, some which open a form to enter additional data to the new record in the referenced table, some which transparently insert a new row in to the referenced table where no additional data is required.

Answer 2

The answer would depend on the finer points of your implementation, but suffice to say if you do it correctly it does not need to be entered again, Check the sample code in the NotInList event in the help file.

Answer 3

This depends on whether the info you are adding to the RowSource of the combo is just the one piece of data or whether there is other info to be added.

For example, lets say you have a lookup table of product categories. The table looks like:

tluProductCategories

ProductCategoryID (PK Autonumber)

ProductCategory

So the user types in the category. You can then use the NotInList event to add that new category to the table after prompting the user. You can find examples of code for the NotInList event in VBA help.

I still use the NotInList event when I want to add more info as well. The difference is I capture the Autonumber PK after inserting, and then open the form filtered for the newly added record.

Answer 4

Take a look at this page from Allen Browne's site:

http://allenbrowne.com/ser-27.html

He uses a query to add an item to the table rather than a recordset, so that is most likely the best way but I don't know why.

Answer 5

Instead of opening a form, why not use a msgbox to check with the user that they want to add to the list.  If a new entry is required then this could be made with a recordset.