When does Combo NotInList Fire?

[Warren Bell]

When I have the Combo Box LimitToList set to Yes,
NotInList will fire and then I get a warning message. When
I have LimitToList set to No, NotInList will not fire. I
need it to fire when LimitToList is set to No so I can add
the new value to a table. How do I do this?

Thanks,

Warren Bell

 

[Ken Snell]

No, you want to set the LimitToList property to Yes if you want the
NotInList event to "fire". Then you use code in that event to add the new
data value to your combo box's RowSource.

See http://www.mvps.org/access/forms/frm0015.htm for code that does this.

 

[Marshall Barton]

When I have the Combo Box LimitToList set to Yes,
NotInList will fire and then I get a warning message. When
I have LimitToList set to No, NotInList will not fire. I
need it to fire when LimitToList is set to No so I can add
the new value to a table.

Generally, you would have LimitToList set to Yes and use the
NotInList event to add the new data to the combo box's Row
Source table. Just be sure to set the Response argument
appropriately. There should be an example of this in Help.

 

[Warren Bell]

I have tried it that way, but I get an Access warning
message telling me that I have to select a value from the
list. Is there a way to override the warning?

 

[Ken Snell]

Did you look at the website that I posted? It shows the correct way to do
this. What it contains is the setting of the variable "Response" to the
value of "acDataErrAdded" which is how this message doesn't show up.

If you don't use code in the NotInList event, then yes ACCESS will display
the warning message. You must use VBA code routine to do what you seek.

Again, look at this website:
http://www.mvps.org/access/forms/frm0015.htm

 

[Albert D. Kallal]

You can use the following code. It will get the users new record, and then
does a requery on the combo box for you.


If MsgBox("Add new source type?", vbOKCancel) = vbOK Then

DoCmd.OpenForm "frmAddSource", acNormal, , , acFormAdd, acDialog, NewData

Response = acDataErrAdded

End If

The trick in the above is to set the Response as above, and it will then
re-query the combo box for you.

I also pass "newdata" to the form so the what you typed in is already set in
the field in the form that loads. So, in the forms on-load event, you go:

if isnull(me.OpenArgs) = false then

me!SourceType = me.OpenArgs

end if

That is all you need. It is VERY little code, and it works smooth as butter.

As mentioned, setting the Response is the trick, as then you get no error
messages, and ms-access even does a re-query for you.

 

[Warren Bell]

I still can not get Access to not display the warning.
This is what I have.

Private Sub Combo76_NotInList(NewData As String, Response
As Integer)
MsgBox NewData, vbOKOnly
Response = acDataErrAdded
End Sub

What am I doing wrong?

Warren Bell

 

[Warren Bell]

Figured it out, I need to update the rowsource data.

Thanks

Warren Bell