NotInList event of a ComboBox

[David C. Holley]

I have a form in which a user enters a person's name into a comboBox. If
the person's name does not exist in the list, I've got code in the
NotInList event that adds the person's name to the list and then
supresses the error.

*TAKE NOTE*
The names in the comboBox are formated as LastName, First Name (e.g.
'Smith, David')

I have in the NotInList procedure code which detects the existance of a
comma [,] in the newData variable and then processes the newData based
on if the value is LastName/FirstName or FirstName/LastName. (First/Last
is detected by the existance of a space and the lack of a comma.)

*TAKE NOTE*
I am able to successfully create the neccessary records in the
underlying table and have the comboBox requery.

*ACTUAL PROBLEM*
The problem that I have is that if the user enters the name in First
Name/Last Name format [David Smith], the record in the comboBox will
appear as [Smith, David] after its added to the underlying table and the
comobox requeries. How then can I select the new record [Smith, David]?
Using [response = DATA_ERRADDED], Access wants to search for and select
[David Smith] as that is the original value entered.

 

[tina]

Access Help says that the acDataErrAdded response does the following:
"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."

based on the above, try adding something to your "detects the existance of a
comma [,] in the newData variable..." code sequence, to change the value of
the NewData variable to the appropriate string value.

hth

 

[David C. Holley]

Doesn't work. I tried explicity setting newData to a value that already
exists in the list. Access ignores the value that I explicity set
newData to and searches for the original value. Basically, I need to get
Access to ignore the original value and select another.

Access Help says that the acDataErrAdded response does the following:
"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."

based on the above, try adding something to your "detects the existance of a
comma [,] in the newData variable..." code sequence, to change the value of
the NewData variable to the appropriate string value.

hth

I have a form in which a user enters a person's name into a comboBox. If
the person's name does not exist in the list, I've got code in the
NotInList event that adds the person's name to the list and then
supresses the error.

*TAKE NOTE*
The names in the comboBox are formated as LastName, First Name (e.g.
'Smith, David')

I have in the NotInList procedure code which detects the existance of a
comma [,] in the newData variable and then processes the newData based
on if the value is LastName/FirstName or FirstName/LastName. (First/Last
is detected by the existance of a space and the lack of a comma.)

*TAKE NOTE*
I am able to successfully create the neccessary records in the
underlying table and have the comboBox requery.

*ACTUAL PROBLEM*
The problem that I have is that if the user enters the name in First
Name/Last Name format [David Smith], the record in the comboBox will
appear as [Smith, David] after its added to the underlying table and the
comobox requeries. How then can I select the new record [Smith, David]?
Using [response = DATA_ERRADDED], Access wants to search for and select
[David Smith] as that is the original value entered.

 

[Marshall Barton]

Doesn't work. I tried explicity setting newData to a value that already
exists in the list. Access ignores the value that I explicity set
newData to and searches for the original value. Basically, I need to get
Access to ignore the original value and select another.

David, this is a very tricky thing to do. Any time you save
a value to the combo box's table that is different from the
value the user entered, you run into this issue.

I don't remember the exact details, but it involves your
NotInList procedure setting the combo box's Text property.
This in turn will trigger another NotInList event so the
event procedure will be processing two different values
simultaneously. You need to use a Static flag variable so
the procedure can determine what to do for the different
values.

I don't have the time to search google for a code sample
now, but maybe you can find one before I can get to it.

 

[David C. Holley]

Of course it'd be TO easy for MS to have a .Transform method available!
thpppt!

 

[David C. Holley]

Well, I kind of got it working using by counting the number of times the
notInList event fires. Big ugly though...

 

[Marshall Barton]

Doesn't work. I tried explicity setting newData to a value that already
exists in the list. Access ignores the value that I explicity set
newData to and searches for the original value. Basically, I need to get
Access to ignore the original value and select another.

I searched around Google and came across:

http://groups.google.com/group/comp...s+author:Shears&rnum=1&hl=en#85d5ba2cca84a7a8

http://groups.google.com/group/micr...s*+author:Baron&rnum=6&hl=en#d523b658ea57423f

They are essentially the same method, but I think the first
one is much easier to understand.

 

[David C. Holley]

Thank you. That's got me on the right track. I still think that MS
should provide a comboBox method that forces the box to select a
different value than the original.

Me.cboBox.Transform or Me.cboBox.SelectValue

 

[David C. Holley]

To quote Gene Hackman from 'Young Frankenstein' - 'IT'S ALIVE!'
Frightening how easy it was to implement the first example. Now, if I
can only get the NotInList event to go out and pick up a six-pack for me.

 

[Marshall Barton]

Hey, if NotInList won't go out for a six pack, you could try
finding him a nice girl and see if that makes him more
agreeable ;-)

Glad you got it working.

 

[David C. Holley]

especially if the girl is a MVP?