Combo box with unique values?

[Charles W. Stricklin]

Suppose I have a table named 'customers' containing a field named
'custName'. In that table/field I have the following values:

ACME brick
Joe's Crab Shack
ACME brick
Some Guy Printing

What I would like to accomplish is in the form have a combo box with the
following available choices, in order:

ACME brick
Joe's Crab Shack
Some Guy Printing

In other words, each unique customer name sorted alphabetically. If the
user views a record, the correct value is shown, if he/she creates a new
record and chooses one of the already used customer names that record
will contain the choice, or if he/she types a new customer name it is
used instead and added to the combo box for subsequent records.

Should someone help out with this? I have a lot of need for something
like this.

 

[tina]

so you have a tblCustomers. is custName the primary key field in the table?
are there other fields in the table that describe a customer?

and you want to use tblCustomers as the row source for a combo box on your
data entry form. and you want the user to be able to add a new customer to
the combo box list "on the fly".

that is easy enough to do, from a programming standpoint. but you have to
consider that "adding a customer name to the combo box list" really means
adding a new record to tblCustomers. so you have to ask yourself: will the
user have the information at hand to add a "complete" new record to
tblCustomers?

if the answer is No, you need to consider the ramifications to your data, of
having incomplete customer records entered in tblCustomers.

if you're comfortable with having the user add records to tblCustomers, you
can use the following code in the combo box's NotInList event to add new
records, as

'substitute the correct name of the combo box
Private Sub ComboBoxName_NotInList(NewData As String, _
Response As Integer)

'it's a good idea to ask the question, because the user may have
'made a typo, rather than entering a valid new customer name.
If MsgBox("Do you want to add a new customer to the list?", _
vbDefaultButton1 + vbYesNo) = vbYes Then
'substitute the correct form name
DoCmd.OpenForm "frmCustomers", , , , acFormAdd, acDialog
'opening the form as a dialog box suspends this code until the form is
'close, then the code automatically resumes running.
Response = acDataErrAdded
Else
'if the user clicks No in the message box, then the error message
'is suppressed, the invalid entry is erased, and the combo box
'droplist is opened.
Response = acDataErrContinue
'substitute the correct name of the combo box.
Me!ComboBoxName = Null
Me!ComboBoxName.Dropdown
End If

End Sub

suggest you read up on the NotInList Event (not the Property) to better
understand how the available responses work.

hth

 

[charlesstricklin]

tina,

Thanks for the answer, and sorry about the multi-post. I wasn't aware of a
breach of netiqette.

custName is not the primary key field in the table.

No, there are no other tables.

I copied the function, changed the forms and fields and when I try to add
the name I get an error message telling me, "The text you entered isn't an
item in the list."

Did I do something wrong?

 

[tina]

when is the error message occurring? before, or after, you open the
tblCustomers form and add the new record? if necessary, step through the
procedure to find out what line of code triggers the error message. then
post back with the info.

 

[charlesstricklin]

It appears to happen after the subroutine is exited.

 

[tina]

hmmm, hard to tell what the problem is, there are plenty of places to make a
simple mistake. if i can take a look at the setup, i can probably find it
and explain how to fix it. if you want me to, post back here and i'll send
you an email (i usually don't post my email address due to spam miners).

 

[Charles W. Stricklin]

Awww crud! I just realized after you said something about it that I've been
using my real home e-mail address since I got home! Dang! Dang! Dang!

Anyway, yes, please. I'd appreciate the help.

 

[tina]

okay, sending email now. (yeah, i cringed - on your behalf - when i saw the
email address...)