Heenan, Lambert
Lambert.Heenan at AIG.com
Thu Dec 13 08:22:58 CST 2007
That's the odd thing. In the XP Help (yes I'm looking at the VBA Help, not
Access Help) all I see is the entry below. This is just a nice little story
about the event, with zero examples, not even a mention of what the events
parameters are about. ???!@@!
Lambert
#### XP VBA Help Entry Follows ####
NotInList Event
See Also Applies To Example Specifics
The NotInList event occurs when the user enters a value in the text box
portion of a combo box that isn't in the combo box list.
Remarks
The NotInList event applies only to controls on a form, not controls on a
report.
To run a macro or event procedure when this event occurs, set the
OnNotInList property to the name of the macro or to [Event Procedure].
This event enables the user to add a new value to the combo box list.
The LimitToList property must be set to Yes for the NotInList event to
occur.
The NotInList event doesn't trigger the Error event.
The NotInList event occurs for combo boxes whose LimitToList property is set
to Yes, after you enter a value that isn't in the list and attempt to move
to another control or save the record. The event occurs after all the Change
events for the combo box.
When the AutoExpand property is set to Yes, Microsoft Access selects
matching values in the list as the user enters characters in the text box
portion of the combo box. If the characters the user types match the first
characters of a value in the list (for example, the user types "Smith" and
"Smithson" is a value in the list), the NotInList event will not occur when
the user moves to another control or saves the record. However, the
characters that Microsoft Access adds to the characters the user types (in
the example, "son") are selected in the text box portion of the combo box.
If the user wants the NotInList event to fire in such cases (for example,
the user wants to add the new name "Smith" to the combo box list), the user
can enter a SPACE, BACKSPACE, or DEL character after the last character in
the new value.
When the LimitToList property is set to Yes and the combo box list is
dropped down, Microsoft Access selects matching values in the list as the
user enters characters in the text box portion of the combo box, even if the
AutoExpand property is set to No. If the user presses ENTER or moves to
another control or record, the selected value appears in the combo box. In
this case, the NotInList event will not fire. To allow the NotInList event
to fire, the user should not drop down the combo box list.
#### XP VBA Help End ####
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo
Sent: Wednesday, December 12, 2007 5:30 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Requery on setting row source or record source
I was using the help in A2002-2003
The full entry follows below:
Max
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert
Sent: Wednesday, December 12, 2007 10:11 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Requery on setting row source or record source
Quite so. But I'm damned if I could find that in the XP help. Access 97 help
was much more helpful! :-)
NotInList Event
See AlsoApplies ToExampleSpecificsThe NotInList event occurs when the user
enters a value in the text box portion of a combo box that isn't in the
combo box list.
Private Sub object_NotInList(NewData As String, Response As Integer)
Object The name of a combo box control.
NewData A string that Microsoft Access uses to pass the text the user
entered in the text box portion of the combo box to the event procedure.
Response The setting indicates how the NotInList event was handled. The
Response argument can be one of the following intrinsic constants:
Constant Description
acDataErrDisplay (Default) Displays the default message to the user. You can
use this when you don't want to allow the user to add a new value to the
combo box list.
acDataErrContinue Doesn't display the default message to the user. You can
use this when you want to display a custom message to the user. For example,
the event procedure could display a custom dialog box asking if the user
wanted to save the new entry. If the response is Yes, the event procedure
would add the new entry to the list and set the Response argument to
acDataErrAdded. If the response is No, the event procedure would set the
Response argument to acDataErrContinue.
acDataErrAdded Doesn't display a message to the user but enables you to add
the entry to the combo box list in the NotInList event procedure. After the
entry is added, Microsoft Access updates the list by requerying the combo
box. Microsoft Access then rechecks the string against the combo box list,
and saves the value in the NewData argument in the field the combo box is
bound to. If the string is not in the list, then Microsoft Access displays
an error message.
Remarks
The NotInList event applies only to controls on a form, not controls on a
report.
To run a macro or event procedure when this event occurs, set the
OnNotInList property to the name of the macro or to [Event Procedure].
This event enables the user to add a new value to the combo box list.
The LimitToList property must be set to Yes for the NotInList event to
occur.
The NotInList event doesn't trigger the Error event.
The NotInList event occurs for combo boxes whose LimitToList property is set
to Yes, after you enter a value that isn't in the list and attempt to move
to another control or save the record. The event occurs after all the Change
events for the combo box.
When the AutoExpand property is set to Yes, Microsoft Access selects
matching values in the list as the user enters characters in the text box
portion of the combo box. If the characters the user types match the first
characters of a value in the list (for example, the user types "Smith" and
"Smithson" is a value in the list), the NotInList event will not occur when
the user moves to another control or saves the record. However, the
characters that Microsoft Access adds to the characters the user types (in
the example, "son") are selected in the text box portion of the combo box.
If the user wants the NotInList event to fire in such cases (for example,
the user wants to add the new name "Smith" to the combo box list), the user
can enter a SPACE, BACKSPACE, or DEL character after the last character in
the new value.
When the LimitToList property is set to Yes and the combo box list is
dropped down, Microsoft Access selects matching values in the list as the
user enters characters in the text box portion of the combo box, even if the
AutoExpand property is set to No. If the user presses ENTER or moves to
another control or record, the selected value appears in the combo box. In
this case, the NotInList event will not fire. To allow the NotInList event
to fire, the user should not drop down the combo box list.
Macro
You can use a NotInList macro to add a new value to the combo box list. When
the NotInList event occurs, open a custom dialog box and set the value of
one of its controls to the new value. This control should be bound to the
field in the table or query that is the source for the combo box. Save the
record in the custom dialog box, then requery the combo box.
You can't use the CancelEvent action in a NotInList macro.
Example
The following example uses the NotInList event to add an item to a combo
box.
To try this example, create a combo box called Colors on a form. Set the
combo box's LimitToList property to Yes. To populate the combo box, set the
combo box's RowSourceType property to Value List, and supply a list of
values separated by semicolons as the setting for the RowSource property.
For example, you might supply the following values as the setting for this
property: Red; Green; Blue.
Next add the following event procedure to the form. Switch to Form view and
enter a new value in the text portion of the combo box.
Private Sub Colors_NotInList(NewData As String, _
Response As Integer)
Dim ctl As Control
' Return Control object that points to combo box.
Set ctl = Me!Colors
' Prompt user to verify they wish to add new value.
If MsgBox("Value is not in list. Add it?", _
vbOKCancel) = vbOK Then
' Set Response argument to indicate that data
' is being added.
Response = acDataErrAdded
' Add string in NewData argument to row source.
ctl.RowSource = ctl.RowSource & ";" & NewData
Else
' If user chooses Cancel, suppress error message
' and undo changes.
Response = acDataErrContinue
ctl.Undo
End If
End Sub
Note The above example adds an item to an unbound combo box. When you add an
item to a bound combo box, you add a value to a field in the underlying data
source. In most cases you can't simply add one field in a new record-
depending on the structure of data in the table, you probably will need to
add one or more fields to fulfill data requirements. For instance, a new
record must include values for any fields comprising the primary key. If you
need to add items to a bound combo box dynamically, you must prompt the user
to enter data for all required fields, save the new record, and then requery
the combo box to display the new value.
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com