Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Combo List Query

Discussion in 'Information Technology' started by Bob, Jul 28, 2006.

Tags:
  1. Bob

    Bob
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News How The Left & Indian Nationalists Distorted The History Of Sikh Gadhar Movement - Breaking News Sep 21, 2016
    Interfaith Materialists And Its Discontents Interfaith Dialogues Jun 10, 2016
    Sikhi Quasi-Pragmatists And Quasi-Spiritualists, Care To Explain? Sikh Sikhi Sikhism Jun 6, 2016
    Cholesterol Is Finally Officially Removed From "naughty List" Health & Nutrition Feb 11, 2016
    Listening to Gurbani while driving?? Sikh Rehat Maryada Jan 13, 2016

  3. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Mon, 22 May 2006 10:51:21 +1200, "Bob" <xxx@xx.xx> wrote:

    >
    >
    >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]
     
  4. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    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" wrote:

    >
    >
    > 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
    >
    >
    >
     
  5. Bob

    Bob
    Expand Collapse
    Guest

    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

    "John Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
    news:eek:fu172t9358k8n40fcta10upe74boovf2s@4ax.com...
    > On Mon, 22 May 2006 10:51:21 +1200, "Bob" <xxx@xx.xx> wrote:
    >
    >>
    >>
    >>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]
     
  6. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    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
    __________________________________________


    "Bob" wrote:

    > 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
    >
    > "John Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
    > news:eek:fu172t9358k8n40fcta10upe74boovf2s@4ax.com...
    > > On Mon, 22 May 2006 10:51:21 +1200, "Bob" <xxx@xx.xx> wrote:
    > >
    > >>
    > >>
    > >>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]

    >
    >
    >
     
  7. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Mon, 22 May 2006 12:26:19 +1200, "Bob" <xxx@xx.xx> wrote:

    >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]
     
  8. Bob

    Bob
    Expand Collapse
    Guest

    "John Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
    news:ff8272psd5jri2nd5u8q2qnkqid2gmsfcq@4ax.com...
    > On Mon, 22 May 2006 12:26:19 +1200, "Bob" <xxx@xx.xx> wrote:
    >
    >>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]


    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
     
  9. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Mon, 22 May 2006 17:10:01 +1200, "Bob" <xxx@xx.xx> wrote:

    >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]
     
  10. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    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" wrote:

    > 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
     
  11. Bob

    Bob
    Expand Collapse
    Guest

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

    "John Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
    news:pai272pgenhj35e435hmpuq4h2to63mkg1@4ax.com...
    > On Mon, 22 May 2006 17:10:01 +1200, "Bob" <xxx@xx.xx> wrote:
    >
    >>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]
     

Share This Page