Loop for multiselect list box

[NeedsHelp101]

Hi,
I'm trying to create a loop that will write the values selected from an extended multiselect list box to a table.
Table = TrafficForForm
Market = column in the table to be populated
lstMarket = the multiselect list box

The commented out lines are ones I think might be wrong or unecessary.

Here's the code:
***
Dim dbMarketing As Database
Dim ForresterMarket As String
Dim rec As Recordset
Dim lstMarket As ListBox


Set dbMarketing = CurrentDb()
Set rec = dbMarketing.OpenRecordset("TrafficForForm")
'Set ForresterMarket =
'Set lstMarket =

For Each ForresterMarket In Me.lstMarket.ItemsSelected
rec.AddNew
'TrafficForForm!Market = Me.lstMarket.ItemData(Market)
rec.Update
Debug.Print ForresterMarket
End With
Next

'Set dbMarketing = Nothing
***

Any help would be amazing!!

 

[jebry]

Hi!

You have to declare ForresterMarket as a Variant since ItemsSelected is a group of Variants.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[PHV]

You may try something like this:
Dim dbMarketing As Database
Dim ForresterMarket
Dim rec As DAO.Recordset
Set dbMarketing = CurrentDb()
Set rec = dbMarketing.OpenRecordset("TrafficForForm")
For Each ForresterMarket In Me!lstMarket.ItemsSelected
rec.AddNew
rec!Market = Me!lstMarket.ItemData(ForresterMarket)
rec.Update
Next
rec.Close
Set rec = Nothing
Set dbMarketing = Nothing

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[NeedsHelp101]

Hi Jeff,
Thanks, I changed it.
What about Me.lstMarket.ItemData(Market) ... is that correct?
I don't know what I would set lstMarket to, and I haven't declared Market anywhere so I don't know if that's necessary...

Any suggestions?

 

[NeedsHelp101]

I also tried your version, PHV, but for some reason, when I try to test it in the "immediate window," an error message keeps popping up saying that the sub or function are undefined. Nothing shows up in the table either, when I work through the form.
What am I doing wrong?
thanks!

 

[jebry]

Hi!

Try:

TrafficForForm!Market = Me.lstMarket.Column(0, ForresterMarket)

This assumes the information you want is in the first column.

Also, Column may be Columns, I never can remember which is right.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[NeedsHelp101]

No matter what I change, I keep getting an error message saying the Sub or Function is not defined... ideas?
Thank you!

 

[PHV]

Put the code I gave you in the Click event procedure of a button.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[NeedsHelp101]

Now I'm getting a message that says "User defined type not defined", and it higlights Dim dbMarketing As Database. How would I fix that?

Sorry for asking so many questions and thank you for being so patient!

 

[PHV]

menu Tools -> References ...
Tick the Microsoft DAO 3.6 Object Library

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886