Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2011
    Posts
    4

    Question Unanswered: Viewing all columns in a combo box

    Hi,

    I have a combo box that uses a simple query as the recordsource. There are 4 columns including the key.

    I have set the column count to 4 and the lengths are 0";1";1";1"

    When I select the dropdown I see the three columns I need. However, when I select a specific row and the dropdown closes all that is seen in the combobox on the form is the 1st column.

    Example:

    When I select the Dropdown I see:
    Smith Joe Employee
    Jones John Contractor

    If I select the second row, the combobox collapses (which is good) but all that remains visible is the last name of the row selected:
    Jones
    I want to see all three columns in the combobox of the selected row, and not just the first one.

    I cannot find any properties that would fix this. I am using MS Access 2010 but this problem existed in 2007 as well.

    In the past I worked around it by concatenating the columns I want to see in the first column but frankly this is a pain.

    Thanks for your help!!
    Last edited by ComputersAreFun; 11-15-11 at 23:20. Reason: typo

  2. #2
    Join Date
    Nov 2011
    Posts
    11
    I am new to access but you will need to modify your query as:
    1st column ID remain
    2nd column should be an expression. right click build > select "table" select the column you want then +" "+ then select the second column then +" "+ third column etc...
    Also change the value from 0,1,1,1 to 0,5

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by ComputersAreFun View Post
    When I select the dropdown I see the three columns I need. However, when I select a specific row and the dropdown closes all that is seen in the combobox on the form is the 1st column.

    I want to see all three columns in the combobox of the selected row, and not just the first one.

    I cannot find any properties that would fix this. I am using MS Access 2010 but this problem existed in 2007 as well.
    This is just the way a ComboBox is built and works. It is made of a ListBox associated to a TextBox. When the combo is "closed" (i.e. the list is invisible) it behaves as a TextBox, which can only display (and allow editing) one value. There is no property or method that would allow you to change that.

    What you can do, if you really want to, is to associate several TextBox controls to the ComboBox, using its AfterUpdate event to update the TextBoxes contents.
    Have a nice day!

  4. #4
    Join Date
    Nov 2011
    Posts
    4

    Thumbs up Thanks for the tip

    I suspected as much. Thanks for the feedback!

    I like your idea and it would be fun to try it out just to say I did it. But, I think I'll just stick to concatenating the important fields into column 1.

    I'll add this as a follow-up question: I would like to have the columns separated by a fixed length so they are easier to read. I tried padding with spaces and they didn't line up. This is probably because of the font. How can I change the font for the combo box? I looked at the properties and it didn't jump out at me.

    Thanks!

  5. #5
    Join Date
    Nov 2011
    Posts
    4

    Question That is sort of how I do it, but...

    Quote Originally Posted by john_gringo View Post
    I am new to access but you will need to modify your query as:
    1st column ID remain
    2nd column should be an expression. right click build > select "table" select the column you want then +" "+ then select the second column then +" "+ third column etc...
    Also change the value from 0,1,1,1 to 0,5
    Perhaps you can provide guidance on how to allow the columns to line up.

    For example, if I concat last_name + ", " + first_name + " " + phone_number I get this in the ComboBox

    Jones, John 555-1212
    Murphy, Frank 444-1212
    Wilsonnator, Louise 333-1212

    I want them to line up.

    I tried String to pad it like this: last_name + ''," + string(20-length(last_name)," ") + first_name etc...

    But they still don't line up. I suspect the font is at fault. Any ideas on how to fix? (It just occurred to me to try placing an ASCII tab between the columns. This might work, I suppose)

    Thanks
    Last edited by ComputersAreFun; 11-15-11 at 23:36.

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    The only way that you could get the columns to line up would be to use a fixed-width font for the combo box, and to pad all the input columns to a set length. Using a tab character would smooth things out a bit, but you would still get some records that were longer than others.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Quote Originally Posted by ComputersAreFun View Post
    I want them to line up.

    I tried String to pad it like this: last_name + ''," + string(20-length(last_name)," ") + first_name etc...

    But they still don't line up. I suspect the font is at fault.
    You suspect right!

    If this is the path you wish to go down (which, personally, I wouldn't), then you will need to use a monospace font. i.e. Courier.

    Courier makes sure that every character takes up the same amount of pixels on your screen. Whether it's a 'W' or a ','. For example, the [ CODE ] tags for these posts use courier, so:

    Code:
    WWWW
    ,,,,
    This is a sentance with only vowels
      i  i  a  e  a  e  i   o     o e
    Gives you the idea.


    Personally, I would avoid going down this route, because you'll run into problems if you get a long name for example.

    You can't use vbTab in as part of the strings that are listed in the combo box, so that's a no go.


    I would:
    1) Make a listbox, that's only tall enough for one row, with your 4 columns.
    2) I'd then set an 'AfterUpdate()' event for it, to change its height, from one row, to x rows, and vice versa, giving the impression of it being a combobox.
    3) I'd then set a 'LostFocus()' event for it, to ensure that it returns to one row, if a user doesn't make a choice, and moves to a different control.

    So, so far you have a piece of code that looks like:

    Code:
    Public switch As Boolean
    Public curLng As Long
    
    Option Compare Database
    
    
    Private Sub tmpList_AfterUpdate()
    
    If (switch) Then
        curLng = Me.tmpList.ListIndex
        Me.tmpList.Height = 280
        switch = False
    Else
        curLng = Me.tmpList.ListIndex
        Me.tmpList.Height = 2000
        switch = True
    End If
    
    End Sub
    
    
    Private Sub tmpList_LostFocus()
    
    switch = True
    Me.tmpList.Height = 280
    
    Me.tmpList.ListIndex = curLng
    
    End Sub
    The ListIndex is used to allow the bottom items of the list (that are effectively 'cut off', when the list is shrunk back to one row) to remain in view when selected.


    If you wanted to get more technical about it, there are a couple of things that aren't particularly nice about it. For one, the \/ icon that is usually next to the combobox, obviously won't be there, instead you have an up and down arrow. This you could cover up with a label, that has its background/foreground colour set to match the colour of the form, and then simply toggle its '.visible' element to true or false, depending on whether the listbox has one row or several.

    Another thing that isn't particularly convincing (for making it look like a true combobox) is that the selected item, the one that you'll see when you navigate to the next control on your form, will have a black, highlighted, background. This can be overcome by:
    1) Storing the selected value(s) in a Public variable/array.
    2) Then adding immediately after step 1, 'tmpList.Selected(tmpList.ListIndex) = False', to deselect it.
    3) When you need to reference the value that is shown in the combobox, you would then simply reference the Public variable/array instead.


    With those two slight modifications, you'd have exactly what you were after, but with a little more work and coding involved.
    Looking for the perfect beer...

  8. #8
    Join Date
    Nov 2011
    Posts
    4

    Talking Outstanding post

    Thanks! What extraordinary input!

    I will spend some time looking over your code and putting it to use. I am working on a relatively small application that will be used by several grant writers and project managers to track deliverables and to document various organizations, learning activities, attendees, etc...

    It isn't an overly complex system but my users are very non-technical so every thing I can do to make it easier to use is a plus!

    Thanks again.

    -f

  9. #9
    Join Date
    Aug 2013
    Posts
    7

    Simpler way to have column of same size and multiple valu combo-box

    I Also had the same problem as you and found the following procedure:
    Step 1/ select all the fields you want to see in your combo-box and a concatenated field
    for example SELECT contact.ID, contact.Name &" "& contact.FirstName AS fullName , contact.Name, contact.FirstName, ....

    Step 2/, set the following width : 0";0.0007";1;1
    Selecting 0.00007 makes this field absolutely invisible when the combo-box is open and, when the combo-box collapses, the values it displays is the one of this very small column. Which in our example is "Name FirstName"

    (If you set it to 0, when the combo-box collapses the combo-box value is the one of the 3rd column)

    works perfectly for me without any VBA code
    regards

  10. #10
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by ComputersAreFun View Post

    ...if I concat last_name + ", " + first_name + " " + phone_number ...
    FYI, when performing concatenation, you really should be using the ampersand (&) rather than the plus sign; using the plus sign, for this task, can lead to unexpected results! One, for instance, is that if an element of the concatenation is 'empty,' which is to say Null, the results will be Null!

    If

    last_name = "Smith"

    first_name is empty, or Null

    and

    phone_number = 555-1212

    the results of

    last_name + ", " + first_name + " " + phone_number

    will be nothing! That's because

    anything + Null = Null.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Tags for this Thread

Posting Permissions

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