Select Multivalues in listbox

[voroojak]

Hi
i have a listbox that generate years. i want to be able to selct one or more years. i change the properties of multi selct to extended and i can select more than one. but the problem is that i want to pass the data in to the table in sql database and it is not passing .

i would appreciate any help.

thanks
voroojak

[Stewart Ross]

Hi. You will need to loop through each of the values selected from your listbox and use a suitable SQL INSERT statement generated in code to insert them one by one into the table concerned. You tell us too little to give you much that will help you with this task, but the following skeleton may give you some ideas:

Dim ctl as Control
Dim varItm as Variant
Dim strSQL as String
Set ctl = Me![name of your listbox]
For Each varItm In ctl.ItemsSelected
        strSQL = "INSERT INTO [your table name] ([your field name 1]) "
        strSQL = strSQL & " VALUES " & ctl.ItemData(varItm) & ";"
        CurrentDb.Execute strSQL
Next varItm

You may find that you need to give all of this much more thought. I see little value in inserting single values like this into new rows in a table (assuming that you are not violating key contraints by doing so), but without knowing more details all I can do is answer the question you have actually asked, not the ones I think you should have asked.

Maybe you really mean you want to update existing rows, in which case you would use an UPDATE statement in place of the INSERT, but you would have to have suitable selection criteria to update the correct records. Anyway, you don't tell us, and there is simply not enough detail in what you ask to advise you further.

-Stewart

[voroojak]

HI
thanks for your answer.the thing is that i want to select multivalues from my listbox and then i can be able to show the selected values in my table. for example if i choos 2009 and 2010, then it shows in my table 2009 2010.

thanks alot

[Stewart Ross]

Sorry, but I have no idea what you mean by 'show the selected values in my table'. Are you really meaning the table, or do you mean the form itself?

If you really do mean table, is it the table to which your form is bound? Is it some other table? If it is, you either want to INSERT a row into your table to contain the values, or UPDATE an existing row with those values - what other choices are there?

I am thoroughly confused over what you are really asking...

-Stewart

[voroojak]

I am really sorry if i confuse you.
i try to fix it.
maybe it is better just to say that i have a table that i called campaign_data
and the column that the year should be pass is year_list. the list box name is yr_list.
what i want to have as a result is that if i select 2009 and 2010 in my list box, the value that i can see in my table be 2009 2010.

thanks alot

[voroojak]

H again
it worked some how.
i did with check box and put the conditions whether it is checked or not. i was prefering to do it with list box but it didnt work unfortunately. i still like to know how can i do it with list box but for now it is working:)

thanks alot

[Stewart Ross]

Hi voroojak. Thanks for your clarification. You were referring to the table bound to the form itself, a point not clear from your question. The solution given by me in post # 2 is referring to inserting rows in a completely independent table from an unbound listbox control, and it just does not apply to your circumstances as we now know them. A bound listbox control updates a value in the underlying table as soon as a selection is made from the control.

I am sure next time you post you will be clearer about such details.

Glad whatever was wrong is now working for you.

-Stewart

[voroojak]

Thanks alot and sorry for not be cleared.