How do you enter multiple terms off a list into one field?

[Redbeard]

I am using Access 2003. Does anyone know a way creating a field in a form that you can only accept data from a list to enter into it, but you can enter multiple entrees in the same field? For Example, I would click on the field and a list or 20 terms would come up. Then I click on a term and enter it into the field (like a combo box) but I can then click on the field again and enter a second term into my field with out deleting my first one. I want to be able to add at lest 4 or 5 different terms off the list to one field. What I am trying to do is create a field where there would be set terms that I could search for each record.

Anyone got any ideas?

[Scott Price]

A multi-select listbox should allow what you are describing.

Have a look at this code snippet in our Articles section: To retrieve values from a multiselect listbox:

Regards,
Scott

[Redbeard]

I am having troble with the Code for list boxs that you recomender? As I am new to coding in VB.

Dim valSelect As Variant
Dim strValue As String ' just used for the demonstration

For Each valSelect In Me.listboxName.ItemsSelected
strValue = strValue & "'" & Me.listboxName.ItemData(valSelect) & "', "
Next valSelect

' to remove trailing comma
strValue = Left(strValue, Len(strValue)-2)

Where do I put this code... is it conected to the List box as an event or just inserted in general code?
listboxName = this is name of my listbox
ItemsSelected = ?
ItemData = ?

Please help!

[nico5038]

You should use Access 2007 for such a multivalue field.
In the older versions I wouldn't store the data in one field, as you would have to code and de-code the field each time.
Assume fields 3, 5 and 7 are set, then when updating you would need to fill the multiselect listbox and set those values and after a change the new set would deen to be stored again :-(
Personally I just define an additional table with two fields (The ID of the maintable and the ID of the table holding the 20 choices) that's linked to the maintable. By using a linked subform Access will fill the maintable ID and using a combobox, the needed selection of the 20 choices can be added.

Getting the idea ?

Nic;o)

[Redbeard]

Sorry I don’t really follow? Won’t this still result in me having a combo box (on my main form) that I can pick one and only one term from the terms table and enter into my the combo box in the main form? Can you explain farther?

Thanks

[nico5038]

Sorry I don’t really follow? Won’t this still result in me having a combo box (on my main form) that I can pick one and only one term from the terms table and enter into my the combo box in the main form? Can you explain farther?

Thanks

No, it will provide you with a subform where you can add as many terms as needed. (And removing terms will be possible too)
This will allow you to search all used terms by filtering this additional table.

You can compare this with adding one ore more products to an Order. These are also placed in an OrderDetail table. To find all orders with a specific product you can filter the Orderdetail table for the product. Guess you won't place all ordered products into the main Order table, so why storing your terms in one field...

Nic;o)

[Redbeard]

Thanks for all the help so far Nic;o).

so why storing your terms in one field...

The database is of artifacts and photo’s kept by the museum that I work for. The main form currently has a field that is called “Classification” that is a combo box. When you click on it, it displays a list of terms that categorize the photos so that they are easier to search thought (like: People, Places, Rule, Streets…). However only one term per photo can be put in the combo box. The problem is that many photo’s need to be linked to more then one term, so if a search is done a photo could come up as “People” and as “Houses” if they have both in the photo. I need to have a list of terms that I can select from that when selected will enter into a field one after another. So my new “Classification” field could have up to 4 or more terms to search by.

What you are suggesting would take my to a sub-form ever time to fill in my terms? Is it not possible to have a list box that when you click a term off the list it adds it to a text box and will continue to add as many terms off the list as you click?

[nico5038]

As stated before, combining the terms into one field will require a "code" and a "de-code" of the applicable terms.
When searching on multiple terms, you'll need to filter on multiple values within that field.
Having them in a separate table will ease the searching (you can use for the WHERE clause e.g. Term IN ('house','horse') ). You can still use a multiselect listbox, but personally I prefer however to show all possibilities in a subform and have the user to check the ones applicable. Then the checked ones are stored in the relation table.
A checkbox is more "stable" and userfriendly as a multiselect list.

Getting the idea ?

Nic;o)

[ethanhines]

A multi-select listbox should allow what you are describing.

Have a look at this code snippet in our Articles section: To retrieve values from a multiselect listbox:

Dim valSelect As Variant
Dim strValue As String ' just used for the demonstration
 
    For Each valSelect In Me.listboxName.ItemsSelected
        strValue = strValue & "'" & Me.listboxName.ItemData(valSelect) & "', "
    Next valSelect
 
    ' to remove trailing comma
    strValue = Left(strValue, Len(strValue)-2)
 

If I am right this code makes strvalue equal to the values that were selected in the multiselect list box right? Now how do I get that information into the right field in the right record say for example TOPPINGS

[ethanhines]

If I am right this code makes strvalue equal to the values that were selected in the multiselect list box right? Now how do I get that information into the right field in the right record say for example TOPPINGS

Never Mind...Figured it out Sorry.....

[Scott Price]

I've been away from the desk for the last two or three weeks, so sorry to not get back to your question sooner. Glad you got it figured out, though.

Regards,
Scott