Lookup list, add

[gr8whitewaldron]

I have a form where the user is to enter data. In the pull-down lists, the user may choose one of the options, but may also type in an answer. How to I make it so that the user's typed in answer is added to the list so that it may be chosen in other instances?

 

[jdegeorge]

Here's the code I found on the MVPS site.

http://www.mvps.org/access/forms/frm0015.htm

Hope this helps! It works for me.

Jim DeGeorge

 

[gr8whitewaldron]

I ended up not using it but it helped, thanks.

 

[willir]

gr8whitewaldron

I learned to "cheat" for this. I create a list or combo box that references the same master table. In the row source, I use "SELECT DISTINCT" instead of the default DISTINCTROW. This way, only the unique values appear in the selection list.

Example: A loan tracking database where the type of loan is required to be entered. Instead of referencing another table to the loan_type, the query for the field references itself ... SELECT DISTINCT loan_type from loan.

Admittedly, for the first few records, it is a little awkward since the user will be creating new load types, but after more and more loan types are defined, fewer and fewer additional loan types are required. (And with custom lists or support table, some one still needs to enter the values.)

(This is just an example; a loan officer or bank manager would probably want more controls on loan types. But in your case, you have indicated that the user has fairly liberal access to adding new values.)

Yes, there is pretty cool coding out there for adding new values to list and combo boxes. Yes, it would be fairly easy to update another table (ie, tblLoanType). This is just a simple work around from a simple guy.

Richard

 

[jdegeorge]

gr8whitewaldron...

What did you do to solve your problem? One of the rules here is that you have to post your solution so that others searching the site with the same problem will have the solution.

Jim DeGeorge

 

[gr8whitewaldron]

Sorry, I was unaware. I poked around in the resource scheduling template that comes with Access (2k) and the way they do it is if it is not in the list, it brings up a message to double click, and when double clicked it brings up a form where you can add an entry. The NotInList Event helped me find it.

 

[jdegeorge]

I find it more rewarding to resolve things myself, too! Nice going! Too bad you can't give yourself a star!

Jim DeGeorge

 

[gr8whitewaldron]

Yea, I am so new at this anything that works is a triumph.