How to add new company in drop down box in a form

[dwheatley]

Hi,

I am trying to make my Form as efficient as possible.

Using a combo box I have it set up so that when I enter a new company name which is not already on the drop down box, it will save that entry to my table.

However, when I go to use the form again, I want that new entry to show up on the drop down box as an option.

I tried selecting the option 'I want the combo box to get the values from another table or query' option, but when this is selected it does not allow me to add a new name to the drop down box in the form if it isn't already available in the table (which kind of renders the point of the form useless).

Any help?

Many thanks.

[ranman256]

i have a button next to the drop box [add] which opens that table to add new items.
docmd.opentable "tCodes"

but once you add items there, and come back to the form, you must click the REFRESH ALL button on the toolbar to see the added items in the combo.
this is the simple solution.

you can always make the add new code a form
docmd.openform "frmCodes"

and place a REFRESH ALL button on that form to refresh everything, but why re-invent the wheel?

[orange]

See this free video for a method included in Access to add an Item to your combo

[ArviLaanemets]

When company info is read from separate table, then you have to add new company in separate form, or you have to use some unbound controls on your form to enter at least the new company name, and a button to add new company to companies table, refresh combo's recordsource, and set the new value for combo equal with freshly added one.

When you have company name saved directly in items table, then you can use a single-column combo to select from existing company names in your items table (YourCombo.Rowsource = SELECT DISTINCT CompanyName FROM ItemsTable ORDER BY CompanyName) and set LimitToList = No for combo (ColumnCount = 1 of-course). Whenever the user doesn't find the company name from drop-down, it can be entered into control. Whenever the user then moves to another record, the record with new company name will be saved, and the new company name will be accessible in combo.

[Bulzie]

If it is saving the new name into the table ok, then at the end of your code requery that combo box and it should now have that new value.

Me.YourComboboxName.Requery

[June7]

Or use the approach suggested in orange's post and don't need to write code. Mind blown!

I've seen that ListItemsEditForm property but never explored.