How to get combo box to show only one of each choice and not the .

[Guest]

How to get combo box to show only one of each choice and not the entire
table, and also allow new entries?

 

[Arvin Meyer]

You can't because you are asking 2 different things from the same query. To
show only unique values you need the Distinct keyword or you need to Group
By the common value. Both of those situations don't allow editing because
they don't include the Primary Key or show a Unique row. If you can't a key,
you can't add a new row.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Guest]

Thank you for your fast response. I do not yet have a grasp of the Query and
Source things. I had used Lotus Approach, in which when you placed a combo
box on a form it asked you simply if you wanted only one of each entry to be
shown for you to make your drop down selection from. At the same time you
could enter a new entry to the form and that entry would be in the combo box
list the next time you used it. I have tried to think of a reason where
anyone would want everything in the table to be listed on the combo box drop
down including blank fields to make your choice from, I do not understand why
this is the default way access makes a combo box. I know you can list
specific things in a list box for you to choose from. I know the combo box
can have a list box type list that is added to with new entries, but I do not
know what any of these items are called. Thanks Chaz / Our Shop in Toyland

 

[Rick Brandt]

Thank you for your fast response. I do not yet have a grasp of the
Query and Source things. I had used Lotus Approach, in which when
you placed a combo box on a form it asked you simply if you wanted
only one of each entry to be shown for you to make your drop down
selection from. At the same time you could enter a new entry to the
form and that entry would be in the combo box list the next time you
used it. I have tried to think of a reason where anyone would want
everything in the table to be listed on the combo box drop down
including blank fields to make your choice from, I do not understand
why this is the default way access makes a combo box. I know you can
list specific things in a list box for you to choose from. I know
the combo box can have a list box type list that is added to with new
entries, but I do not know what any of these items are called.
Thanks Chaz / Our Shop in Toyland

You can put a DISTINCT clause on the query you are using as the RowSource and it
will not affect the ability to update the RecordSource of the form.

 

[Arvin Meyer]

Hi Chaz,

The Row Source of a combo box can be a list which is hardcoded in, or a
select statement. If you select all the rows in a table, you can adda record
to that table. It occured to me that you are using the combo with the same
row source as the underlying record. That's a mistake, and you can get what
you want. Use a source table for the data in your combo, but bind the output
to another table. For instance:

Row Source for combo:

tblCustomers
CustomerID
CustomerName

Select CustomerID, CustomerName From tblCustomers Order By
tblCustomers.CustomerName;

Now the value in tblOrders:

OrderID
CustomerID
....

And the form would look like:

cboCustomerID
Bound to field: CustomerID
Columns: 2
BoundColumn: 1
ColumnWidths: 0";1.5"

This would store the CustomerID in the table but display the CustomerName
from the Customers table. It is not a lookup combo, those are not bound to a
field. You would add a new value to the Customers table using the NotInList
event (code in the help files) to open the Customers form. Once entered, you
can resume entering the data for the new order.

I believe there is an example in the Orders forms in the Northwind sample
database that comes with Access.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Arvin Meyer]

You can put a DISTINCT clause on the query you are using as the RowSource and it
will not affect the ability to update the RecordSource of the form.

Actually, it will not allow editing or updating. It is DISTINCTROW which
allows editing, but will not give distinct values, only distinct records.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Rick Brandt]

Actually, it will not allow editing or updating. It is DISTINCTROW which
allows editing, but will not give distinct values, only distinct records.

Yes, but the SQL Statement of a ComboBox or ListBox being editable or not has
nothing to do with whether the RecordSource of the form is editable.

My impression is the OP wants the lists of choices to be distinct and still be
able to edit the data in the form. He should be able to use DISTINCT in his
RowSource without affecting editability of the form.

 

[Doug M]

If you put a combo box which is bound to the field you want the data to go
into, then you can set the rowsource of the combo box to a query that
returns distinct values (Unique Values) and you will get the list you want.
But you can still enter a different value into the field instead of
selecting something from the list.

Doug M