Unanswered: Using combo boxes in forms: auto update, no repeating and multi field desired!!
I am creating a form and need a little help.
I have set up several combo boxes in my form.I would like these boxes to:
- not repeat [list each value once no matter how many times it appears in the table]
- allow the entry of new values [not contained in the table]
- update the pull down list with each new entry.
Im thinking this sholdnt be as hard as im making it. BUt it seeems impossible.
ALso as an extra bonus. I would like to have the values in the list come from two fields in the tAble. When i enter the contact name i would like to pull down and see all authors and all organizations, all together in one column.
If you can solve any or all of this i would be SOO appreciative.
For you rowsource of the combo box put in an SQL statement such as:
SELECT DISTINCT tblName.[field1] FROM tblName WHERE (((tblName.[field1]) Is Not Null)) ORDER BY tblName.[field1];
This will give you unique values.
To get the "new" values entered into the table I did a Dlookup in the afterupdate event of my combo box. I lookedup the value of the combo box. If it does not exist I append it using a query and requery the recordsource of the combo box.
The only problem with this solution is that it leaves you vulnerable to typos when users may mistakenly enter a "new" item which is just a typo of an existing item.