How to defer NotInList handling until record is saved

[phuile]

I have a combobox that allows users to select data from a lookup table.
When the user enters data that is not in the dropdown list the
NotInList event fires so I can provide code to add the new data to the
lookup table and repopulate the list.

However, I don't want to process the data as each combobox is updated.
I want to wait until the user has updated the entire record before
processing the fields that fired the NotInList events. (I can track
which fields these are by setting a flag when the event occurs.)

I might need to do this, for example, because I need the values from
subsequent fields to properly add the data to the lookup tables.

How can I do this? I need to defeat the NotInList event on each
individual combobox so that the user can move on to the subsequent
fields. Irritatingly, the combobox dropdown automatically opens after
the NotInList event terminates even if the event response is set to
acDataErrContinue. And the field value has to be cleared before the
next field can be selected.

Does anyone have any ideas on how to work around this problem?

Thanks.
Petre.

 

[Allen Browne]

The NotInList event cannot be delayed until the record is saved, so you will
have to find another approach.

If the Bound Column of your combo is not zero-width (typically if it is not
using hidden autonumbers), you could set the combo's LimitToList property to
No, so that it accepts values not in the list. You could then perform
whatever processing is necessary in (say) Form_BeforeUpdate to ensure the
related record exists before this record is saved.

 

[Jeff Boyce]

As Allen indicates, I, too am not aware of a way to tell NotInList to
"wait".

I'm curious, too. If you have a control on your form that is bound to a
field that is important enough to need to be able to add a row to the
underlying table, why would you NOT want to record the new row before
proceeding.

If you are saying that the form contains many potential values, related to
each other in complex ways, maybe you need to unbind the form entirely. You
could then allow NotInList events and any other data entry, doing your final
validation checks after all the fields had been filled. Your final action
would be to save (INSERT) a new record to the appropriate table.