Using NotInList with Combo Box

[Hilary Ostrov]

Another newbie question!

I have a combo box in a Form, and am using NotInList to Add a new item
to the combo's underlying table. I started from the sample code in
Help (which may or may not have been the right thing to do!), and have
modified to the extent that the code below will open the desired form
to add a new item. But I'd also like to:

1) Close the newly opened form; and

2) refresh the list in the combo box on the first form to show the
newly added item, so the user can select it, then proceed with other
entries in the first form - rather than having to close the first form
and re-open in order to accomplish this! And:

3) Eliminate (or modify) the message boxes that currently pop up;
presumably this can be accomplished somewhere in the else statement,
but I don't know the correct modifications to make!

Here's my code, so far:

===
Private Sub ManageCo_NotInList(NewData As String, Response As Integer)

Dim ctl As Control

' Return Control object that points to combo box.
Set ctl = ManageCo

' Prompt user to verify they wish to add new value.
If MsgBox("This company is not in list. Do you want to add a new
one?", _
vbOKCancel) = vbOK Then

' Open the Add/Edit Management Company Form'

DoCmd.OpenForm "Add/Edit Management Company", acNormal, , ,
acFormAdd, acWindowNormal

Else
' If user chooses Cancel, suppress error message
' and undo changes.
Response = acDataErrContinue
ctl.Undo
End If
End Sub
===

A secondary question, pertaining to the above. Because it *seemed*
like the right thing to do, I did create a Macro:

===
Sub OpenAddManagementCompany()
'Open the Add/Edit Management Company Form'

DoCmd.OpenForm "Add/Edit Management Company"

End Sub
===

However, since this is a procedure that I'd like to be able to invoke
at other times, it seems to make more sense to me to include all the
parameters/arguments (?!) in the macro, and run the Macro from the
NotInList (in this instance).

If my thinking is correct, how should I then modify my NotInList code
to run the macro?

OTOH, if my thinking is completely off-track, either in this regard,
or wrt any part of this particular process, please let me know

Thanks!
hro

 

[Chris Large]

Hi

You need to open the form modal and then set the response
correctly, amend the openform line and add the following
line, like this:-

DoCmd.OpenForm "Add/Edit Management Company", , , ,
acFormAdd, acDialog
Response = acDataErrAdded


hth

Chris