Looking for Multi Select options on Access Form

[LvDRvr]

Working with Access database. I have created a form. I need to pull Employee Name(s) on the form from employee table. Need some multiselect option for more than one name to attach to the form.
I have played with combobox with multiselect option checked as yes. It works great. But if the Employee table has 2000 employee's then i need capability to type and search by name on the drop down list. Currently I loose that capability if i pull more than one field to display and make it multiselect.
How can I do that? Please help.
Nancy

 

[TheAceMan1]

How are ya LvDRvr . . .

To my knowledge comboboxes don't have a [blue]multiselect[/blue] property. Are you sure your not talking a listbox here?

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

I also assume you mean a listbox. If that is the case the easiest is to put an unbound textbox over top of the listbox. You can then write code on the change event of the text box to either do a find as you type where it narrows down the list of things
i.e you type in Smi and you requery the listbox to show only records beginning with Smi
Or if you type in Smi you move the list to the first record with Smi.


Do you want to search one field, multiple fields, from the beginning (Smi*), or anywhere in the text (*Smi*)?

 

[MajP]

Anyway here is some code to move to the record that matches what is being entered in a textbox.

Private Sub txtBxSearch_Change()
  Dim strSearch As String
  strSearch = Nz(txtBxSearch.Text, "")
  MoveListBox strSearch, Me.lstProducts, 1
End Sub

Public Sub MoveListBox(strSearch As String, lstBox As Access.ListBox, Optional intColumn As Integer = 0)
  strSearch = Trim(strSearch)
  Dim I As Integer
  If Not strSearch = "" Then
    For I = 0 To lstBox.ListCount - 1
      If lstBox.Column(intColumn, I) Like strSearch & "*" Then
        lstBox.Selected(I) = True
      End If
    Next I
  End If
End Sub

 

[TheAceMan1]

LvDRvr . . .

[blue]?[/blue]

[blue]?[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[lameid]

Guys,

It could be one of those multi-valued fields new in ... Access 2007... Still using 2003 myself...


LvDRvr,

That said, from what I have read about multi-valued fields I have to ask is it to late to fix the database design to not use it? I can't find it but I saw a thread on another site yesterday (when I tried to figure out your issue) where one of the Access MVP's recommended not to use them. In short, they cause more hassle than anything but are slightly more intuitive to people not knowing relational database theory?

This seems like the kind of thing PHV usually ways in on when the rest of us are poking it with a stick... Paging PHV... I don't think that will work

 

[TheAceMan1]

[blue]It could be ...[/blue]

We await! ...

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

I had no idea what the OP was referring to with the "attaching to the form", my assumption was looking for a way to navigate a large multiselect listbox.
However, disregard my code because that would provide some strange issues in a multiselect. Works find on a single select

If the issue is pushing multiple values into a field, yes the multivalue field would work. However, you are stuck with the multivalue field control to do this. Either a listbox with checks or a text box that acts like a combo.

http://office.microsoft.com/en-us/access-help/guide-to-multivalued-fields-HA001233722.aspx

This solves the issue of easily pushing multiple selected values into a single field. Note: in truth it does not really do this, it normalizes the data into hidden system tables. However, there is no way to navigate the list by code that I can figure. Unlike a standard list if I select a value in code on the MV listbox the list will not move to that location. It just selects that value. You can filter the list which works, but gets a little strange with values already selected

 

[LvDRvr]

Hello everyone thanks for the input. My apologies to all, I got side tracked and did not log back in to check the responses.

TheAceMan1: it is a combobox with multiselect value field. Its new in Access 2007 and up versions.

MajP: I can try your suggestion and see how far I get with this.Will let you know. Let me look into your suggestions that you posted. Will let you know. Thank you so much.


Lameid: No its not too late to change the database. I can totally not use combobox and go with some other suggestion. I was already thinking of looking into other better suggestions when i posted this thread.

Thanks all of you.

 

[MajP]

AceMan,
I do not think the OP is quite categorizing it correctly. There is not a standard multiselect combobox like you would think. But if you add a comobox to a multivalued field you get the ability to multiselect. But it is not a standard combobox. If you have not played with it yet, it looks like a column of check boxes and then the selection.

It is a pretty neat control similar to a listview, but as far as I can tell you cannot build one yourself. In other words you could not make this an unbound control, although that would be very useful. It must happen behind the scenes.
What confuses me is that the OP says that "multselect" is choosen. I do not believe there is a choice for that, and that would make little sense. Why have a multi value field with no way to multiselect?

BTW this is not an endorsement for MV fields. They actually work very well, and in truth are normalized. However, like lookup fields in tables they seem to add more confusion than benefit.

 

[lameid]

LvDRvr,

The issue with multi-valued fields is that they or more or less hidden from being seen in queries and use later from what I gather.


As MAJP points out it is doing some things behind the scenes that you could do more manually.

Instead of having a Multi-valued field, you could have another table. This table would have a foreign key in it (field or fields) that relate back to your existing table plus a field for the data that is in your multi-valued field now... This creates a one to many relationship. In this case you would likely use a suborm to enter your values instead.

 

[TheAceMan1]

MajP . . .

Thanks for the intel. I have attempted to us [blue]MV[/blue] in a few designs but threw it aside as it always appeared to be more trouble than its worth. At least so far. Now with this combobox issue it appears to be over complicated.

In any case ... a combobox with a [blue]multiselect[/blue] property ... I think not.

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]