duplicates in combo boxes

[Guest]

I have a simple reference library database based on one table. There is a
combo box where the user is to select a topic and the publications that I
have assigned to this topic are displayed in a sub form. The problem is that
I want the combo box to list each topic just once but it doesn't, it has an
entry for that topic for each publication. i.e one topic is 'housing' and
there are 6 items in the library that come under this so the combo box lists'
housing' 6 times. I have tried adding SELECT DISTINCT
to the code in the Rowsource but this doesn't do anything. Any suggestions?
This is my Rowsource code..

SELECT DISTINCT [tblReference Libary].[Reference LibraryID], [tblReference
Libary].Topic
FROM [tblReference Libary];

 

[SusanV]

Hi Jo,

You need to remove the ID field from the source query, as the ID *is*
distinct for every record and will cause all records to show. Try:
SELECT DISTINCT [tblReference Libary].Topic FROM [tblReference Libary];

Be sure that the Bound Column property is set to 1 as well (it's probably 2
now, given your Row source statement)

 

[Guest]

I did what you suggested but then everything disapears from my combo box!
What will sort this out?

 

[SusanV]

What was the Bound Column property before?

Probably the easiest way to fix this is to delete the control and re-add it
using the wizard. Don't include the ID in the control this time, and modify
the RowSource to use Distinct as you did before.

I did what you suggested but then everything disapears from my combo box!
What will sort this out?

I have a simple reference library database based on one table. There is
a
combo box where the user is to select a topic and the publications that I
have assigned to this topic are displayed in a sub form. The problem is
that
I want the combo box to list each topic just once but it doesn't, it has
an
entry for that topic for each publication. i.e one topic is 'housing'
and
there are 6 items in the library that come under this so the combo box
lists'
housing' 6 times. I have tried adding SELECT DISTINCT
to the code in the Rowsource but this doesn't do anything. Any
suggestions?
This is my Rowsource code..

SELECT DISTINCT [tblReference Libary].[Reference LibraryID],
[tblReference
Libary].Topic
FROM [tblReference Libary];

 

[fredg]

I did what you suggested but then everything disapears from my combo box!
What will sort this out?

I have a simple reference library database based on one table. There is a
combo box where the user is to select a topic and the publications that I
have assigned to this topic are displayed in a sub form. The problem is that
I want the combo box to list each topic just once but it doesn't, it has an
entry for that topic for each publication. i.e one topic is 'housing' and
there are 6 items in the library that come under this so the combo box lists'
housing' 6 times. I have tried adding SELECT DISTINCT
to the code in the Rowsource but this doesn't do anything. Any suggestions?
This is my Rowsource code..

SELECT DISTINCT [tblReference Libary].[Reference LibraryID], [tblReference
Libary].Topic
FROM [tblReference Libary];

Did you change the column Count from 2 to 1?
Did you change the width of the first column to 1"?

 

[Guest]

Thanks SusanV and Fredg, I have now got rid of the repeated entries in my
combo box but now when a topic is selected the corresponding publication
titles do not display in the table below. I will try and figure this out -
probably something to do with the after update function I guess...

I may post again if I get stuck!

Many thanks
Jo

I did what you suggested but then everything disapears from my combo box!
What will sort this out?

I have a simple reference library database based on one table. There is a
combo box where the user is to select a topic and the publications that I
have assigned to this topic are displayed in a sub form. The problem is that
I want the combo box to list each topic just once but it doesn't, it has an
entry for that topic for each publication. i.e one topic is 'housing' and
there are 6 items in the library that come under this so the combo box lists'
housing' 6 times. I have tried adding SELECT DISTINCT
to the code in the Rowsource but this doesn't do anything. Any suggestions?
This is my Rowsource code..

SELECT DISTINCT [tblReference Libary].[Reference LibraryID], [tblReference
Libary].Topic
FROM [tblReference Libary];

Did you change the column Count from 2 to 1?
Did you change the width of the first column to 1"?

 

[SusanV]

You need to refresh or requery the form...

Thanks SusanV and Fredg, I have now got rid of the repeated entries in my
combo box but now when a topic is selected the corresponding publication
titles do not display in the table below. I will try and figure this
out -
probably something to do with the after update function I guess...

I may post again if I get stuck!

Many thanks
Jo

I did what you suggested but then everything disapears from my combo
box!
What will sort this out?

:

I have a simple reference library database based on one table. There
is a
combo box where the user is to select a topic and the publications
that I
have assigned to this topic are displayed in a sub form. The problem
is that
I want the combo box to list each topic just once but it doesn't, it
has an
entry for that topic for each publication. i.e one topic is 'housing'
and
there are 6 items in the library that come under this so the combo box
lists'
housing' 6 times. I have tried adding SELECT DISTINCT
to the code in the Rowsource but this doesn't do anything. Any
suggestions?
This is my Rowsource code..

SELECT DISTINCT [tblReference Libary].[Reference LibraryID],
[tblReference
Libary].Topic
FROM [tblReference Libary];

Did you change the column Count from 2 to 1?
Did you change the width of the first column to 1"?