Not In List - pass value from one form to another 1

[tlafferty]

Hi -
I have a form named People connected to a table named People. On that form, there is a combo box named Company that has as its data source this expression to pull values from the Companies table:

SELECT [Companies].[Company_ID], [Companies].[Company] FROM Companies;

When a Company is entered in the combo box that is not in the list, I use this code to open the Companies form:

DoCmd.OpenForm "Companies", acNormal, "", "", , acNormal

There are four things I'd like to accomplish:
1. Suppress the validation message, "The text you entered is not an item in the list."
2. Open the Companies form in data entry mode.
3. Pass the value from the Company combo box on the People form to the Company text box on the Companies form.
4. On closing the Companies form, refresh the data source of the Company combo box on People form so that the new entry becomes part of the list and return focus to the Company combo box on the People form.

Also, if this isn't best practice, please feel free to make a better suggestion. Thanks in advance for your help!

 

[PHV]

Have a look at the NotInList event procedure of the combo.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

Take a look here.

http://www.databasedev.co.uk/not_in_list.html