Select from combobox or add new (1 Viewer)

I have a form with customer information. The information will be imported from Excel and someone will move through each record and add the provider information to it. There is a Provider table which holds the name and address. There is a combobox to choose a provider, which then displays the provider's address in a subform, and can be edited if needed. I would like to have a "Add new provider" button which opens a pop up form where the provider name and address would be added, and when they close it, it would populate what they typed over into the provider drop down on the main form and the provider address in the subform. I tried making the Add New Provider form be a subform as well, but since it is a data entry form and my main form is not, it didn't work. What is the best way to accomplish this? I just need a way for them to select the provider info that is already available in the table, be able to edit the address if needed, and have the ability to add new ones if the provider is not in there. I am not set on any particular setup, I just need it to function. I would appreciate any assistance. Thank you!

put an ADD New Provider button.
this opens the frmProvider form. Enter new provider info.
close form

in your customer form, click the REFRESH ALL button on the toolbar.
The new provider is now in the combo box. Selecting one will acivate the subform and display the info.

Thank you Ranman. I have the Add Provider button and a data entry form that opens to type in the name and address. This will be used by a team of people, so I will probably hide the toolbar. I was hoping that when they type in a new provider, the new ProviderID (autonumber) will pass over to the main form and save it to the ProviderID FK in that table and ideally, show it on the main form without having to pick it from the combobox. Is that possible?

You might get some ideas from this re not in list

is your subform Linked to the combo?
you can create a combo with "<new provider>" on it.
use union query as the row source of your combo.

SELECT ProviderID, Provider FROM tblProvider
UNION ALL
SELECT TOP 1 0, "<New Provider>" FROM tblProvider


now on AfterUpdate Event of the combo, just
check if <New Provider> is selected:

Private Sub combo_AfterUpdate()
If Me.combo = 0 Then
' open your new provider form here
End if
End Sub


'''
now when you add record to the New Provider
form, you must capture the ProviderID on BeforeUpdate
Event of the form, so that later you can
requery to combobox, and put the address in the
subform:

Private varProviderID

Private Sub Form_BeforeUpdate()
varProviderID = Me.ProviderID
End Sub

on the unload of the New Provider form, you requery
the combo, and set its value to varProviderID:


Private Sub Form_Unload(Cancel As Integer)
Forms!yourMainForm!comboName.Requery
Forms!yourMainForm!comboName = varProviderID
End Sub

Thank you both! I went the easier route jdraw posted for now because I'm in a time crunch, but will definitely try out the other one when I get a chance. For now it is working by linking my Add New Provider form to the Edit List button. Super easy. Thanks!