[AccessD] Requery on setting row source or record source

Max Wanadoo max.wanadoo at gmail.com
Wed Dec 12 16:29:51 CST 2007


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. 




More information about the AccessD mailing list