How to show all available items in combobox?

[TomDDS]

Hello! I'm using Access 2010. I've created a combobox that "finds a record" based on a selection, the selection choice being client last name. It works fine, BUT, I have about 90 names on the list, and although I've increased the "List Rows" value to 90, I get one column that I have to scroll through, as 90 rows don't fit on the screen. What I want to do is pop up a list of all the names, all visible, which would presumably mean 3 columns of 30 (since 30 rows fit on the screen), so I can get to any name in 2 clicks with no scrolling. My list will never get significantly longer than this, so there is no danger of someday having 1000 names on the screen. Lots of searching keeps bringing me to info on showing multiple columns each with a different field, but I've found nothing about "wrapping" a single field list into multiple columns.

Thanks in advance!

[zmbd]

For ACC2010 and older: Column wrapping, can not be done with the current default control.
As far as I've seen in ACC2013, this still hold true.

Thus, to the best of my knowledge, you are out of luck, unless there is a third party control, an activex, or an API call that I am unware of.

[TomDDS]

Thank you zmbd. Given that, are there any other ideas for how to accomplish my goal? I have a "master listing" split form, with client details on the top, and the datasheet on the bottom (or the side, depending on circumstances). I have been scrolling through the datasheet to select the client I want tosee detials on. The current combobox is better, becuase I can see more names at once than on the datasheet, so there is less scrolling. This is nothing mission-critical; I was just hoping to make that last last leap to "click-click" and the record details appear. Thansk again!

[zmbd]

Hmm...
Personally, I would have the parent form be the client and then the child form be the details for the selected client.

IE: In the lab: Parent form is linked to Internal/External customers. The child form is linked via the customer_pk/fk related fields, thus the subform shows only the records from the sample table related to that customer. I have a toggle on the subform that will filter the shown records to only those samples that are "open." The child records have an on-click event that will then open the form related to samples, and so forth.
However, I also have a way to directly hunt the sample down based on ID... the samples when enterd have an ID code assigned. Using that code takes the user directly to that sample's dataentry


As for the cbo:
I usually turn "on" the autoexpand property of my cbo; thus, as I type the entry into the textbox portion, it starts to fill in.
For example, a cbo with a list my supplier company names... if I star typing in "f" then the first entry in the list starting with "f" fills, if I have drop open, then I can see the remaining "f" entries, if I type in "fi" then it fills in the first with "fi" and drops thru the list... of course this works best when the record source is sorted alphabetically.

You might also want to take a look at the links in this list. I understand they're not exactly what you have in mind; however, something here may help with a different approach:
-filtering-

• Microsoft Access / VBA Insights Sitemap
• How to select Records in an Access Subform from a Combo Box
  
• Cascaded Form Filtering
• Multiple Combobox Filter with Clear Option
• Example Filtering on a Form.

[ADezii]

It is a lot easier to accomplish this than you would think. Let's assume that you have a Table named Products with the Product Name entered into the [ProductName] Field. Let's further assume that you have a Combo Box named cboProducts and that you wish cboProducts to disply all 90+ Products alphabetically in three Columns, 2 inches in Width each. The following Code will do just that:

Dim MyDB As Database
Dim rst As DAO.Recordset
Dim strBuild As String

Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("SELECT [ProductName] FROM [Products] ORDER BY [ProductName]", dbOpenForwardOnly)

With rst
  Do While Not .EOF
    strBuild = strBuild & ![ProductName] & ","
      .MoveNext
  Loop
End With

strBuild = Left$(strBuild, Len(strBuild) - 1)

rst.Close
Set rst = Nothing

With Me![cboProducts]
  .RowSourceType = "Value List"
  .ColumnCount = 3
  .ColumnWidths = "2 in;2 in;2 in"
  .RowSource = strBuild
End With

[TomDDS]

Thank you ADezii. This sounds like what I want to do. Unfortunately, I'm not a "coder." I have some basic understanding of it, and have even been able to insert a few bits and pieces, but with something this size I don't even know how/where to insert it and get it working. This is not my job, I'm just hacking around with ways to make my record keeping as a social worker eaiser.

I don't expect a full tutoring session on VBA from you, and I am gratefull for the time you have given me already. I will probably take this and experiment with it at some point, since it looks so perfect for my goal, but, for now I'm likely to use the simple combobox so I can get back to my real job before the boss notes my lack of completed work.

I really do appreciate the help from you and the other poster!

It is a lot easier to accomplish this than you would think. Let's assume that you have a Table named Products with the Product Name entered into the [ProductName] Field. Let's further assume that you have a Combo Box named cboProducts and that you wish cboProducts to disply all 90+ Products alphabetically in three Columns, 2 inches in Width each. The following Code will do just that:

Dim MyDB As Database
Dim rst As DAO.Recordset
Dim strBuild As String

Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("SELECT [ProductName] FROM [Products] ORDER BY [ProductName]", dbOpenForwardOnly)

With rst
  Do While Not .EOF
    strBuild = strBuild & ![ProductName] & ","
      .MoveNext
  Loop
End With

strBuild = Left$(strBuild, Len(strBuild) - 1)

rst.Close
Set rst = Nothing

With Me![cboProducts]
  .RowSourceType = "Value List"
  .ColumnCount = 3
  .ColumnWidths = "2 in;2 in;2 in"
  .RowSource = strBuild
End With

[zmbd]

As Always very clever ADezii!

So they're all the entries are now in the drop down list...
let's just say the letters A...Z, 1,2,3,4 to give us a nice set of thirty... and something to play with:
(using "1" reference basis)
So the letter Z is in the ninth row in the second colum.
I click on the letter z
I instead get the letter y in the combobox instead of the intended letter z.

Now what?

[ADezii]

1. Immediately in the AfterUpdate() Event of the Combo Box, set its Value to NULL.
2. Superimpose a Text Box over the Combo displayimng all three Values in the Selected Row.
3. Prompt the User to see if they want the Value in Column(0), Column(1), or Column(2) for the Current Selection.
4. Granted, a very unorthodox approach.

[zmbd]

Nice idea, fairly straight forward to accomplish...

However, if we go the button route, let's toss the CBO and why not go with a 7x5 (think calendar), or 2x15, 3x10, etc... gridwork of command buttons?
The names can be read into the captions and the same returned from the click event of the selected button.
One could set the first button (A1-7x5) as page back, with the last button (G5-7x5) as the page forward and maybe a home button at B1 or F5
I've done a 5 button version very early on with a game/quiz. I don't have the code anymore, however, it seemed fairly easy at the time.
In anycase, the onlick event of the combo/text box could be set to open the form, the user selcts and the result returned.
I'd most likly do this with custom events so that when the button was clicked, RaiseEvent(with theinformationt opass) and let the calling form deal with it, in the meantime unload the form.
What do you think?

(@TomDDS: I know, it's a tad off topic, I applogise, the answer to the OP still appears to be "no - not at this time" yet we're trying to find you a workaround (^-^) )

[ADezii]

Nice approach, but the skill set of the OP would not warrant it.

[zmbd]

Well Tom:

I think we've hit the limit here:

Option1)
With the CBO as is:
format tab: list rows = # 1 to 255 == I generally set this to 20 as most of my PCs in the lab have large monitors.
data tab: auto expand = yes == this allows the box to start filling in as you type.

2) ADezii's method with the string building code and collary with the command button

3) A customized form

4) If there is a third part control, install and use.
(BTW: If there is a third party opensource/freeware/shareware - we'd like to know.
A commercial product that could potientially help please PM me prior to posting within the thread so that we make sure that the post meets the forum guidelines.

Best of Luck
-z

[TomDDS]

Nice idea, fairly straight forward to accomplish...


(@TomDDS: I know, it's a tad off topic, I applogise, the answer to the OP still appears to be "no - not at this time" yet we're trying to find you a workaround (^-^) )

LOL, no apology necessary. I got just enough of it to appreciate it. (I am a computer geek, just not real experienced with Access). I enjoyed the creative convolution :-) I'm feeling rather pleased with myself at having sparked an interesting little exchange :-)