NotInList event?

[Pieter Linden]

Just got done reading some of ADH2000 about comboboxes and the
NotInList event... Doesn't look like it's possible to tweak the
behavior of this so that I can have a non-text bound column, can I?
Say I have a Customers--Invoices relationship, and I want to be able
to pick the Customer's name from a combobox. (keeps people from
entering the same text - or worse, just almost the same - a zillion
times). So I create a CustomerID, enforce uniqueness CustomerName
field, and away I go... but what if I want to have my cake and eat it
too? Is there a way to allow a non-text bound column and then use
LimitToList?

I'll give an example in case what I'm saying isn't making sense...
Say I have a table of customers, (CustomerID,CustomerName). For
simplicity's sake, say that CustomerName is unique, and CustomerID
just acts as a surrogate for it and I store that because it's
easier... Can I still use some kind of LimitToList on my cboCustomer
combobox and have it work, or do I have to bind to the CustomerName
field in my table? (So it stores the ID, not the text value)

Thanks,

Pieter

[Bruce M. Thompson]

Response below.

Just got done reading some of ADH2000 about comboboxes and the
NotInList event... Doesn't look like it's possible to tweak the
behavior of this so that I can have a non-text bound column, can I?
Say I have a Customers--Invoices relationship, and I want to be able
to pick the Customer's name from a combobox. (keeps people from
entering the same text - or worse, just almost the same - a zillion
times). So I create a CustomerID, enforce uniqueness CustomerName
field, and away I go... but what if I want to have my cake and eat it
too? Is there a way to allow a non-text bound column and then use
LimitToList?

I'll give an example in case what I'm saying isn't making sense...
Say I have a table of customers, (CustomerID,CustomerName). For
simplicity's sake, say that CustomerName is unique, and CustomerID
just acts as a surrogate for it and I store that because it's
easier... Can I still use some kind of LimitToList on my cboCustomer
combobox and have it work, or do I have to bind to the CustomerName
field in my table? (So it stores the ID, not the text value)

You will simply need to grab the ID value (regardless of whether you add a
record by code or open a form for the user to enter the data) and assign that
value in your "NotIInList" event procedure to the combo box's "Value" prior to
setting Response to "acDataErrAdded", which will cause the combo box to requery.

--
Bruce M. Thompson
bt******@mvps.org (See the Access FAQ at http://www.mvps.org/access)

NO Email Please. Keep all communications

within the newsgroups so that all might benefit.<<

[Albert D. Kallal]

"Pieter Linden" <pi********@hotmail.com> wrote in message >

I'll give an example in case what I'm saying isn't making sense...
Say I have a table of customers, (CustomerID,CustomerName). For
simplicity's sake, say that CustomerName is unique, and CustomerID
just acts as a surrogate for it and I store that because it's
easier... Can I still use some kind of LimitToList on my cboCustomer
combobox and have it work, or do I have to bind to the CustomerName
field in my table? (So it stores the ID, not the text value)

What you explain is the standard default behaviour for ms-access combo
boxes. It is most common to store the id, but display, and search via the
text name (it is not clear if that is what you want...but sure makes sense
to me). If you build the combo box with the wizard, it default to your
desired setup. There should be no problem with your storing the id, but
displaying the customer name. This is the standard way the combo box
functions.
--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
ka****@msn.com
http://www.attcanada.net/~kallal.msn

[Bob Quintal]

pi********@hotmail.com (Pieter Linden) wrote in
news:bf**************************@posting.google.c om:

Just got done reading some of ADH2000 about comboboxes and the
NotInList event... Doesn't look like it's possible to tweak
the behavior of this so that I can have a non-text bound
column, can I? Say I have a Customers--Invoices relationship,
and I want to be able to pick the Customer's name from a
combobox. (keeps people from entering the same text - or
worse, just almost the same - a zillion times). So I create a
CustomerID, enforce uniqueness CustomerName field, and away I
go... but what if I want to have my cake and eat it too? Is
there a way to allow a non-text bound column and then use
LimitToList?
Store your customer name and ID in a table, which becomes the
rowsource for your combo. Use the NotInList event to add the New
name and create a new UserID to the table when required.

I have the notInList event open a form, pass what the user has
typed via Openargs to a textbox, and allow the user the choice to
Accept or Cancel the New item. If he accepts, I calculate the next
ID, save the record, requery the list and move on. If the user
cancels, he gets a messagebox, stating that the entry was not
accepted, please re-enter.

I'll give an example in case what I'm saying isn't making
sense... Say I have a table of customers,
(CustomerID,CustomerName). For simplicity's sake, say that
CustomerName is unique, and CustomerID just acts as a
surrogate for it and I store that because it's easier... Can I
still use some kind of LimitToList on my cboCustomer combobox
and have it work, or do I have to bind to the CustomerName
field in my table? (So it stores the ID, not the text value)
Limit to list will trigger the notinlist event if your unbound
description isn't one of the existing values.


Thanks,

Pieter