Add Values to Rowsource via Combo Box

[gschimek]

I hope this is an easy one...

I have a combo box that's got it's rowsource linked to a table, and when an item is selected, it updates the field associated to it in the combo box's control source.

But there are times when the value I need isn't already in the rowsource table and needs to be added before it can be selected from the combo box. Is it possible to add a new value in a combo box, and have that value be added to the table linked in the rowsource? So if I add something new in that combo box, it'll be there for the next time I might want to select it.

Thanks in advance.

 

[Adam Caramon]

I hope this is an easy one...

It should be. If the combo box's row source is a table, simply go into that table and key in the additional information.

 

[gschimek]

I'm hoping not to have to go into the table, though, for usability.

I've done some more digging since I posted and I think I've found an answer. I see that Access 2010 has an option to limit selections to the list, and then either pop up a form, or kick off the Not In List event.

I think I like either of those options, but is there a quick and easy way to take the data that someone just entered (that's not currently in the list) and have that entered automatically as a new record in the rowsource table, or else have it jump to a new record via the pop-up form, and enter that value into the form? Right now if I use the pop-up form, it just goes to the first record in the table, and then the person has to click "New record" and then enter the new value a second time.

 

[Adam Caramon]

...or else have it jump to a new record via the pop-up form, and enter that value into the form?

Yes, you can do that. In the form's On_Load event, use:

DoCmd.GoToRecord , , acNewRec

Then in the forms On_Close event do:

Forms!YourFormName!YourComboBoxName.Requery

Try that out and see if it works as expected for you.

 

[Galaxiom]

No need for a form. The new value can quietly be added to the rowsource table in response to the not in list event using SQL.

However what usually happens is a lot of unwanted and misspelled entries.

 

[gschimek]

I found this link and option 1 did the trick for me, which is the SQL option mentioned. http://allenbrowne.com/ser-27.html

Thanks for all the help.