Auto List Combo Field Editing

[raychow22]

Hi Forum,

Is there a way for a combo box on the Form to help requery the "List" again after a comma "," or a semi-colon ";".

For example I have a combo box for of names list as followed:
1) Alpha
2) Beta
3) Omega

Since this is a single field, I can really only select one; however this is not limited to the list so I can add if I wanted to.

Let's say I enter the combo box "A" and it helps me find "Alpha", then I want to be able to find "Beta" after I separate it with a "," or ";". I would just enter "B", and Access will help me find "Beta" on the list for me.

Is this possible?

[orange]

I'm sure it's possible, but add more detail to your requirements description. Get some sample data. For each test record, write down what should be done to get the format/result you want. Make sure you understand the logic of your proposed process(es) before jumping in to a physical database and doing a trial and error approach to getting it right.

You might also want to review the Split function in Access.

[ranman256]

difficult and awkward,
instead, use a list box, then the user can just dbl-click the item needed.
place all clicks into a text box via:

sub lstBox_dblClick()
txtBox = txtBox & ";" & lstbox 
if left(txtbox) = ";" then txBox = mid(txtBox,2)
end sub

[raychow22]

difficult and awkward,
instead, use a list box, then the user can just dbl-click the item needed.
place all clicks into a text box via:

sub lstBox_dblClick()
txtBox = txtBox & ";" & lstbox 
if left(txtbox) = ";" then txBox = mid(txtBox,2)
end sub

I've tried this method. I got an error which Highlighted "Left"

[raychow22]

I'm sure it's possible, but add more detail to your requirements description. Get some sample data. For each test record, write down what should be done to get the format/result you want. Make sure you understand the logic of your proposed process(es) before jumping in to a physical database and doing a trial and error approach to getting it right.

You might also want to review the Split function in Access.

The idea here is to get away from multi-value fields. combo box can have multiple selections from my list. I want to be able to use a delimiter (comma/semi-colon) to restart the combo list and help me requery the list again so I can find second, third and so on ..

[orange]

Show us some sample data. And tell us what you want to achieve in plain English - no database jargon.

to get away from multi-value fields

why do you have them in the first place?
Tell us about the database/application.

It seems you are dealing with a form but you have told readers nothing about your needs in business terms and nothing about your tables.
Good luck.

[Micron]

I got an error which Highlighted "Left"
if left(txtbox)

Check out the Left function. Your syntax is incomplete.
I don't think what you're trying to do is possible. Multi value fields look like the values are in one field, but in fact they are not. They are stored in hidden system tables in a manner that resembles normal table relationships anyway, with the downside that you can't see them. Thus I don't see any string function (such as Left) ever working. About the only way I've ever been able to separate them is to use the .Value property of a MVF in a query, and that was only for testing.

[June7]

You want to get away from multi-value field yet store multiple data elements as a CSV string in a single field? Can't say that is an improvement. So what purpose does combobox serve - validate the user free-type input by matching to list items? It may be possible with complicated and creative code, possibly in Change event and using an array. But I really don't see the benefit. I think ranman nailed it succinctly with 'difficult and awkward'.