How to show only one occurence of name in combo box

  • Thread starter Thread starter Tony Girgenti
  • Start date Start date
T

Tony Girgenti

Hello.

I have a form with a combo box that has a a Rwo source type of Table/Query
and a Row Source of a table with customer names displayed in the combo box.
The table has duplicate customer names.

How do i only display one occurance of the customer name in the combo box
when they use the drop down arrow ?

Thanks,
Tony
 
Hello.

I have a form with a combo box that has a a Rwo source type of Table/Query
and a Row Source of a table with customer names displayed in the combo box.
The table has duplicate customer names.

How do i only display one occurance of the customer name in the combo box
when they use the drop down arrow ?

Thanks,
Tony
Click to expand...

Change the Row Source of the Combo box from
Select TableName.CustomerName ... etc.
To
Select Distinct TableName.CustomerName ... etc .
 
Hello Fredg.

My Row Source only has the name of the table. The bound column tells it
which column of the table to use.

Thanks,
Tony
 
Then change your row source to be a SQL statement, like Fred suggested.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Tony Girgenti said:
Hello Fredg.

My Row Source only has the name of the table. The bound column tells it
which column of the table to use.

Thanks,
Tony
Click to expand...
 
Hello Douglas.

I tried that. Now it shows no records.
SELECT DISTINCT [PENDING ORDERS].[COMPANY NAME] FROM [PENDING ORDERS];

Thanks.
Tony

Douglas J. Steele said:
Then change your row source to be a SQL statement, like Fred suggested.
Click to expand...
 
Hello Douglas.

I tried that. Now it shows no records.
SELECT DISTINCT [PENDING ORDERS].[COMPANY NAME] FROM [PENDING ORDERS];

Thanks.
Tony

Douglas J. Steele said:
Then change your row source to be a SQL statement, like Fred suggested.
Click to expand...
Click to expand...

One way to determine if the SQL is correct is to click on the little
button with the 3 dots on the Row Source line. Run the query. If it
returns the correct records, then the SQL is correct and your problem
lies elsewhere.
Perhaps you didn't set the column width property to a value more than
Zero. Or you have an incorrect Column count set.

Is the name of the table "Pending Orders"?
Is the name of the field "Customer Name"?
Is the Column count property set to 1?
Is the column width property set to about 1"?

If so that should work.
 
Hey Tony,

Wouldn't having duplicate customers be an indication of redunt data, which
means you may have normalization problems? Any way, where did you put your
SELECT DISTINCT statement at?

Shane



Tony said:
Hello Douglas.

I tried that. Now it shows no records.
SELECT DISTINCT [PENDING ORDERS].[COMPANY NAME] FROM [PENDING ORDERS];

Thanks.
Tony
Then change your row source to be a SQL statement, like Fred suggested.
Click to expand...
[quoted text clipped - 25 lines]
Click to expand...
 
Hi Fred.

The query runs OK when i do as you suggested.

Is the name of the table "Pending Orders"? You can see what this is
Is the name of the field "Customer Name"? You can see what this is
Is the Column count property set to 1? = 2
Is the column width property set to about 1"?= 0";2.0521"
Thanks,
Tony
Click to expand...
fredg said:
Hello Douglas.

I tried that. Now it shows no records.
SELECT DISTINCT [PENDING ORDERS].[COMPANY NAME] FROM [PENDING ORDERS];

Thanks.
Tony

Douglas J. Steele said:
Then change your row source to be a SQL statement, like Fred suggested.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Hello Fredg.

My Row Source only has the name of the table. The bound column tells
it
which column of the table to use.

Thanks,
Tony

On Tue, 20 Dec 2005 16:20:11 -0500, Tony Girgenti wrote:

Hello.

I have a form with a combo box that has a a Rwo source type of
Table/Query
and a Row Source of a table with customer names displayed in the
combo
box.
The table has duplicate customer names.

How do i only display one occurance of the customer name in the combo
box
when they use the drop down arrow ?

Thanks,
Tony

Change the Row Source of the Combo box from
Select TableName.CustomerName ... etc.
To
Select Distinct TableName.CustomerName ... etc .
Click to expand...
Click to expand...

One way to determine if the SQL is correct is to click on the little
button with the 3 dots on the Row Source line. Run the query. If it
returns the correct records, then the SQL is correct and your problem
lies elsewhere.
Perhaps you didn't set the column width property to a value more than
Zero. Or you have an incorrect Column count set.

Is the name of the table "Pending Orders"?
Is the name of the field "Customer Name"?
Is the Column count property set to 1?
Is the column width property set to about 1"?

If so that should work.
Click to expand...
 
Hi Shane.

It's not really duplicate customers, it just duplicate company names.

I put SELECT DISTINCT [PENDING ORDERS].[COMPANY NAME] FROM [PENDING ORDERS];
in the Row Source field.

Thanks,
Tony

Shane S via AccessMonster.com said:
Hey Tony,

Wouldn't having duplicate customers be an indication of redunt data, which
means you may have normalization problems? Any way, where did you put
your
SELECT DISTINCT statement at?

Shane



Tony said:
Hello Douglas.

I tried that. Now it shows no records.
SELECT DISTINCT [PENDING ORDERS].[COMPANY NAME] FROM [PENDING ORDERS];

Thanks.
Tony
Then change your row source to be a SQL statement, like Fred suggested.
Click to expand...
[quoted text clipped - 25 lines]
To
Select Distinct TableName.CustomerName ... etc .
Click to expand...
Click to expand...
Click to expand...
 
So after doing what Fred suggested you said that it does show Ok in the query,
Do you have it working now?

If no, I might would suggest that you check your column count and make sure
it's right and if it is then make sure you column widths are correct.
Especially the one that your wanting displayed in your combo box.

HTH,
Shane

Tony said:
Hi Shane.

It's not really duplicate customers, it just duplicate company names.

I put SELECT DISTINCT [PENDING ORDERS].[COMPANY NAME] FROM [PENDING ORDERS];
in the Row Source field.

Thanks,
Tony
Hey Tony,
Click to expand...
[quoted text clipped - 18 lines]
Click to expand...
 
Shane.

After doing what Fred suggested, it shows nothing in the dropdown.

It worked fine when it was showing the duplicates. I want ot get rid of the
duplicates.

Row source type = Table/Query
Row source = SELECT DISTINCT [PENDING ORDERS].[COMPANY NAME] FROM [PENDING
ORDERS];
Column count = 2
Column widths = 0";2.0521"
List Width = 0";2.0521"

Let me know if you need anything else.

Thanks,
Tony


Shane S via AccessMonster.com said:
So after doing what Fred suggested you said that it does show Ok in the query,
Do you have it working now?

If no, I might would suggest that you check your column count and make sure
it's right and if it is then make sure you column widths are correct.
Especially the one that your wanting displayed in your combo box.

HTH,
Shane

Tony said:
Hi Shane.

It's not really duplicate customers, it just duplicate company names.

I put SELECT DISTINCT [PENDING ORDERS].[COMPANY NAME] FROM [PENDING ORDERS];
in the Row Source field.

Thanks,
Tony
Hey Tony,
Click to expand...
[quoted text clipped - 18 lines]
To
Select Distinct TableName.CustomerName ... etc .
Click to expand...
Click to expand...
Click to expand...
 
Shane.

After doing what Fred suggested, it shows nothing in the dropdown.

It worked fine when it was showing the duplicates. I want ot get rid of the
duplicates.

Row source type = Table/Query
Row source = SELECT DISTINCT [PENDING ORDERS].[COMPANY NAME] FROM [PENDING
ORDERS];
Column count = 2
Column widths = 0";2.0521"
List Width = 0";2.0521"

Let me know if you need anything else.

Thanks,
Tony

Shane S via AccessMonster.com said:
So after doing what Fred suggested you said that it does show Ok in the query,
Do you have it working now?

If no, I might would suggest that you check your column count and make sure
it's right and if it is then make sure you column widths are correct.
Especially the one that your wanting displayed in your combo box.

HTH,
Shane

Tony said:
Hi Shane.

It's not really duplicate customers, it just duplicate company names.

I put SELECT DISTINCT [PENDING ORDERS].[COMPANY NAME] FROM [PENDING ORDERS];
in the Row Source field.

Thanks,
Tony

Hey Tony,

[quoted text clipped - 18 lines]
To
Select Distinct TableName.CustomerName ... etc .
Click to expand...
Click to expand...
Click to expand...

Regarding :
Column count = 2
Column widths = 0";2.0521"
List Width = 0";2.0521"

You are hiding the first (and only) column.
Setting a column Width to 0" means it doesn't show. And you only have
that one column. That's why you can't see it.

Change the above properties to:
Column count = 1
Column width = 2.0521" (or any suitable width that will show the
column)
List Width = Auto (or any suitable width to display the dropped down
columns.
 
Hi Fred.

Well, after making your changes, the dropdown list works just fine(no
duplicates), but when i select from the dropdown list, it doesn't put it into
the combo box.

Thanks,
Tony


fredg said:
Shane.

After doing what Fred suggested, it shows nothing in the dropdown.

It worked fine when it was showing the duplicates. I want ot get rid of the
duplicates.

Row source type = Table/Query
Row source = SELECT DISTINCT [PENDING ORDERS].[COMPANY NAME] FROM [PENDING
ORDERS];
Column count = 2
Column widths = 0";2.0521"
List Width = 0";2.0521"

Let me know if you need anything else.

Thanks,
Tony

Shane S via AccessMonster.com said:
So after doing what Fred suggested you said that it does show Ok in the query,
Do you have it working now?

If no, I might would suggest that you check your column count and make sure
it's right and if it is then make sure you column widths are correct.
Especially the one that your wanting displayed in your combo box.

HTH,
Shane

Tony Girgenti wrote:
Hi Shane.

It's not really duplicate customers, it just duplicate company names.

I put SELECT DISTINCT [PENDING ORDERS].[COMPANY NAME] FROM [PENDING ORDERS];
in the Row Source field.

Thanks,
Tony

Hey Tony,

[quoted text clipped - 18 lines]
To
Select Distinct TableName.CustomerName ... etc .
Click to expand...
Click to expand...

Regarding :
Column count = 2
Column widths = 0";2.0521"
List Width = 0";2.0521"

You are hiding the first (and only) column.
Setting a column Width to 0" means it doesn't show. And you only have
that one column. That's why you can't see it.

Change the above properties to:
Column count = 1
Column width = 2.0521" (or any suitable width that will show the
column)
List Width = Auto (or any suitable width to display the dropped down
columns.
Click to expand...
 
Bound column should now be 1
And
The Control Source should be the field name in the table or whatever
that you are editing
 
Hi Ron.

When i do that, the dropdown list shows no duplicates, but when i select
something in the dropdown list, it just beeps and does nothing. It doesn't
even close the dropdown list.

Thanks,
Tony
 
Two places I can think of looking now.

1) Do you perhaps have the filed 'locked' (On the data Tab for the
field itself)? When I set this to locked on a test, the box stays open
and nothing happens. My test just now was with an unbound combo box
with no control source.

2) If the combo is for a real field which is part of a table/query,
then the question is for the data source for the whole form, are you
allowing edits - again data tab for form record source. Or are you
perhaps calling the form in a read only mode (if you used a
docmd.openform to call it one of the options is readonly.)

3) If it is bound to a field, is it the right field? If the wrong field
then there could be a type difference causing an error.

Ron
 
Back
Top