Not In List - carrying over data to form

[Niniel]

Hello,

I use a combo box on a form, and I have programmed the Not In List event so
that if th user enters something new, a form will pop up where new
information can be entered.
It works nicely, except ideally, the data that was originally entered into
the combo box would be carried over to the pop-up form so that the user
wouldn't have to type it again.
Does anybody know how to do that?

Also, I noticed that if what I type into the form [ok, the field that
corresponds to the combo box] is not the same as what I had originally
entered into the combo box, then upon saving and closing the form, I get an
error message stating that the content of the combo box is not in the list.
The newly entered data is available, though.
What is a good way to handle this? I thought that maybe wiping the combo box
may work, or would importing back the information from the corresponding
field on the form be better?
How would that have to be done?

Here is what I have so far:

Private Sub LocationID_NotInList(NewData As String, Response As Integer)

' Opens form to enter new location

Dim stDocName As String
stDocName = "frmNewLocation"

DoCmd.OpenForm stDocName, acNormal, , , acFormAdd, acDialog

Response = acDataErrAdded

End Sub

Thank you.

 

[Klatuu]

To carry the value of the combo into the other form, use the OpenArgs
argument of the OpenForm method to pass the value to the form:
DoCmd.OpenForm "AddNewForm", , ,NewData

Then in the Load event of the form:
If Not IsNull(Me.OpenArgs) Then
Me.txtSomeControl = Me.OpenArgs
End If

Make the pop up form Modal so the code in the calling form will wait until
entry is completed and the form is closed, then requery the combo to get the
new value in the list:

Me.MyCombo.Requery

 

[Niniel]

Thank you very much, that worked nicely.

I ended up modifying my original code only slightly

Private Sub LocationID_NotInList(NewData As String, Response As Integer)

' Opens form to enter new location

Dim stDocName As String
stDocName = "frmNewLocation"

DoCmd.OpenForm stDocName, acNormal, , , acFormAdd, acDialog, NewData

Response = acDataErrAdded

End Sub

because acDialog already makes the form modal, and Response=acDataErrAdded
already does the requerying.

Do you also have a suggestion for how to get rid of the "not in list"
message that I get if I cancel out of the form that adds new values, or if
the text that was originally entered into the combo box and passed onto the
form is modified in the form?

 

[Klatuu]

Great! I knew we could get through it.

I do know that
Response = acDataErrContinue
Will supress the default error message; however, since you have gone out to
another form, you will need to have a way for frmNewLocation to tell the
current form it canceled out. One technique to do this is to create a
control on your form that will tell you what you want to assign to Response.
You might consider a text box. You can make it invisible so the user never
sees it.

Then in the code ofr frmNewLocation, based on what you want to do, set the
text box to the value you need:
If SomeCondtion Then
Forms!frmTheOriginalForm!chkRespond = acDataErrAdded
Else
Forms!frmTheOriginalForm!txtRespond = acDataErrContinue
End If

And in the code you posted replace
Response = acDataErrAdded
With
Response = Me.txtRespond

 

[Niniel]

Oh, brilliant!
This works like a charm, too.

I made a minor modification - instead of the "if" statement you suggested,
I added Forms!frmTheOriginalForm!txtRespond = acDataErrContinue to the
cancel button, and Forms!frmTheOriginalForm!txtRespond = acDataErrAdded
to the save button.

Thanks a lot.

Now the only time I'm still getting a "not in list" message is when I don't
use the text string that was originally entered into the combo box. I can add
to it, but when I replace it with something completely different, the message
pops up.
But I can live with that.

Unless of course you know how to fix that as well.