Combo List Query

[Bob]

I have a drop down list from my Combo Box, If I have some data on the first
form, then the seventh Form the dropdown list has a 6 row gap, Is there any
way I can not show the empty lines


Thanks in advance.........Bob Vance

 

[John Vinson]

I have a drop down list from my Combo Box, If I have some data on the first
form, then the seventh Form the dropdown list has a 6 row gap, Is there any
way I can not show the empty lines


Thanks in advance.........Bob Vance

This is confusing. Neither Forms nor Combo Boxes contain any data:
just Tables. We have no way of knowing what your "first form" or your
"seventh form" are displaying.

I suspect that you can base the Combo Box on a query which doesn't
return the "gap" records, but I suspect even more that your table
structure may need to be revised!

John W. Vinson[MVP]

 

[John Vinson]

I have a drop down list from my Combo Box, If I have some data on the first
form, then the seventh Form the dropdown list has a 6 row gap, Is there any
way I can not show the empty lines


Thanks in advance.........Bob Vance

This is confusing. Neither Forms nor Combo Boxes contain any data:
just Tables. We have no way of knowing what your "first form" or your
"seventh form" are displaying.

I suspect that you can base the Combo Box on a query which doesn't
return the "gap" records, but I suspect even more that your table
structure may need to be revised!

John W. Vinson[MVP]

 

[Guest]

Hi Bob,

It sounds as if the recordsource for your combo box is based on a detail
table, instead of a lookup table, and some of the detail records for the
selected field(s) are blank.

What type of data are you attempting to display in your combo box? Do you
know if it comes from a lookup table or a detail table? What is the
rowsource for your combo box? If it is a query, then please copy the SQL
statement for the query and post it in a reply.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Hi Bob,

It sounds as if the recordsource for your combo box is based on a detail
table, instead of a lookup table, and some of the detail records for the
selected field(s) are blank.

What type of data are you attempting to display in your combo box? Do you
know if it comes from a lookup table or a detail table? What is the
rowsource for your combo box? If it is a query, then please copy the SQL
statement for the query and post it in a reply.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Bob]

Sorry this combo Box is picking up the information from a Query List , just
opened up the Query Table and there is data in the 1st and 7th line which is
giving me the same gaps in the drop down list, How do I go about just
showing the lines with data?....Thanks Bob

 

[Bob]

Sorry this combo Box is picking up the information from a Query List , just
opened up the Query Table and there is data in the 1st and 7th line which is
giving me the same gaps in the drop down list, How do I go about just
showing the lines with data?....Thanks Bob

 

[Guest]

Try adding a criteria to the field in question.

Numeric Field:
is not null

Text Field:
is not null AND <> ""


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Try adding a criteria to the field in question.

Numeric Field:
is not null

Text Field:
is not null AND <> ""


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[John Vinson]

Sorry this combo Box is picking up the information from a Query List , just
opened up the Query Table and there is data in the 1st and 7th line which is
giving me the same gaps in the drop down list, How do I go about just
showing the lines with data?....Thanks Bob

It would help a lot if you would use Access terminology. There is no
such thing as a "Query List" or a "Query Table". A Select Query
produces a Recordset; and a query can be used as the Row Source for a
combo box (or for other purposes of course).

If you want a Combo Box with different data than your query produces -
*use another query*. Modify this query with a criterion of

IS NOT NULL

on the relevant field, to include only those records which *do* have
data, and use this query for the combo box.

John W. Vinson[MVP]

 

[John Vinson]

Sorry this combo Box is picking up the information from a Query List , just
opened up the Query Table and there is data in the 1st and 7th line which is
giving me the same gaps in the drop down list, How do I go about just
showing the lines with data?....Thanks Bob

It would help a lot if you would use Access terminology. There is no
such thing as a "Query List" or a "Query Table". A Select Query
produces a Recordset; and a query can be used as the Row Source for a
combo box (or for other purposes of course).

If you want a Combo Box with different data than your query produces -
*use another query*. Modify this query with a criterion of

IS NOT NULL

on the relevant field, to include only those records which *do* have
data, and use this query for the combo box.

John W. Vinson[MVP]

 

[Bob]

It would help a lot if you would use Access terminology. There is no
such thing as a "Query List" or a "Query Table". A Select Query
produces a Recordset; and a query can be used as the Row Source for a
combo box (or for other purposes of course).

If you want a Combo Box with different data than your query produces -
*use another query*. Modify this query with a criterion of

IS NOT NULL

on the relevant field, to include only those records which *do* have
data, and use this query for the combo box.

John W. Vinson[MVP]

Thanks John I added "Is Not Null" to criteria and worked perfect on
descending order, How do I add " No Repeats" to criteria so I don't get the
same word repeating itself in the list?......Thanks Bob

 

[Bob]

It would help a lot if you would use Access terminology. There is no
such thing as a "Query List" or a "Query Table". A Select Query
produces a Recordset; and a query can be used as the Row Source for a
combo box (or for other purposes of course).

If you want a Combo Box with different data than your query produces -
*use another query*. Modify this query with a criterion of

IS NOT NULL

on the relevant field, to include only those records which *do* have
data, and use this query for the combo box.

John W. Vinson[MVP]

Thanks John I added "Is Not Null" to criteria and worked perfect on
descending order, How do I add " No Repeats" to criteria so I don't get the
same word repeating itself in the list?......Thanks Bob

 

[John Vinson]

Thanks John I added "Is Not Null" to criteria and worked perfect on
descending order, How do I add " No Repeats" to criteria so I don't get the
same word repeating itself in the list?......Thanks Bob

View the query's Properties (right mouseclick the background of the
tables) and set the "Unique Values" property of the query to True.

John W. Vinson[MVP]

 

[John Vinson]

Thanks John I added "Is Not Null" to criteria and worked perfect on
descending order, How do I add " No Repeats" to criteria so I don't get the
same word repeating itself in the list?......Thanks Bob

View the query's Properties (right mouseclick the background of the
tables) and set the "Unique Values" property of the query to True.

John W. Vinson[MVP]

 

[Guest]

Hi Bob,

In query design view, click on View > SQL View. Insert the DISTINCT keyword
just after the SELECT keyword. For example, in the sample Northwind database,
you could use the following query:

SELECT DISTINCT ShipCity
FROM Orders
WHERE ShipCity Is Not Null And ShipCity <>""
ORDER BY ShipCity;

However, this may not be the most efficient way of solving this issue. The
reason you are running into these duplicates is that you are basing the
rowsource for your combo box on a detail table. In the Northwind database,
click on Tools > Relationships. Here, you will see how the Customers table is
related one-to-many (1:M) to the Orders table. A more efficient rowsource for
the same combo box would be this:

SELECT City
FROM Customers
WHERE City Is Not Null And City <>""
ORDER BY City


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Hi Bob,

In query design view, click on View > SQL View. Insert the DISTINCT keyword
just after the SELECT keyword. For example, in the sample Northwind database,
you could use the following query:

SELECT DISTINCT ShipCity
FROM Orders
WHERE ShipCity Is Not Null And ShipCity <>""
ORDER BY ShipCity;

However, this may not be the most efficient way of solving this issue. The
reason you are running into these duplicates is that you are basing the
rowsource for your combo box on a detail table. In the Northwind database,
click on Tools > Relationships. Here, you will see how the Customers table is
related one-to-many (1:M) to the Orders table. A more efficient rowsource for
the same combo box would be this:

SELECT City
FROM Customers
WHERE City Is Not Null And City <>""
ORDER BY City


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Bob]

BRILLIANT John Thank You Very Much for your HELP.....Bob

 

[Bob]

BRILLIANT John Thank You Very Much for your HELP.....Bob