Form drop down list duplicates

  • Thread starter Thread starter KYMailman
  • Start date Start date
K

KYMailman

For an existing database I created a drop down list box on a Form for the
field Author. How do I edit the drop down list to exclude or delete
duplicates?
 
Use a query for the list box row source using Select Distinct. That will
return only one occurance of each author:

SELECT DISTINCT [author] FROM tblAuthors;
 
The more I learn the less I know. I am new to using Access in Office 2003. I
attempted to build a query as follows:
Field: Author

Table: Book List

Criteria: "DISTINCT [Author] FROM tblbooklist"

I must be missing something, because the query opens with nothing in the
box, and when I open the FORM, the drop down list still has multiple entries.
Hope you can help. Thanks John

Klatuu said:
Use a query for the list box row source using Select Distinct. That will
return only one occurance of each author:

SELECT DISTINCT [author] FROM tblAuthors;
--
Dave Hargis, Microsoft Access MVP


KYMailman said:
For an existing database I created a drop down list box on a Form for the
field Author. How do I edit the drop down list to exclude or delete
duplicates?
Click to expand...
Click to expand...
 
I don't know where you put it, but take it out.
Open your form in design view.
Select the combo box.
Open the Properties Dialog.
Put the following in the Row Source Property exactly like this:

SELECT DISTINCT [Author] FROM tblbooklist;

--
Dave Hargis, Microsoft Access MVP


KYMailman said:
The more I learn the less I know. I am new to using Access in Office 2003. I
attempted to build a query as follows:
Field: Author

Table: Book List

Criteria: "DISTINCT [Author] FROM tblbooklist"

I must be missing something, because the query opens with nothing in the
box, and when I open the FORM, the drop down list still has multiple entries.
Hope you can help. Thanks John

Klatuu said:
Use a query for the list box row source using Select Distinct. That will
return only one occurance of each author:

SELECT DISTINCT [author] FROM tblAuthors;
--
Dave Hargis, Microsoft Access MVP


KYMailman said:
For an existing database I created a drop down list box on a Form for the
field Author. How do I edit the drop down list to exclude or delete
duplicates?
Click to expand...
Click to expand...
Click to expand...
 
I followed your instructions and still no luck. I selected the combo box in
design and opened properties. Row Source Type: Table/Query. Row Source: I
entered your statement line. When I returned to Form view and clicked on the
drop button I got the following error message:

The Record Source 'SELECT DISTINCT [Author] FROM tblbooklist' specified on
this form does not exist.

Klatuu said:
I don't know where you put it, but take it out.
Open your form in design view.
Select the combo box.
Open the Properties Dialog.
Put the following in the Row Source Property exactly like this:

SELECT DISTINCT [Author] FROM tblbooklist;

--
Dave Hargis, Microsoft Access MVP


KYMailman said:
The more I learn the less I know. I am new to using Access in Office 2003. I
attempted to build a query as follows:
Field: Author

Table: Book List

Criteria: "DISTINCT [Author] FROM tblbooklist"

I must be missing something, because the query opens with nothing in the
box, and when I open the FORM, the drop down list still has multiple entries.
Hope you can help. Thanks John

Klatuu said:
Use a query for the list box row source using Select Distinct. That will
return only one occurance of each author:

SELECT DISTINCT [author] FROM tblAuthors;
--
Dave Hargis, Microsoft Access MVP


:

For an existing database I created a drop down list box on a Form for the
field Author. How do I edit the drop down list to exclude or delete
duplicates?
Click to expand...
Click to expand...
Click to expand...
 
Is there a table named tblBooklst and does it have a field named Author?
--
Dave Hargis, Microsoft Access MVP


KYMailman said:
I followed your instructions and still no luck. I selected the combo box in
design and opened properties. Row Source Type: Table/Query. Row Source: I
entered your statement line. When I returned to Form view and clicked on the
drop button I got the following error message:

The Record Source 'SELECT DISTINCT [Author] FROM tblbooklist' specified on
this form does not exist.

Klatuu said:
I don't know where you put it, but take it out.
Open your form in design view.
Select the combo box.
Open the Properties Dialog.
Put the following in the Row Source Property exactly like this:

SELECT DISTINCT [Author] FROM tblbooklist;

--
Dave Hargis, Microsoft Access MVP


KYMailman said:
The more I learn the less I know. I am new to using Access in Office 2003. I
attempted to build a query as follows:
Field: Author

Table: Book List

Criteria: "DISTINCT [Author] FROM tblbooklist"

I must be missing something, because the query opens with nothing in the
box, and when I open the FORM, the drop down list still has multiple entries.
Hope you can help. Thanks John

:

Use a query for the list box row source using Select Distinct. That will
return only one occurance of each author:

SELECT DISTINCT [author] FROM tblAuthors;
--
Dave Hargis, Microsoft Access MVP


:

For an existing database I created a drop down list box on a Form for the
field Author. How do I edit the drop down list to exclude or delete
duplicates?
Click to expand...
Click to expand...
Click to expand...
 
The name of my table is: Book List
I do have a field: Author

Klatuu said:
Is there a table named tblBooklst and does it have a field named Author?
--
Dave Hargis, Microsoft Access MVP


KYMailman said:
I followed your instructions and still no luck. I selected the combo box in
design and opened properties. Row Source Type: Table/Query. Row Source: I
entered your statement line. When I returned to Form view and clicked on the
drop button I got the following error message:

The Record Source 'SELECT DISTINCT [Author] FROM tblbooklist' specified on
this form does not exist.

Klatuu said:
I don't know where you put it, but take it out.
Open your form in design view.
Select the combo box.
Open the Properties Dialog.
Put the following in the Row Source Property exactly like this:

SELECT DISTINCT [Author] FROM tblbooklist;

--
Dave Hargis, Microsoft Access MVP


:

The more I learn the less I know. I am new to using Access in Office 2003. I
attempted to build a query as follows:
Field: Author

Table: Book List

Criteria: "DISTINCT [Author] FROM tblbooklist"

I must be missing something, because the query opens with nothing in the
box, and when I open the FORM, the drop down list still has multiple entries.
Hope you can help. Thanks John

:

Use a query for the list box row source using Select Distinct. That will
return only one occurance of each author:

SELECT DISTINCT [author] FROM tblAuthors;
--
Dave Hargis, Microsoft Access MVP


:

For an existing database I created a drop down list box on a Form for the
field Author. How do I edit the drop down list to exclude or delete
duplicates?
Click to expand...
Click to expand...
Click to expand...
 
Then try.

SELECT DISTINCT [Author] FROM [Book List]

You need to use your field and table names.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

KYMailman said:
The name of my table is: Book List
I do have a field: Author

Klatuu said:
Is there a table named tblBooklst and does it have a field named Author?
--
Dave Hargis, Microsoft Access MVP


KYMailman said:
I followed your instructions and still no luck. I selected the combo
box in
design and opened properties. Row Source Type: Table/Query. Row Source:
I
entered your statement line. When I returned to Form view and clicked
on the
drop button I got the following error message:

The Record Source 'SELECT DISTINCT [Author] FROM tblbooklist' specified
on
this form does not exist.

:

I don't know where you put it, but take it out.
Open your form in design view.
Select the combo box.
Open the Properties Dialog.
Put the following in the Row Source Property exactly like this:

SELECT DISTINCT [Author] FROM tblbooklist;

--
Dave Hargis, Microsoft Access MVP


:

The more I learn the less I know. I am new to using Access in
Office 2003. I
attempted to build a query as follows:
Field: Author

Table: Book List

Criteria: "DISTINCT [Author] FROM tblbooklist"

I must be missing something, because the query opens with nothing
in the
box, and when I open the FORM, the drop down list still has
multiple entries.
Hope you can help. Thanks John

:

Use a query for the list box row source using Select Distinct.
That will
return only one occurance of each author:

SELECT DISTINCT [author] FROM tblAuthors;
--
Dave Hargis, Microsoft Access MVP


:

For an existing database I created a drop down list box on a
Form for the
field Author. How do I edit the drop down list to exclude or
delete
duplicates?
Click to expand...
Click to expand...
Click to expand...
 
Thank you both for all your help. Using John's suggestion for the Row Source
did not work at first either, but it led me to the solution. I entered:

SELECT DISTINCT [Author] FROM [Book List]


When I saved it, Access entered "AS" between the words Book and List.
Naturally when I opened it I got the error message, "No such Table." I
renamed the table "Books", and my row source entry worked as follows:

SELECT DISTINCT [Author] FROM Books;

Thanks again for the help.

John Turner



John Spencer said:
Then try.

SELECT DISTINCT [Author] FROM [Book List]

You need to use your field and table names.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

KYMailman said:
The name of my table is: Book List
I do have a field: Author

Klatuu said:
Is there a table named tblBooklst and does it have a field named Author?
--
Dave Hargis, Microsoft Access MVP


:

I followed your instructions and still no luck. I selected the combo
box in
design and opened properties. Row Source Type: Table/Query. Row Source:
I
entered your statement line. When I returned to Form view and clicked
on the
drop button I got the following error message:

The Record Source 'SELECT DISTINCT [Author] FROM tblbooklist' specified
on
this form does not exist.

:

I don't know where you put it, but take it out.
Open your form in design view.
Select the combo box.
Open the Properties Dialog.
Put the following in the Row Source Property exactly like this:

SELECT DISTINCT [Author] FROM tblbooklist;

--
Dave Hargis, Microsoft Access MVP


:

The more I learn the less I know. I am new to using Access in
Office 2003. I
attempted to build a query as follows:
Field: Author

Table: Book List

Criteria: "DISTINCT [Author] FROM tblbooklist"

I must be missing something, because the query opens with nothing
in the
box, and when I open the FORM, the drop down list still has
multiple entries.
Hope you can help. Thanks John

:

Use a query for the list box row source using Select Distinct.
That will
return only one occurance of each author:

SELECT DISTINCT [author] FROM tblAuthors;
--
Dave Hargis, Microsoft Access MVP


:

For an existing database I created a drop down list box on a
Form for the
field Author. How do I edit the drop down list to exclude or
delete
duplicates?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top