Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2008
    Posts
    25

    Unanswered: Want combo box to display 2 columns from other table

    I have the following code so my Combo box displays all of the values in a column from another table (table = Observer):

    Row Source: SELECT DISTINCT Observer.ObserverID FROM Observer;

    this works great, however, I'd like my combo box to display 2 columns - the values from 2 different columns of the Observer table.

    I know I need to set my Column Count to 2 (for 2 columns), but I've tried using the code:

    Row Source: SELECT DISTINCT Observer.ObserverID FROM Observer; SELECT DISTINCT Observer.LastName FROM Observer;

    and it's not working.

    any suggestions?
    thx

  2. #2
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201

    Multi-column combo boxes

    The rowsource property of a combo box can be an SQL select statement showing as many columns as you like (this can be useful for proper record identification). Right-click on the combo box field and select properties. Set the Row Source Type to Table/Query. Click on the RowSource and a small button will appear at the RH end. Click on this to open the SQL query wizard. Select your fields in the usual way and then close. The SQL SELECT statement will appear in the Rowsource property field. Depending upon the data you have chosen, you will need to set some other properties; e.g column count, column heads (if required), bound column (the one which provides the selectable values from the combo box) and the combined width of all fields. It is common practice to use a numeric ID field as the first one, but hide it by giving it a width of zero.

    You can change the rowsource programmatically, by setting the value to an SQL statement written in VBA and then requerying the combo box. This is handy when cascading values in hierarchical relationships. If you take advantage of this feature, remember to set the rowsource to a zero-length string and requery at startup and before closing the form and then resetting it with your VBA statement. Oherwise, you may get some harmless bu irritating dialogue boxes asking for parameters. This is a consequence of Microsoft's opening and closing events sequence.

    I hope this helps.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    your rowsource SQL is a problme
    Code:
    SELECT DISTINCT Observer.ObserverID FROM Observer; SELECT DISTINCT Observer.LastName FROM Observer;
    ...doesn't make sense. you've wrapped up two statements in one line, which wont work.. the cntrol needs a single sql statement
    i'd try something like
    Code:
    SELECT DISTINCT (Observer.ObserverID), Observer.LastName FROM Observer;
    I think you will probably benefit from some order by clause, which depends on what your users will be looking for.. are they entering an ID or a name?

    you may need to changes the display width (I think) settings if one is ets to 0cm...... that'll be a problem....
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Aug 2008
    Posts
    25
    thanks everyone, this code did the trick

    SELECT DISTINCT (Observer.ObserverID), Observer.LastName FROM Observer;

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Any reason for the brackets?

    You realise that DISTINCT is not a function and applies to all returned columns, right?
    George
    Home | Blog

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    no I didn't.. I thought it only returned distinct values of the specified columns...

    my bad...
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •